Re: referring to a temp table in a procedure?

Thanks for all the responses. Special thanks to

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : avion_at_ozemail.com.au

Because of his reply, I check out the PL/SQL User's Guide and Reference 2.3 on page 4-12. in the follwoing example, the cursor c1 declaration can be much more complex to generate a result set from multi table join. And then the result set is populated in the PL/SQL table, in essence an array-like repository in the momey, for DBMS_OUTPUT to process. And from what I understand, please correct me if I am wrong, the PL/SQL table will be discarded when the procedure/function is finished.

So the PL/SQL table is the way to go for this type of processing. But it seems only apply to PL/SQL 2.3???

DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i BINARY_INTEGER := 0;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
i := i + 1;
/* Fetch entire row into record stored by ith element. */ FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
–– process data record
END LOOP;
CLOSE c1;
END; In article <328C2EDF.3251_at_spindle.net>, Ken Shirey <kshirey_at_spindle.net> wrote:
>Good news:
>
>Check out pg 4-9 of the PL/SQL user's guide and reference (v2.2) and the
>Oracle7 server application developers guide to see more complete
>examples of table creation/dropping in PL/SQL.
>
>You must, of course, use DBMS_SQL:
>
>create procedure DropTable(v_tablename in varchar2) as
> cid INTEGER;
>begin
> cid := dbms_sql.open_cursor;
> dbms_sql.parse(cid,'Drop Table ' || v_tablename, dbms_sql.v7);
> dbms_sql.close_cursor(cid);
>exception
> when others then
> dbms_sql.close_cursor(cid);
> raise;
>end;
>
>Enjoy...
>Ken Shirey
>Oracle Database Administrator
>PrimeCo Personal Communications, LLP
>kshirey_at_primeco.com
>
>
>
>Systems and Programming wrote:
>>
>> You cannot create the table right?
>>
>> In PL/SQL, you cannot use DDL (Data Definition Language)
>> commands, such as "Create table".
>> You can only use DML (Data Manipulation Language)
>> commands (Select, Update, Insert).
>>
>> It is frustrating, correct?
>>
>> You have to use a script to create your temporary tables.
>>
>> Have you considered making some views, that would
>> show only the data that you wanted? Since Oracle is multi-tasking
>> and multi-user, you might create some views, a few generations
>> deep, and finally select only from the last view. You would need to use
>> the front end tool to create the views. (Of course, if you could do
>> that,
>> you would just create the tables the same way, right.)
>>
>> Rodger Lepinsky
>> ADP Systems Partnership
>>
>> Phone: 204-957-1885
>> Fax: 204-942-3003
>> sysdev_at_adpsystems.mb.ca
>>
>> L. Tseng wrote:
>> >
>> > I don't know if this is a trivial question but I am hitting
>> > the wall now. Any help will be very appreciated!!!
>> >
>> > What I want to do is
>> >
>> > In a strored procedure,
>> >
>> > create a temp table;
>> >
>> > insert some rows to the temp table;
>> >
>> > create a cursor on the temp table;
>> >
>> > Loop thru the temp table with the DBMS_OUTPUT;
>> >
>> > It seems to me DBMS_SQL is the only way to go but...
>> >
>> > Can what I want be achieved? how?
>> >
>> > Thank you very much.
>> >
>> > Leslie
Received on Fri Nov 15 1996 - 00:00:00 CET