topic 'Drop / Create Table' inside a stored procedure in Operating System - OpenVMS

'Drop / Create Table' inside a stored procedure

Dmitry V. Sinelnikov — Mon, 26 Apr 2010 12:50:05 GMT

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?

Re: 'Drop / Create Table' inside a stored procedure

Jean-François Piéronne — Mon, 26 Apr 2010 13:50:50 GMT

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

Re: 'Drop / Create Table' inside a stored procedure

Dmitry V. Sinelnikov — Wed, 16 Jun 2010 07:19:34 GMT

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.

Re: 'Drop / Create Table' inside a stored procedure

Richard J Maher — Wed, 16 Jun 2010 13:05:36 GMT

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

Re: 'Drop / Create Table' inside a stored procedure

Dmitry V. Sinelnikov — Wed, 16 Jun 2010 20:08:42 GMT

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