0

I have the following segment of oracle pl/sql block

DECLARE
    rec all_tab_columns%ROWTYPE;
    v_tableName VARCHAR2(100);
    v_columnName VARCHAR2(100);
    v_schemaName VARCHAR2(100);
BEGIN
    v_schemaName := 'TESTNEW';
    EXECUTE IMMEDIATE 'create table '||v_schemaName||'.table_column_mapping_8(table_name varchar2(100), column_name varchar2(100));';
    for rec in (
        SELECT table_name, column_name FROM all_tab_columns WHERE identity_column = 'YES' AND OWNER = v_schemaName
    ) LOOP
         v_tableName := rec.table_name;
         v_columnName := rec.column_name;

        EXECUTE IMMEDIATE 'insert into ' ||v_schemaName||'.table_column_mapping_8 values('''||v_tableName||''', '''|| v_columnName ||''');';
      END LOOP;
END;

As I try running this, I get the following error:

Error report - ORA-00922: missing or invalid option ORA-06512: at line 8 00922. 00000 - "missing or invalid option" *Cause:
*Action:

Looks like something is wrong at the execute immediate statment

Tried to google it and found this: http://www.dba-oracle.com/t_ora_00922_missing_or_invalid_option.htm

Couldn't find the real cause.

Can someone help here?

Thanks in advance

7
  • @basha04 the '.' separates the schema from the table. Otherwise how would I tell to the engine that a table needs to be created in a particular schema if there are many schemas?
    – Rahul
    Sep 7, 2021 at 13:56
  • It hink the string of the Execute Imemdiate statement should not end with a '"'. But I am not sure and have not the time to investigate this
    – miracle173
    Sep 7, 2021 at 14:07
  • @miracle173 I think you are referring to the second execute immediate statement and the block fails at the first statement itself. Moreover, in the second statement also, it's 3 single quotes( '''), used to specify the character values not single quote -double quote-single quote('"').
    – Rahul
    Sep 7, 2021 at 14:13
  • 2
    No I am referring to the first statement, too 'create table '||v_schemaName||'.table_column_mapping_8(table_name varchar2(100), column_name varchar2(100));'ends with a ;
    – miracle173
    Sep 7, 2021 at 15:02
  • 1
    You should provide a minimal-complete-and-verifiable-example. If you think the problem is in the create-table then you should remove all othere things that are not necessary to demonstrate your problem.
    – miracle173
    Sep 8, 2021 at 12:45

1 Answer 1

1

Remove the semicolon at the end of the execute immediate DDL string. Oracle SQL doesn't have statement terminators - semicolons are only used by client tools and the PL/SQL language, and your DDL string is not PL/SQL.

This fails due to the unrecognised semicolon character:

begin
    execute immediate 'create table demo (id int);';
end;

This works:

begin
    execute immediate 'create table demo (id int)';
end;

You will need to do the same for the dynamic insert later in the block.

1
  • Thanks @William Robertson. It works
    – Rahul
    Sep 15, 2021 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.