Welcome Guest ( Log In | Register )



Performance Tuning Reference poster
Oracle training in Linux 

Oracle training Weblogic Book
Easy Oracle Jumpstart
Oracle training & performance tuning books
Burleson Consulting Remote DB Administration
Reply to this topicStart new topic
> yesterdays problem, with a small change
post Jun 15 2005, 03:25 AM
Post #1


Group: Members
Posts: 26
Joined: 23-May 05
Member No.: 2,163

our java team wants the following solution from oracle

they want the output in a notepad, all the inserts, update and

delete statements of the tables

like suppose there is a table x with columns id and value

then the output in a .txt file should be

insert into x values(6,'indus')

update x set id=11, Value='indus' where id=11

for that i wrote a procedure using utl_file package, which

accepts a value, and displays the same in a notepad file

(we should already create a folder, and notepad file beforehand,

and in init.ora file, we should add a new entry as follows:

utl_file_dir = E:\MYDIR, where E:\mydir is the name of your directory,
then restart your oracle service)

the procedure is :

create or replace procedure utlproc(VALUE VARCHAR2) is
fhandle utl_file.file_type;

so this will accept a string and display it in the notepad file utl.txt under

mydir folder in e drive.

Now, there is a trigger which calls this procedure, this trigger, when

inserting into the table, accepts the insert string and displays it in the

notepad file (because we are calling utlproc here), similarly for updating

and deleting.

the trigger is

create or replace trigger Trig_x
before insert or update or delete on x
for each row
x1 varchar2(100);

if inserting then
x1:='insert into x values(' || :new.id || ',' || :new.value || ')';
elsif updating then

x1:='update x set id=' || :new.id || 'Value=' || :new.value || 'where id='|| :old.id

elsif deleting then

x1:='delete from x where id=' || :old.id ;

end if;


when i insert into table x, i am getting the following output in the utl.txt

insert into x values(6,indus)

I WANT THE OUTPUT LIKE THIS : insert into x values(6,'indus')

the single quotes is what i want, i tried adjusting the above

x1:='insert into x values(.........statement, but could not get the desired

output. similary, proper update and delete statements should be printed in a

notepad file.

how to adjust the above update and delete statements in the trigger so that

i can print proper update and delete statements?
Go to the top of the page
+Quote Post
post Jun 15 2005, 07:49 AM
Post #2

Advanced Member

Group: Members
Posts: 162
Joined: 10-January 05
From: Greenville SC
Member No.: 1,246

The problem is with the formatting of your INSERT statement...

you have:
if inserting then
x1:='insert into x values(' || :new.id || ',' || :new.value || ')';

Try this:
if inserting then
x1:='insert into x values(' || :new.id || ','''' || :new.value || '''')';

I'm not 100% sure it is 4 single quote marks (') ... it may be 3.

Go to the top of the page
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:


Lo-Fi Version Time is now: 25th November 2015 - 05:07 AM