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

From: Andrew Schneider <aschneider_at_powervision.com>
Date: Thu, 01 Jul 1999 10:24:05 -0400
Message-ID: <377B7A05.49A0A218@powervision.com>

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