Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
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
gautamvv
post Jun 15 2005, 03:25 AM
Post #1


Member
**

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;
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?
Go to the top of the page
 
+Quote Post
mensrea
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:
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
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 July 2014 - 11:00 AM