Geek asks and answers

Cannot be CREATE TABLE inside, if else

When I run this request

DECLARE
      num NUMBER;
BEGIN

    SELECT COUNT(*) INTO num FROM user_all_tables WHERE TABLE_NAME=upper('DatabaseScriptLog')
    ;

    IF num < 1 THEN

      CREATE TABLE DatabaseScriptLog 
      (ScriptIdentifier VARCHAR(100) NOT NULL,
       ScriptType VARCHAR(50), 
       StartDate TIMESTAMP, 
       EndDate TIMESTAMP, 
       PRIMARY KEY (ScriptIdentifier)
       );

    END IF;

END;

When doing the above, I got the following:

PLS-00103: collided with the CREATE symbol, expecting one of the following:

begin case declare exit for goto if loop mod null pragma raise return select update when <<close current delete extract lock insert open rollback
Save point set sql execute commit forall merge pipe 06550. 00000 - "row% s, column% s: \ n% s "* Reason: Typically a PL / SQL compilation error.

+3
source share
2 answers

You cannot run such DDL statements. You need to use dynamic SQL (EXECUTE IMMEDIATE).

IF num < 1 THEN

  EXECUTE IMMEDIATE 'CREATE TABLE DatabaseScriptLog (ScriptIdentifier VARCHAR(100) NOT NULL, ScriptType VARCHAR(50), StartDate TIMESTAMP, EndDate TIMESTAMP, PRIMARY KEY (ScriptIdentifier))'

END IF;
+10
source

, SQLServer. , . , , , .

script, , , if-then, , db. , if .. script, ifs . 46, script, 50, , 47-50.

You can take immediate action, but you will need elevated privileges that I would not recommend.

Hope this helps.

0
source

More articles:


All Articles