SELECT from dynamic table name [message #589942] |
Fri, 12 July 2013 05:22  |
 |
sinpeak
Messages: 59 Registered: January 2011 Location: india
|
Member |
|
|
Hi,
I do not know the table name but have a query that for sure returns the table name and I want to select a column value from the table into my PLSQL variable.
Here is the code :
DECLARE
v_VerTabName VARCHAR2(30);
v_Minor NUMBER(2);
v_SQL VARCHAR2(80);
BEGIN
SELECT
OBJECT_NAME INTO v_VerTabName
FROM SYS.ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'ABB_%'
AND OWNER = 'ABB_ADMIN'
AND STATUS = 'VALID';
v_SQL := 'SELECT ABB_VERSION INTO ' || v_Minor || ' FROM '|| v_VerTabName ;
EXECUTE IMMEDIATE v_SQL;
DBMS_OUTPUT.PUT_LINE('Minor:'||v_Minor);
END;
But when I compile this I get ORA-00936 : Missing expression at the EXECUTE IMMEDIATE line.
Please help/advise.
Thanks.
|
|
|
Re: SELECT from dynamic table name [message #589943 is a reply to message #589942] |
Fri, 12 July 2013 05:27   |
 |
Littlefoot
Messages: 21826 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Wrong syntax. Should be
v_SQL := 'SELECT ABB_VERSION FROM '|| v_VerTabName ;
EXECUTE IMMEDIATE v_SQL INTO v_minor;
Note that it *might* fail with TOO-MANY-ROWS if there is more than a single valid object owned by ABB_ADMIN whose name begins with ABB_ (and, of course, NO-DATA-FOUND if there's none).
[Updated on: Fri, 12 July 2013 05:27] Report message to a moderator
|
|
|
|
|