Home » SQL & PL/SQL » SQL & PL/SQL » Converting SQL to PL/SQL
Converting SQL to PL/SQL [message #1988] Fri, 14 June 2002 10:20 Go to next message
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 [message #1989 is a reply to message #1988] Fri, 14 June 2002 11:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That will work, but change the first line to:

create or replace procedure procedure_name


so you can update the proc without having to drop it first.
Re: Converting SQL to PL/SQL PART 2 [message #1990 is a reply to message #1989] Fri, 14 June 2002 11:27 Go to previous messageGo to next message
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 Go to previous message
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)';
Previous Topic: Extract numbers from a string
Next Topic: Manipulation Function
Goto Forum:
  


Current Time: Sat Oct 20 16:06:10 CDT 2018