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
'create table '||v_schemaName||'.table_column_mapping_8(table_name varchar2(100), column_name varchar2(100));'
ends with a;