Home » SQL & PL/SQL » SQL & PL/SQL » need to convert a set of sql scripts into pl\sql program which needs to be triggered automatically
need to convert a set of sql scripts into pl\sql program which needs to be triggered automatically [message #361330] Tue, 25 November 2008 23:56 Go to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Hi,

I have a set of sql statements which has 3 insert into statements and 2 truncate statements.
I want to convert these sql into pl\sql which needs to triggered automatically every weeks and updates data.
PLs help on this as this is my first pl\sql code.I'm using oracle 10gEE and want to do this.....and how it's pos,pls let me know the method step by step.

Regards,
Sowmya
Re: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatical [message #361339 is a reply to message #361330] Wed, 26 November 2008 00:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a procedure which would consist of these SQL statements; in order to make it run "every week", use DBMS_SCHEDULER package.

Note that you can't use TRUNCATE statement directly - you'll need to use EXECUTE IMMEDIATE (or DELETE a table instead).

Now that you know all the keywords, feel free to do the research by yourself. Documentation can be found here.
Re: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatical [message #361345 is a reply to message #361330] Wed, 26 November 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is just to call a procedure each week, DBMS_JOB will be easier (I think).

Regards
Michel
Re: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatical [message #361398 is a reply to message #361345] Wed, 26 November 2008 04:32 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thank u so much for your replies.
And added to that,
I have the sql query as

insert into tablename
Select A.field1,A.field2, B.field3,B.field4
FROM tablea A JOIN Tableb B ON (A.field3 = B.field3)
Where Field4 = 'AAAA'
GROUP BY B.field3, B.field4;

I want to convert into plsql...
how it can be done as it has insert statement in it?
Re: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatical [message #361400 is a reply to message #361398] Wed, 26 November 2008 04:42 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@sowmyaa,

I didn't get you. You can use the INSERT Statement directly in PL/SQL if you know what are the tables and columns used in the query you provided:
insert into tablename 
Select A.field1,A.field2, B.field3,B.field4 
FROM tablea A JOIN Tableb B ON (A.field3 = B.field3) 
Where Field4 = 'AAAA' 
GROUP BY B.field3, B.field4;


Regards,
Jo

[Updated on: Wed, 26 November 2008 04:43]

Report message to a moderator

Re: need to convert a set of sql scripts into pl\sql program which needs to be triggered automatical [message #361401 is a reply to message #361398] Wed, 26 November 2008 04:43 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing to do, it works as it.
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Previous Topic: ORA-29283: invalid file operation, ORA-06512: at "SYS.UTL_FILE", line 449
Next Topic: Problem while Dequeuing message (merged)
Goto Forum:
  


Current Time: Fri Dec 09 04:13:39 CST 2016

Total time taken to generate the page: 0.10241 seconds