Re: PL/SQL not accepting CREATE TABLE command?!?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/08
Message-ID: <348d5375.20526715@inet16>

On Mon, 08 Dec 1997 11:12:37 -0600, kgrigg_at_acxiom.com wrote:

>Hello all, I am writing some PL/SQL and what I am wanting to do it
>retrieve values from cursors, do some manipulations and insert it into a
>table I created in the PL/SQL code, then do a SELECT on the table and
>spool it out to a file....and finally, drop the table. I get the
>following error:
>
>ORA-06550: line 3, column 1:
>PLS-00103: Encountered the symbol "CREATE" when expecting one of the
>following:
>
> begin declare exit for goto if loop mod null pragma raise
> return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql commit <a single-quoted SQL string>
>
>
>Does this mean I cannot create and drop tables from within PL/SQL? I was
>assuming this was how to do a temporary table....
>

It would be much easier/better to use one real table and let everyone insert/delete from it instead of creating a table, inserting, and dropping it. That aside, if you really want to run a procedure that looks like:

create or replace procedure examp
as

tname varchar2(30);
begin

select 'abc_' || ltrim(to_char(table_seq.nextval,'00009'))

      into tname
      from dual;

exec( 'create table ' || tname || ' ( x int )' ); exec( 'insert into ' || tname || ' select user_id from all_users' );

csv( 'select * from '||tname, '/tmp', 'abc.dat', ',' );

exec( 'drop table '||tname );
end;
/

(that is, it gets a unique table name, creates that table, inserts into it, calls some routine to 'dump' it and then drops it), you'll need to use dynamic sql. You need dynamic sql not only to create the table and drop it but to access it (since the procedure doesn't exist at compile time, the procedure cannot contain static references to non-existent objects.

The support routine 'exec' I used above might look like:

create or replace procedure exec( stmt in varchar2 ) as

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

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

exception

when others then

      if dbms_sql.is_open(exec_cursor) then
        dbms_sql.close_cursor(exec_cursor);
      end if;
      raise;

end;
/

Bear in mind that the OWNER of the exec routine needs to have the create table privelege granted directly to them, it cannot be gotten via a role like resource or dba. If you cannot:

SQL> set role none;
SQL> create table foo ( x int );

You will not be able to create the table in the procedure either. Roles are not enabled during the execution of procedures.

The routine 'csv' I used above is just an example of using UTL_FILE (new with 7.3) and dynamic sql to dump a query to a file. It looks like:

create or replace procedure csv( p_query     in varchar2,
                                 p_dir       in varchar2,
                                 p_fname     in varchar2,
                                 p_separator in varchar2 default ',' )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_output        utl_file.file_type;

begin

l_output := utl_file.fopen( p_dir, p_fname, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );

    end loop;
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );
end csv;
/

>Any suggestions out there?
>
>TIA!!
>
>Kelly
>kgrigg_at_acxiom.com
>
>ps. Please cc by mail too...our newsfeed is not reliable here...thus I'm
>posting from DejaNews...
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

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

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 08 1997 - 00:00:00 CST