Home » SQL & PL/SQL » SQL & PL/SQL » How do I pass a table name dynamically using execute immediate (O/S:Windows 7 PRO; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
How do I pass a table name dynamically using execute immediate [message #636430] Thu, 23 April 2015 15:29 Go to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Say in my schema there are 2 tables called emp and dept

a) emp
b) dept


CREATE OR REPLACE PROCEDURE SANDEEP_TST_TBL(p_tablename IN VARCHAR2)
IS

    lv_strsqlquery VARCHAR2(2000); 


BEGIN

    lv_strsqlquery:='SELECT MAX(deptno) FROM'||p_tablename||;
    
    EXECUTE IMMEDIATE lv_strsqlquery;

END;


I create a procedure and wish to query dept no based on either table emp or dept at run time. I believe using execute immediate will resolve this issue. However when I write the following code it does not compile and gives me a message PLS-00103. Can some one help me in this regards.

Many thanks in advance.
Re: How do I pass a table name dynamically using execute immediate [message #636431 is a reply to message #636430] Thu, 23 April 2015 15:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
lv_strsqlquery:='SELECT MAX(deptno) FROM'||p_tablename||;


Re: How do I pass a table name dynamically using execute immediate [message #636432 is a reply to message #636431] Thu, 23 April 2015 15:42 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Thanks Michel. I changed it and was able to compile the procedure successfully. Thanks a ton.

However when I call the procedure via an anonymoys block, I am getting this error:

PLS-00357  Table,View Or Sequence reference 'emp' not allowed in this context
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


I think it may be that it wants the schema name to be referened.
Re: How do I pass a table name dynamically using execute immediate [message #636433 is a reply to message #636432] Thu, 23 April 2015 15:47 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I got it. I was not passing the parameter as a string i.e. 'emp' or 'dept'. Once I passed it worked fine. Thanks a lot again.
Re: How do I pass a table name dynamically using execute immediate [message #636437 is a reply to message #636433] Thu, 23 April 2015 17:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Once I passed it worked fine.

Depends upon your definition of "fine".

my code below produces the EXACT same results as the procedure posted by buggleboy007 .


  1  CREATE OR REPLACE PROCEDURE SANDEEP_TST_TBL(p_tablename IN VARCHAR2)
  2  IS
  3      lv_strsqlquery VARCHAR2(2000);
  4  BEGIN
  5      lv_strsqlquery:=NULL;
  6* END;
  7  /

Procedure created.

SQL> exec sandeep_tst_tbl('FUBAR');

PL/SQL procedure successfully completed.
Re: How do I pass a table name dynamically using execute immediate [message #636438 is a reply to message #636437] Thu, 23 April 2015 20:09 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Blackswan: What I meant was that I was passing the parameter, emp or dept as a string as is i.e. I was passing it as
exec sandeep_tst_tbl(emp)
and not
exec sandeep_tst_tbl('emp')
. This was creating issues. Hope this helps.
Re: How do I pass a table name dynamically using execute immediate [message #636440 is a reply to message #636438] Fri, 24 April 2015 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the final procedure code?
It may help future readers.

Re: How do I pass a table name dynamically using execute immediate [message #636444 is a reply to message #636433] Fri, 24 April 2015 01:49 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
buggleboy007 wrote on Fri, 24 April 2015 02:17
I got it. I was not passing the parameter as a string i.e. 'emp' or 'dept'. Once I passed it worked fine.


There are multiple issues with your code.

Firstly, as BS pointed out, your code does nothing. Nothing at all. Just a SELECT. What is the purpose of it?

Secondly, you must have a space after FROM keyword.

Thirdly, though you can do in this way. That doesn't mean you should do it. (ab)using execute immediate is not a good idea, perhaps you need to think more on the business case.


Regards,
Lalit
Re: How do I pass a table name dynamically using execute immediate [message #636455 is a reply to message #636444] Fri, 24 April 2015 07:33 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
@Lalit: I wanted to use that
SELECT
statement and pass one table dynamically into another procedure and do additional processing. Since I felt that I would not know the name of the table before hand, the only way I thought this could be accomplished is through
EXECUTE IMMEDIATE
style as it belongs to native dynamic SQL in Oracle. Other than this I did not know how this can be accomplished. If there is a better way then please share it with us as it will help one and all here.

@Michel:Till I was doing this it was fine however business team came back to me and told that they don't want to pass one table. They want to pass the names of all the tables (there are 7 tables based on a satisfying condition). So I used a
FOR I IN (SELECT column_name FROM table_name WHERE criteria = a criteria)
. So Once I receive the names of the all the 7 names of the tables in
column_names
then I am using a
LOOP
and then passing each
column_name
into another procedure to do some further processing. This is yet to be done and working on it.

Just out of curiosity Michel - Is it better to use an explicit
CURSOR FOR LOOP
or would you suggest using
FOR I IN (SELECT column_name FROM table_name WHERE criteria = a criteria)
. Would be interested in your thoughts.

Thanks
Re: How do I pass a table name dynamically using execute immediate [message #636456 is a reply to message #636455] Fri, 24 April 2015 07:37 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
The questions is, WHY? Why do you need to pass table names dynamically? The application must know which table to query in advance. Seems to be a design flaw and should be fixed first before doing the coding.

Re: How do I pass a table name dynamically using execute immediate [message #636457 is a reply to message #636456] Fri, 24 April 2015 07:55 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
There is a specific reason why I am passing it dynamically and that cannot be accomplished by hardcoding the table name. Just because I am passing it dynamically it does not mean that it's a design flaw. It could be a design flaw to you but not to me.
Re: How do I pass a table name dynamically using execute immediate [message #636458 is a reply to message #636457] Fri, 24 April 2015 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>They want to pass the names of all the tables

what datatype contains "the names of all the tables"?
Re: How do I pass a table name dynamically using execute immediate [message #636459 is a reply to message #636458] Fri, 24 April 2015 08:41 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It'll certainly be a performance design flaw if you're working like this.

How are you able to handle different column names, if they are only giving you a table name? If the tables are all the same...a global temporary table is probably better.
Re: How do I pass a table name dynamically using execute immediate [message #636461 is a reply to message #636459] Fri, 24 April 2015 09:02 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
@BS:VARCHAR is the data type.
@RC: Luckily I am retrieving only one column of the various tables. All those tables have the same primary key column name and the same datatype.

Re: How do I pass a table name dynamically using execute immediate [message #636463 is a reply to message #636461] Fri, 24 April 2015 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>@BS:VARCHAR is the data type.

having single string containing multiple values is amateurish & Worst Practice.

How do you parse out the individual table names?

Post working code example of how you return results from 7 different tables to single calling procedure.
Re: How do I pass a table name dynamically using execute immediate [message #636468 is a reply to message #636463] Fri, 24 April 2015 10:57 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I will certainly post the required code when it's done fully from my side. Yes, it could be worst practice and hence I approach seniors like you because most of my experience is bug fixing, enhancements and not full fledged projects;that's an experience I really lack and find it a bit challenging as well and not to mention use of collections. This is not an excuse but just stating facts and need to get a way around it.

I initially was looking only for one small answer to my question however as the discussion is getting enriched I will post the code when the time comes.Stay tuned.
Re: How do I pass a table name dynamically using execute immediate [message #636469 is a reply to message #636468] Fri, 24 April 2015 12:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You need to consider the security issues of your code. You are expecting me to invoke your procedure like this:
exec sandeep_tst_tbl('emp')
but what if I invoke it like this:
exec sandeep_tst_tbl('emp'' union all select max(sal) from emp')
(I haven't tested this, but the idea is clear). Using string concatenation to construct commands to pass to EXECUTE IMMEDIATE is highly vulnerable to malicious SQL injections.



Re: How do I pass a table name dynamically using execute immediate [message #636470 is a reply to message #636468] Fri, 24 April 2015 12:34 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
buggleboy007 wrote on Fri, 24 April 2015 10:57
Yes, it could be worst practice and hence I approach seniors like you because most of my experience is bug fixing, enhancements and not full fledged projects;that's an experience I really lack and find it a bit challenging as well and not to mention use of collections.


Yet earlier you said
Quote:
There is a specific reason why I am passing it dynamically and that cannot be accomplished by hardcoding the table name. Just because I am passing it dynamically it does not mean that it's a design flaw. It could be a design flaw to you but not to me.


So take the advice of the "seniors" you claim you want to learn from. Explain WHY you have to take this approach, and be ready to realize that it is, indeed, a flawed design and change your approach. Instead of focusing on a technique, focus on the business problem.
Re: How do I pass a table name dynamically using execute immediate [message #636595 is a reply to message #636430] Tue, 28 April 2015 14:33 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
Once again need some assistance, I have a Function which is as follows:

CREATE OR REPLACE FUNCTION GetTableData(p_vInTname IN VARCHAR2
                                       ,p_dInLastPubTms IN DATE
                                        ) 
RETURN CLOB
IS
  vStrSqlQuery   VARCHAR2(32767);
   TYPE ref_cursor IS REF CURSOR;
   rc_tablevalues ref_cursor;
   lc_XML CLOB;
   
   
   
BEGIN 
    
    vStrSqlQuery:= q'[SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual]';
   
   DBMS_OUTPUT.put_line(vStrSqlQuery);
    
    OPEN  rc_tablevalues FOR vStrSqlQuery;
    FETCH rc_tablevalues INTO lc_XML;
    CLOSE rc_tablevalues;
    RETURN lc_XML;
    printClob(lc_XML);
    
END GetTableData;


This compiled successfully.

Another procedure for printing

Create or replace PROCEDURE printClob (result IN OUT NOCOPY CLOB) IS
    xmlstr   VARCHAR2 (32767);
    line     VARCHAR2 (2000);
  BEGIN
    xmlstr := DBMS_LOB.SUBSTR (result, 32767);

    LOOP
       EXIT WHEN xmlstr IS NULL;
       line := SUBSTR (xmlstr, 1, INSTR (xmlstr, CHR (10)) - 1);
       DBMS_OUTPUT.put_line ('| ' || line);
       xmlstr := SUBSTR (xmlstr, INSTR (xmlstr, CHR (10)) + 1);
    END LOOP;
  END printClob;


I am trying to test it to see how it behaves, so created a test procedure which is:

CREATE OR REPLACE PROCEDURE SANDEEP_TEST_LAMXML
IS
lv_x CLOB;
BEGIN
   lv_x := CTN_PUB_CNTL_EXTRACT_PUBLISH.GetTableData('TRKFCG_SBDVSN',TO_DATE('04/27/2015 19:57:10', 'MM/DD/YYYY HH24:MI:SS'));
   
END;


The above one too compiles successfully. However when I set a break point and then try to pass through the function instead of seeing the parameter in the second part of the query, I am seeing it as:

SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual


What is that I am doing wrong that I am unable to see the runtime parameters being passed into the variables of the query? Can any one suggest something?

Thanks in advance.

[Updated on: Tue, 28 April 2015 14:40]

Report message to a moderator

Re: How do I pass a table name dynamically using execute immediate [message #636596 is a reply to message #636595] Tue, 28 April 2015 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is completely wrong.
Just think about what is the query you generate and what is its result when you execute it:
SQL> select q'[SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual]' query from dual;
QUERY
------------------------------------------------------------------------------------------------------------------------
SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY
 HH24:MI:SS')) FROM dual

SQL> SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY H24:MI:SS')) FROM dual;
ERROR:
ORA-01756: quoted string not properly terminated


Re: How do I pass a table name dynamically using execute immediate [message #636597 is a reply to message #636596] Tue, 28 April 2015 15:03 Go to previous messageGo to next message
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
Senior Member
I am trying to pass the parameters so that I can successfully generate values from inner query and then generate a xml from the outer one. That's my aim.

Which quoted string is not terminated properly?
Re: How do I pass a table name dynamically using execute immediate [message #636598 is a reply to message #636595] Tue, 28 April 2015 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>CREATE OR REPLACE FUNCTION GetTableData(p_vInTname IN VARCHAR2,p_dInLastPubTms IN DATE)
>vStrSqlQuery:= q'[SELECT dbms_xmlgen.getxml('SELECT * FROM p_vInTname ' ||' WHERE record_update_tms >= TO_DATE(p_dInLastPubTMS,'MM/DD/YYYY HH24:MI:SS')) FROM dual]';


NEVER use TO_DATE on a DATE datatype is you did above.
P_DINLASTPUBTMS is already a DATE as shown in the top line.
Re: How do I pass a table name dynamically using execute immediate [message #636601 is a reply to message #636597] Wed, 29 April 2015 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
buggleboy007 wrote on Tue, 28 April 2015 22:03
I am trying to pass the parameters so that I can successfully generate values from inner query and then generate a xml from the outer one. That's my aim.

Which quoted string is not terminated properly?


Can you just read the query you generate and count the quote?

Re: How do I pass a table name dynamically using execute immediate [message #636639 is a reply to message #636601] Wed, 29 April 2015 10:04 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP,

As BS pointed out. Never use TO_DATE on a DATE. You are forcing an implicit data type conversion. Oracle will convert it into string and then back to date using locale-specific NLS format.

For example:

SQL> explain plan for select * from dual where to_date(sysdate) > to_date(sysdate -1);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(TO_DATE(TO_CHAR(SYSDATE@!))>TO_DATE(TO_CHAR(SYSDATE@!-1)))

14 rows selected.

SQL>


So, the actual filter applied is filter(TO_DATE(TO_CHAR(SYSDATE@!)) You will get incorrect output due to the implicit conversion based on locale-specific NLS format.


Regards,
Lalit
Previous Topic: Reg Spliting line using instr,substr and regexp
Next Topic: Leading zero and comma decimal separator
Goto Forum:
  


Current Time: Fri Mar 29 06:05:47 CDT 2024