Re: pl/sql - help!!

From: John Hough <q6y_at_ornl.gov>
Date: 1997/01/30
Message-ID: <32F0AC75.3387_at_ornl.gov>#1/1

Goldrich wrote:
>
> Can't find the answer to this one in any documentation or books I have.
>
> How do I issue a create table stmt w/in a PL/SQL block in a stored proc
> implementation. The syntax for my create table is fine-- it goes
> smoothly when entered at SQLPLUS command line. Same command w/in stored
> proc produces a syntax error.
>
> 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>
> <a single-quoted SQL string> << close delete fetch lock
> insert open rollback savepoint set sql commit
>
> Email replies would be greatly appreciated as I am posting this via a
> very unreliable new server.
>
> Thanks in advance.

Stored procedures, packages, and functions MAY NOT execute Data Definition Language Statements (DDL) thus statements like: CREATE, DROP, TRUNCATE, etc will fail when executed from a stored object. You MUST execute these statements vie Dynamic SQL using the Oracle Supplied DBMS_SQL packages. An example is shown below:

PROCEDURE trunc_table
  (p_table_name IN VARCHAR2)
IS
  v_cursor INTEGER;
  v_ddl_command VARCHAR2;
BEGIN
  v_ddl_command := 'truncate table '||p_table_name;

  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, v_ddl_command,DBMS_SQL.V7);
  v_counts := DBMS_SQL.EXECUTE(v_cursor);

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
  commit work;
END trunc_table;

I did not compile this and just briefly checked syntax against another program that did DDL. The procedure owner will have to have the provileges to, in your case, "create table" granted directly to their account and NOT through a view.

And whoever has execute to the procedure I gave as an example can truncate ANY table that I can truncate, so be careful.

Hope this helps,

John Hough Received on Thu Jan 30 1997 - 00:00:00 CET