Re: referring to a temp table in a procedure?

[Quoted] 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