problem with oracle replacing " with " 1

[tomblanchard]

I am trying to build a sql statement from a function that will return xml, however oracle replaces my quote with "

Here is a sample of the function:

CREATE OR REPLACE function fnDynSelect(client_id_in IN number,feed_id_in IN number, view_in_name IN varchar2) return varchar2
as
x varchar2(32767);
begin
SELECT fnColumnList(client_id_in,feed_id_in, view_in_name).transform(xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
<xsl:text>SELECT XMLELEMENT("</xsl:text>
<xsl:text>table</xsl:text>
<xsl:text>",</xsl:text>
<xsl:text>XMLAGG(XMLELEMENT("</xsl:text>
<xsl:text>row"</xsl:text>
<xsl:for-each select="package/field">
<xsl:text>,XMLELEMENT("</xsl:text>
<xsl:value-of select="."/>
<xsl:text>",</xsl:text>
<xsl:value-of select="."/>
<xsl:text>)</xsl:text>
</xsl:for-each>
<xsl:text>)))</xsl:text>
<xsl:text> FROM </xsl:text>
<xsl:value-of select="package/VIEW_IN_NAME"/>
</xsl:template>
</xsl:stylesheet>'
)).getStringVal()
into x
from
dual;
return x;
end;

This returns the following:

SELECT XMLELEMENT(&quot;table&quot;,XMLAGG(XMLELEMENT(&quot;row&quot;,XMLELEMENT
(&quot;[column1]&quot;,[column1]),XMLELEMENT(&quot;[column2]&quot;,[column1])))) FROM [vwMyView]

which does not work.

Does anyone know of a way that I can get the function to return the statement without the &quot; or a way that I can replace it before sending it to be executed?

Thank you for any help.

 

[SantaMufasa]

Tom,

What I do, with great success, whenever quote issues arise, is that I code with a "backward apostrophe" (for example), which is an ASCII 96 ("`"). It becomes very easy to code since one can include it within Oracle's single quotes without concern for the typical "two-single-quotes-to-get-one-single-quote" stupidity that we normally encounter with Oracle.

Then, when one wants to actually end up with a single quote in place of the "backward apostrophe", you can use this code wherever necessary:

SELECT translate(<my_expression>,chr(96),chr(39)) from <table_name>

...where chr(96) is a "backward apostrophe" and chr(39) is a standard, single quote.

Here is a proof of concept:

select translate('abc`def',chr(96),chr(39)) from dual;

TRANSLA
-------
abc'def

Let us know if this provides any useful insight.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tomblanchard]

ok, I can execute this and I get back these results:

SELECT XMLELEMENT(`table`,XMLAGG(XMLELEMENT(`row`,XMLELEMENT(`[column1]`,[column1]),XMLELEMENT(`[column2]`,[column2])))) FROM [vwMyView]',chr(96),chr(34)) as x from dual;

which is good, but I have trouble when passing to the next function to execute it.
This is where I need to translate it.

if the code was pre-translated and passed into this it works:

create or replace function fnExecX(sql_in IN varchar2) return XmlType
as
x XmlType;
BEGIN
EXECUTE IMMEDIATE (sql_in) into x;
 return x;
END;

However I need to translate it inside this function.

EXECUTE IMMEDIATE ('select translate(sql_in,chr(96),chr(34)) as trans from dual') into x;

does not work which is probably obvious, to most, but me.

Does anyone have an idea of what I am doing wrong?
I think I need to set some param = to the translated value then execute that into x, but am not familiar enough with oracle to know how to do that.


Also, thank you very much for your help ealier SantaMufasa.
It is much appreciated.

 

[tomblanchard]

ok, half the way there now.

CREATE OR REPLACE FUNCTION fnExecVc(sql_in IN varchar2) return Varchar2
As 
x varchar(32767);
Begin 
 select translate(sql_in,chr(96),chr(34)) as trans into x from dual;
 return x;
end;

This works, but on to a new problem.

I can only call one function from the code.
So I need a way to call a function which returns the value that get passed into this function.

Using one function.