function problem 1

[tomblanchard]

Hi, I am fairly new to oracle.
I have written a function that returns a sql statement that I would like to execute and return the results.

I have another function that I can execute the statement with, I so far have no idea of how to put this together.
This is the one that returns a sql statement:

CREATE OR REPLACE function fnMyFunc1(var_in IN varchar2) return varchar2
as
x varchar(32767);
BEGIN
SELECT fnMyFunc2(var_in) into x from dual;
return x;
end;

and this is the one that executes it:

create or replace function fnExecX(sql_in IN varchar2) return XmlType
as
x XmlType;
Begin
EXECUTE IMMEDIATE (fnExecV(sql_in)) into x;
 return x;
end;

If I execute the first one and paste my results into the second one it executes and returns the expected result.
However, I cannot figure ont how to just take what is in x and put it into the next function I need to execute and return the result from that.

I would like to just execute it in the first one and return the XmlType from the first function or have a function that calls the first one, gets the result, passes it to the second and returns the xmltype.

Any help would be much appreciated.

 

[carp]

Tom -
I'm lost here.

First, it looks like fnMyFunc2 might be of interest - please post it so we can analyze the entire string.

Secondly, in fnExecX I only see a call to fnExecV; where do you try to use fnMyFunc1?

Please post all of the pieces so we can trace through the logic.

 

[tomblanchard]

Ok, fnMyFunc1 is something I call that passes a variable into a function that returns a sql statement, eg: SELECT '1' as x from dual;

I want to then take that statement and execute it and return the results.

fnExecX returns the results, ignore fnExecV, it just takes the sql statement passed in and changes some characters before executing it.

I have a function that returns a sql statement that gets built based on information passed to it, and another that executes it.

what I need is a way of putting them together.

 

[SantaMufasa]

Tom,

I agree with Carp: Notwithstanding your clarification just now, I don't see any linkage between "fnMyFunc1" and anything going on in "fnExecX". So, at this point, I don't believe that Carp or I can yet give you a definitive response.

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]

If I changed x to sql_out, and fnMyFunc2 to fnMyDynSqlFunc would that help?


CREATE OR REPLACE function fnMyFunc1(var_in IN varchar2) return varchar2
as
sql_out varchar(32767);
BEGIN
SELECT fnMyDynSqlFunc(var_in) into sql_out from dual;
return sql_out;
end;


fnExecX take sql_in.

 

[tomblanchard]

Carp, here is what would be in fnMyFunc2:

CREATE OR REPLACE function fnMyFunc2(var_in IN varchar2) return varchar
as
x varchar2(32767);
begin
SELECT fnColumnList(var_in).transform(xmltype('<xsl:stylesheet version="1.0" xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
  <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;

 

[tomblanchard]

Basically what I want to do is something like this, although this does not work, maybe, it will help you understand what I am looking for:

CREATE OR REPLACE function fnMyFunc1(var_in IN varchar2) return varchar2
as
x varchar(32767);
xml varchar(32767);
BEGIN
EXECUTE IMMEDIATE ('SELECT fnDynSelect(var_in) as y from dual') into x;
EXECUTE IMMEDIATE ('SELECT fnExecX(x) as z from dual') into xml;
return xml;
end;

 

[SantaMufasa]

Tom,

Now I understand your problem. Thanks for the code restatement.

The code which you formulate for the EXECUTE IMMEDIATE, when it is actually executed, cannot reference any variables outside the actual SQL statement. Therefore, you can accomplish what you want with the following slight adjustments (...addition of concatenation of your variables, which I have placed in bold print) to your code:

CREATE OR REPLACE function fnMyFunc1(var_in IN varchar2) return varchar2
as
x varchar(32767);
xml varchar(32767);
BEGIN
EXECUTE IMMEDIATE ('SELECT fnDynSelect([b][i]'||[/i][/b]var_in[b][i]||'[/i][/b]) as y from dual') into x;
EXECUTE IMMEDIATE ('SELECT fnExecX([b][i]'||[/i][/b]x[b][i]||'[/i][/b]) as z from dual') into xml;
return xml;
end;
/

Let us know how this works for you.

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]

Thank you once again Mufasa.