'Drop / Create Table' inside a stored procedure

[Dmitry V. Sinelnikov]

Hi All,

Does anyone know if it is possible to put CREATE/DROP statements into a stored procedure for Oracle RDB (OpenVMS 8.3, Oracle RDB 7.3)?

Example of sql script:
CREATE MODULE DB_UPGR
LANGUAGE SQL
PROCEDURE PROC();
BEGIN
DROP TABLE UPGR_VERSIONS IF EXISTS;
END;
END MODULE;
COMMIT;
ROLLBACK;

Where 'DROP' expression fails in this way:

cont> DROP TABLE UPGR_VERSIONS IF EXISTS;
%SQL-I-DEPR_FEATURE, Deprecated Feature: Keyword DROP used as an identifier
DROP TABLE UPGR_VERSIONS IF EXISTS;
^
%SQL-F-LOOK_FOR, Syntax error, looking for :, found TABLE instead

I have seen couple of articles where it was said that data definition statements like CREATE/DROP/ALTER are not allowed inside the procedure. The others said that EXECUTE 'statement' could help in this case but I'm afraid it doesn't work for Oracle RDB, does it?

[Jean-François Piéronne]

Dmitry,

I suggest you issue under SQL an
HELP COMPOUND_STMT:
Can include only a subset of the SQL statements allowed in a simple statement procedure. (See the compound-use-statement syntax diagram for a list of these valid statements.)

Then Format
...

JFP

[Dmitry V. Sinelnikov]

in addition to this theme: is there any way to get rid of error messages like this

%SQL-F-REL_EXISTS, Table X already exists in this database or schema
%SQL-F-CONALREXI, Constraint name Y already exists in this database or schema
%SQL-F-COLEXISTS, Column Z is named more than once in table NR_CREDITS

when executing a script on the same DB more than once?
Of course we can redirect output to the file and delete this messages to see the 'clear' log... Just wanted to know if this can be done somehow using RDB tools.

[Richard J Maher]

Hi Dimitry,

What happened to your DROP TABLE x IF EXISTS; commands immediately prior to you CREATE TABLE commands? Unlike Orrible Oracle Rdb does not have CREATE OR REPLACE syntax but I guess you can always interrogate the Metadata tables if need be, but not completely sure of the requirement here?

Cheers Richard Maher

[Dmitry V. Sinelnikov]

Hi Richard,

DROP ... IF EXISTS is useful of course but we have a customer that needs to upgrade his database iteratively and he doesn't want to lose the previous data. So if we include DROP before the CREATE then every upgrade will erase all the data accumulated after the previous upgrade. That's not acceptable.

Of course we can add some check like this:
IF ( EXISTS( SELECT * FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'TABLE_NAME' LIMIT TO 1 ROWS ) )
but this can be done only in scope of BEGIN-END expression which doesn't allow CREATE/DROP/ALTER commands as already mentioned above.

WBR, Dmitry