Re: Help! Question on PL/SQL

creating "session-specifc" tables like this is not multi-user safe.

In SQL server, one is encouraged to use temp tables as an efficient means of sharing data in stored procedures (and other reasons).

In Oracle, there are better ways (see some of the threads on your posting)

Uwe Uhlemann <uhlemann_at_gmx.de> wrote in message news:869n0e$2fdh4$1_at_fu-berlin.de...
>
> "Dana Jian" <djian_at_trasa.com> schrieb im Newsbeitrag
> news:3887953d$0$6125_at_news.choice.net...
> > Thanks for the help!
> >
> > Can I put the "create table temp1 as select .... from .... where ....."
> into
> > a stored procedure/function on the server??
> > I got the error when I was trying to do this:
> >
> > PLS-00103: Encountered the symbol "CREATE" when expecting one of the
> > following:
> >
> > begin declare end exception exit for goto if loop mod null
> > pragma raise return select update while <an identifier
> >
> > Any idea/help will be greatly appreciated!!
>
> Yes.
> You can't make CREATE in PL/SQL direct.
> Use the Package DBMS_SQL.
>
> Exemple:
>
> <<create_table_test>>
> DECLARE
> anzahl number;
> v_cursor number;
> v_create varchar2(200);
> begin
>
>
> v_cursor := sys.dbms_sql.open_cursor;
> v_create := 'create table test (c1 date not null,c2 varchar(30),c3
> varcharchar(80))';
> sys.dbms_sql.parse(v_cursor,v_create, sys.dbms_sql.V7);
> anzahl := sys.dbms_sql.execute(v_cursor);
> sys.dbms_sql.close_cursor(v_cursor);
>
> end if;
> exception
> when OTHERS then raise_Application_error(sqlcode,sqlerrm,true);
>
> end create_table_test;
> /
>
> __________________________________________________
> Dipl.-Wirt. Ing.
> Uwe Uhlemann
> SPM - Systementwicklung und Projektmanagement GmbH
> An den Treptowers 1
> 12435 Berlin, Germany
> Tel +49 (30) 55115-365, Fax -100
> uuhlemann@spm.de http://www.spm.de
>
>
>
>
Received on Mon Jan 24 2000 - 17:59:42 CST