Conference orarep::nomahs::sql

3879.0. "Can you DROP table from a stored procedure?" by M5::JAKUHN ([email protected]) Fri Apr 11 1997 19:50

    Rdb 6.* 7.*
    
    I thought you could do drop statements in a stored procedure, like:
    
    create module slm_create_test language sql
    procedure slm_create_test;
    begin
            drop table co_market_sid;
          !( or drop trable co_market_sid restricted  or cascade ect. )
            commit;
                 create table co_market_sid (
                sid_num  smallint,
                t_market char(6),
                a_market char(3));
            commit;
    end;
     end module;
    
    but i get:
    %SQL-I-DEPR_FEATURE, Deprecated Feature: Keyword drop used as an
    identifier
            drop table co_market_sid cascade  ;
                 ^
    %SQL-F-LOOK_FOR, Syntax error, looking for :, found TABLE instead
    %SQL-F-NO_TXNOUT, No transaction outstanding
    %SQL-F-REL_EXISTS, Table CO_MARKET_SID already exists in this database
    or schema
    end;
    ^
    %SQL-F-LOOK_FOR_STMT, Syntax error, looking for a valid SQL statement,
    found END instead
     end module;
     ^
    %SQL-F-LOOK_FOR_STMT, Syntax error, looking for a valid SQL statement,
    found END
     instead
    
    Should some flavor of this work? the documentation of CREATE PROCEDURE
    seems to indicate that the DROP TABLE CASCADE should work. Maybe I
    am missing a restriction in one of the release notes?
    
    

The documentation makes it very clear that there is a limited number
statements which are acceptable as compound-use statement.  No DDL is
supported.

~    Should some flavor of this work? the documentation of CREATE PROCEDURE
~    seems to indicate that the DROP TABLE CASCADE should work. Maybe I
~    am missing a restriction in one of the release notes?

Can you show us the reference in the documentation?  I have no idea what it
might say to make you think DROP TABLE CASCADE would be accepted in a compound
statement.

Ian

>The documentation makes it very clear that there is a limited number
>statements which are acceptable as compound-use statement.  No DDL is
>supported.

Jay, others,

I found table 1-1 in the SQL Ref. Manual to be a great place to look for
what can/can't be done with a compound statement, and other goodies.

    ahhh, i had compound statements confused with a stored procedure as 
    a whole. I'll try it without the begin/end. thanks
    

    I see now. a "simple procedure" can't be in a stored
    procedure, only in SQLMOD ect. (sql programming v6.0 page 13-4, bullet #6). 
    Heck of a restriction. 

~    Heck of a restriction. 

Maybe you'd change your mind if you tried to implement it in the engine :-)

Ian