| Converting SQL to PL/SQL [message #1988] |
Fri, 14 June 2002 10:20  |
Brijal Padia
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
I would like to transform an existing SQL script with about 10 inserts and 15 update statements into a PL/SQL stored procedure in order for it to run as part of a nightly data refresh.
Does anyone know if I can just say something like:
create procedure PROCEDURE_NAME
begin
//ALL OF MY SCRIPT
END;
Thanks in advance,
Brijal
|
|
|
|
|
|
| Re: Converting SQL to PL/SQL PART 2 [message #1990 is a reply to message #1989] |
Fri, 14 June 2002 11:27  |
BP
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
Thanks for your help.
I forgot to mention that my SQL script also creates/drops a table and index in the middle which I've just read is not something PL/SQL supports. IS that true?
In addition, my root problem, is that my DBA believes that this is a difficult taks, and has sent me the following response:
"We are using stored procedures to populate our tables not scripts. I can't include a script with all stored procedures. This cannot be done."
IN which case I am looking for a quick and easy solution (not really concerned about tuning at the moment). Can I still just make a procedure to:
Delete from a table (doing a complete refresh)
Insert in a bunch of rows
Update a bunch of rows
Create a second table
Create an index for the second table
Insert into second table
Use it to update my original table
Is this possible? Or is it as difficult as my DBA is making it seem?
Thanks again for your help.
|
|
|
|
| Re: Converting SQL to PL/SQL PART 2 [message #1992 is a reply to message #1989] |
Fri, 14 June 2002 12:51 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You can handle the DDL in your procedure by using native dynamic SQL (if you are pre-8i, you'll need to use DBMS_SQL instead).
execute immediate 'create table t (c1 number)';
execute immediate 'create index t_ndx on t(c1)'; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
