Re: simple Stored procedure with drop, create table instruction...???

You cannot execute DDL from stored procedures (do to the late binding nature of DDL). In order to perform your proposed statements, use the DMBS_SQL package (handles dynamic SQL statements and DDL). To look at the methods needed to accomplish your functionality, do a describe on the package in SQL*PLUS/SQL*WORKSHEET, or look at the DBMSSQL.SQL script in your Oracle server's ORACLE_HOME\RDMS<version>\ADMIN directory.

fred wrote:

> I'd like to create a stored procedure looking like that:
>
> CREATE PROCEDURE provToOper
> AS BEGIN
>
> DROP sequence fs_ssis2_oper.COMPANY_SEQ;
> CREATE TABLE fs_ssis2_oper.COMPANY AS (SELECT * FROM fs_ssis2_prov.COMPANY);
>
> END;
>
> But i always get an error message:
>
> PLS-00103: Encountered the symbol "DROP" when expecting one of the foll...
>
> (could not read what it is expecting... It would have been to easy ;-))
>
> Any Idea??
Received on Thu Jul 01 1999 - 09:24:05 CDT