This content has been marked as final. Show 3 replies
privilege acquired via ROLE do not apply within named PL/SQL procedures
Hello, my last question of the year ----building on my last question, I would like to create a new table that writes create and update history to a new table. First it reads USER_TAB_COLS to get the tables that I want, then creates a table based on the query. If I run the query standalone with a fixed table name, it works fine, however, when run this way, I get the following error which indicates it got the first row from my query.
ORA-00904: "ADM_APPS_USERS": invalid identifier
ORA-06512: at line 12
00904. 00000 - "%s: invalid identifier"
You need to enclose the table name in single quotes, not double quotes.
You are using this code with double quotes around 'v_tname'.
Use single quotes
SELECT "'||v_tname||'" Table_Name, NVL ( c.date_created
The above may LOOK the same but it isn't.
SELECT '''||v_tname||''' Table_Name, NVL ( c.date_created
The standard way to write and test dynamic sql is to capture the query to be executed in a VARCHAR2 variable so you can print it out and test it separately.
That makes it a lot easier to get the syntax correct.
v_query VARCHAR2(4000); . . . v_query := 'create table BG_BUILD_AUDIT as WITH created AS . . .'; execute immediate v_query;
Using [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF00218]Text Literals notation is another way to deal with quotes within quotes.1 person found this helpful