How do I pass a table name dynamically using execute immediate [message #636430] |
Thu, 23 April 2015 15:29 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
Say in my schema there are 2 tables called emp and 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 #636432 is a reply to message #636431] |
Thu, 23 April 2015 15:42 |
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 #636444 is a reply to message #636433] |
Fri, 24 April 2015 01:49 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
buggleboy007 wrote on Fri, 24 April 2015 02:17I 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 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
@Lalit: I wanted to use that 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 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 then I am using a and then passing each 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 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 #636468 is a reply to message #636463] |
Fri, 24 April 2015 10:57 |
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 |
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 #636595 is a reply to message #636430] |
Tue, 28 April 2015 14:33 |
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 #636639 is a reply to message #636601] |
Wed, 29 April 2015 10:04 |
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
|
|
|