Home » SQL & PL/SQL » SQL & PL/SQL » Using escape character in dynamic SQL (Oracle 11g, Windows xp)
Using escape character in dynamic SQL [message #564731] Tue, 28 August 2012 04:36 Go to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
I'm trying to replace the variable value v_tblname in dynamic SQL.
But not able to escape the single quotes character.
Please let me know, where I'm going wrong.

DECLARE
v_sqlcols VARCHAR2(2000);
v_sqlcols1 VARCHAR2(2000);
v_tblname VARCHAR2(50) := 'DEPT';
BEGIN
      v_sqlcols := q'[SELECT LISTAGG(COL,',' ||CHR(10)) WITHIN GROUP (ORDER BY COL) 
      FROM (
      SELECT DISTINCT COLUMN_NAME ||'   '||DATA_TYPE ||''||'('||DATA_LENGTH||')'  AS COL 
      FROM user_tab_cols 
      WHERE column_name NOT LIKE 'XX%'
      AND table_name = '''||v_tblname||''') ]';

dbms_output.put_line(v_sqlcols);      
EXECUTE IMMEDIATE v_sqlcols into v_sqlcols1;

dbms_output.put_line(v_sqlcols1);
END;


Regards,
Lokesh
Re: Using escape character in dynamic SQL [message #564734 is a reply to message #564731] Tue, 28 August 2012 04:54 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
As it seems to me v_tblname is part of the string defined with the q operator. Try to split your query in three parts something like this:

Query = q'[<first part>]' || v_tblname || q'[<second part>]'

[Updated on: Tue, 28 August 2012 04:57]

Report message to a moderator

Re: Using escape character in dynamic SQL [message #564740 is a reply to message #564734] Tue, 28 August 2012 05:30 Go to previous messageGo to next message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks dariyoosh for your response.
I'm looking to handle the query in single SQL statement. I know it sounds its crazy, but I really want to know where I'm going wrong in that query.

Regards,
Lokesh
Re: Using escape character in dynamic SQL [message #564744 is a reply to message #564740] Tue, 28 August 2012 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm looking to handle the query in single SQL statement.


What does this mean?

Quote:
I really want to know where I'm going wrong in that query.


Isn't that obvious from the solution that has been given to you?
The real question is "why do you think it is correct?".

Regards
Michel

[Updated on: Tue, 28 August 2012 06:04]

Report message to a moderator

Re: Using escape character in dynamic SQL [message #564746 is a reply to message #564740] Tue, 28 August 2012 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
lokimisc wrote on Tue, 28 August 2012 10:36

But not able to escape the single quotes character.

The problem is that the q mechanism is escaping all the single quotes.
You don't want the ones surrounding v_tblname to be escaped.
In fact you don't seem to understand what the q mechanism does.
It's a way of avoiding having to put two single quotes in a string in order to get one in the output.
You've written the contents of the string as though you are not using the q' mechanism.


lokimisc wrote on Tue, 28 August 2012 11:30

I'm looking to handle the query in single SQL statement.


dariyoosh's solution still gives a single sql statement, not sure why you think otherwise.
Re: Using escape character in dynamic SQL [message #564748 is a reply to message #564746] Tue, 28 August 2012 06:35 Go to previous message
lokimisc
Messages: 72
Registered: February 2008
Member
Thanks cookiemonster.
I got what I'm doing wrong Smile

Regards,
Lokesh
Previous Topic: SQL Table type variable access
Next Topic: Package
Goto Forum:
  


Current Time: Sat Aug 30 23:26:01 CDT 2014

Total time taken to generate the page: 0.12670 seconds