yesterdays problem, with a small change Options

[gautamvv]

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;
begin
DBMS_OUTPUT.PUT_LINE('VALUE '|| VALUE);
fhandle:=utl_file.fopen('e:\mydir','utl.txt','a');
utl_file.putf(fhandle,'%s',VALUE);
utl_file.fclose(fhandle);
end;


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
Declare
x1 varchar2(100);

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

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

;
UTLPROC(x1);
elsif deleting then

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

end if;
end;

question:

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?

[mensrea]

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

you have:

CODE

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

Try this:

CODE

begin
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.

Jeff