How to convert sql to Pl/SQL ...please help me for my code to convert

[jagadish]

Hello

I have one SQL file with following sql queries.

Create table @temp1
(A varchar2(20),
b varchar2(20),
c varchar2(20),
d varchar2(20),
e varchar2(20),
f varchar2(20),
g varchar2(20));

Create table @temp2 as
SELECT A,B,C
From X
Group by Y,Z,W;


Create table @temp3 as
SELECT A,B,C
From XX
Group by Y,Z,W;

Insert into @temp1(
A,B,C
Select 'SS',
B,
c
From @temp2 a,
@temp3 b
Where a.a = b.a and a.b = a.b;

Insert into @temp1(
A,B,C
Select 'SS1',
B,
c
From Itm;

Insert into @temp1(
A,B,C
Select 'SS2',
B,
c
From YYY;

Insert into @temp1(
A,B,C
Select 'SS3',
B,
c
From ZZZ;
------------------
It spools out put to a file...

Finally ...we have the sql statement
which select all the required data from the some other table and also above created
temp table 1 (@temp1).....

Now we want to migrate to PL/SQL ...can any one let me know what is the best/better
way to migrate, i can use cursors..but how in this....
Please let me know with some sample code ....for above sample code...

Thanks in advance...

[thechamp42001]

Use native dynamic sql or execute immediate statements .Actually we use these through a built in package of oracle DBMS_SQL.I am writing a sample code 4 u.

create or replace procedure
(p_tab_name in varchar2,p_rows_del out number)

is
cursor_name integer;
begin
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name,'delete from ' || p_tab_name,dbms_sql.native);

p_rows_del:=dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
end;
/

now u write in SQL*PLUS environment

variable deleted number

execute delete_all_rows('employees',:deleted);

print deleted

i am sending u a oracle documentation link site from where u just download the pdf named "Supplied PLSQL Packages and Types Reference"which will help u surely in these matters.

link is http://zuse.esnig.cifom.ch/database/...v/docindex.htm