Re: CREATE TABLE in procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2000/01/29
Message-ID: <d0469ssgh7j9fdaea36elrc1hach5uagj2@4ax.com>#1/1

A copy of this was sent to Roger G. <rgorden_at_my-deja.com> (if that email address didn't require changing) On Fri, 28 Jan 2000 22:19:23 GMT, you wrote:

>Alan;
>
>You cannot do any DDL (create table etc.) statements in a PL/SQL
>procedure, or any PL/SQL block.

it is quite easy to do ddl in plsql.

In Oracle8i, it is even easier. In Oracle8.0 and before it would look like

create or replace procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;
/
begin

    execute_immediate( 'create table t ( x int )' ); end;
/

In Oracle8i, it'll just be:

begin

   EXECUTE IMMEDIATE 'create table t ( x int )'; end;

(execute immediate being a new feature in 8i).

If you get insufficient privs error, see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for the reason and the solution.

>There are 2 ways around this.
>
>1- Create a table in SQLPLUS using simple commands and delete the
> table when you're through
> -or-
>2- Declare a PL/SQL table datatype and use this in your procedure.
> (The advantage to this is that hte PL/SQL table lives only while the
> procedure is being run and takes up no permanent space on the
> database).
>
>I hope this helps.
>
>Roger
>
>
>

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Sat Jan 29 2000 - 00:00:00 CST