Home » SQL & PL/SQL » SQL & PL/SQL » SELECT from dynamic table name (Oracle 11G)
SELECT from dynamic table name [message #589942] Fri, 12 July 2013 05:22 Go to next message
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 Go to previous messageGo to next message
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

Re: SELECT from dynamic table name [message #589955 is a reply to message #589943] Fri, 12 July 2013 08:44 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I would suggest that you do the following in your query

SELECT TABLE_NAME
INTO v_VerTabName
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME='ABB_VERSION'
AND OWNER = 'ABB_ADMIN';

[Updated on: Fri, 12 July 2013 08:46]

Report message to a moderator

Re: SELECT from dynamic table name [message #590705 is a reply to message #589943] Mon, 22 July 2013 06:40 Go to previous message
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
Thanks a lot LittleFoot. It worked fine. Thanks again.
Previous Topic: ORA-04080: trigger 'WRONG_UPDATE_WSDL' does not exist
Next Topic: how to find top level sql and pl/sql calls, not TOP-N queries
Goto Forum:
  


Current Time: Tue Sep 09 22:30:04 CDT 2025