Solved

Varchar input issue on function

Posted on 2013-10-25
8
666 Views
Last Modified: 2013-10-25
Hello Experts,

I have a package which have 2 functions inside .

Function one returns a sys_refcursor and takes one input as varchar2

For example :

FUNCTION get_data_fnc(ip_uname IN varchar2) RETURN SYS_REFCURSOR;

Second Function takes the all the first function and another varchar as a input :

FUNCTION get_json_fnc(ip_rfc   IN VARCHAR2
                             ,ip_uname IN VARCHAR2) RETURN CLOB;



So finally when I call this should look as like below:

SELECT GET_JSON_FNC('GET_DATA_FNC('1627006644')','1627006644' )
FROM DUAL;

But this doesn't work because of the input type as varchar2 to the inner function.

Can you please suggest me how to over come on this issue ....
0
Comment
Question by:Swadhin Ray
  • 4
  • 4
8 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39600846
I do not understand what you are explaining.  What "isn't working"?

The first thing I notice is in the select against GET_JSON_FNC the first parameter is a string literal in the form of the function call to the second.

Since you are embedding a string literal inside that you need two single quotes:

SELECT GET_JSON_FNC('GET_DATA_FNC(''1627006644'')','1627006644' )
FROM DUAL;

Other than that, I don't understand.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39600917
I tried that earlier but I am not able to get the final result .. only getting null values :


Here is the code which I am using under JSON function :

 FUNCTION get_json_fnc(ip_rfc   IN VARCHAR2
                             ,ip_uname IN VARCHAR2) RETURN CLOB AS
        
                lhtmloutput   xmltype;
                lxsl          LONG;
                lxmldata      xmltype;
                lcontext      dbms_xmlgen.ctxhandle;
                l_ret_clob    CLOB;
                l_header_clob CLOB;
                l_header_data VARCHAR2(100);
                l_ip_rfc      SYS_REFCURSOR;
                l_exec_comm   VARCHAR2(250);
        begin
               l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';
        
                EXECUTE IMMEDIATE l_exec_comm
                        INTO l_ip_rfc;
        
                l_header_clob := '
....
...

.end;

Open in new window



The problem is here :

l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';

Open in new window


As I am trying to execute it inside the JSON function ..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39600946
>> only getting null values :

The you have a bug somewhere in your code.

If I understand the basics of what you are trying to do, the code below works for me.

create or replace function myfunc(p_in in varchar2) return sys_refcursor
is
	mycur sys_refcursor;
begin
	open mycur for 'select ''' || p_in || ''' from dual';
	return mycur;
end;
/

show errors


declare
	mycur sys_refcursor;
	mysql varchar2(100) := 'myfunc(''Hello'')';
	myresult varchar2(100);
begin
	execute immediate 'select ' || mysql || ' from dual' into mycur;
	fetch mycur into myresult;
	dbms_output.put_line('Got: ' || myresult);
end;
/

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39601071
When I pass any number input by changing the inner function i.e. get_data then my json conversion and data works fine .. then only issue is with varchar2 datatype ..
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601078
What is different between what you are tying to do and my test case?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39601151
What I was trying to say is when I change the datatype to number every thing works fine .

Here is my code :
CREATE OR REPLACE PACKAGE BODY my_data IS

        FUNCTION get_cap_data (ip_user_id in number)RETURN SYS_REFCURSOR AS
                l_cursor SYS_REFCURSOR;
        BEGIN
                OPEN l_cursor FOR
                       select CAP_SATUS                            
,VISIT_STATUS                         
,CITY                                 
,FACILITY_ID                          
,FACILITY_NAME from sr_test where user_id= ip_user_id;
                RETURN l_cursor;
        END;

       FUNCTION make_json_fnc(ip_rfc in VARCHAR2 , ip_user in number ) RETURN CLOB AS

        lhtmloutput   xmltype;
        lxsl          LONG;
        lxmldata      xmltype;
        lcontext      dbms_xmlgen.ctxhandle;
        l_ret_clob    CLOB;
       -- desc_cur      NUMBER;
        --l_descr_tab   dbms_sql.desc_tab2;
        --l_num_cols    NUMBER;
        l_header_clob CLOB;
        l_header_data    VARCHAR2(100);
        l_ip_rfc      SYS_REFCURSOR;
        l_exec_comm   VARCHAR2(250);
BEGIN
        l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';

        EXECUTE IMMEDIATE l_exec_comm
                INTO l_ip_rfc;

      l_header_clob := '
  {
      "metadata": {
                "USER_EMAIL":"';
      l_header_data:=get_email_address(ip_user)||'"
      },';
      l_header_clob:= l_header_clob||l_header_data;
       -- desc_cur      := dbms_sql.to_cursor_number(l_ip_rfc);

       


        /*l_header_clob := rtrim(l_header_clob
                              ,',') || '],"data":';*/
        


        EXECUTE IMMEDIATE l_exec_comm
                INTO l_ip_rfc;
        lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
        dbms_xmlgen.setnullhandling(lcontext
                                   ,1);
        lxmldata := dbms_xmlgen.getxmltype(lcontext
                                          ,dbms_xmlgen.none);
        CLOSE l_ip_rfc;
        -- this is a XSL for JSON
        lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html"/>
    
    <xsl:key name="cap" match="*" use="CAP_SATUS"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;cap&apos;, CAP_SATUS )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="CAP_SATUS"/>" :[
            <xsl:apply-templates select="key(&apos;cap&apos;, CAP_SATUS )" mode="grouped-cap"/>
            ]
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-cap">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        {
        <xsl:apply-templates select="*" mode="inner"/>
        }
    </xsl:template>

    <xsl:template match="*" mode="inner">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
        <xsl:text>"</xsl:text>
        <xsl:value-of select="name()"/>
        <xsl:text>":"</xsl:text>
        <xsl:value-of select="text()"/>
        <xsl:text>"</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>';

        lhtmloutput := lxmldata.transform(xmltype(lxsl));
        l_ret_clob  := lhtmloutput.getclobval();
        l_ret_clob  := REPLACE(l_ret_clob
                              ,'_x0020_'
                              ,' ');
        dbms_lob.writeappend(l_header_clob
                            ,length(l_ret_clob)
                            ,l_ret_clob);
        RETURN l_header_clob;
       -- RETURN l_ret_clob;
EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line(SQLERRM);
                dbms_output.put_line(dbms_utility.format_error_backtrace);
                RETURN NULL;
END ;

END my_data;

Open in new window


Now if I run my above code like  below:

select my_data.make_json_fnc('my_data.get_cap_data(2436282)', 2436282)  from dual;


I get my data properly ...

The issue I am getting when I change my get_cap_data input param to varchar2 .....

May I am missing something my JSON code ......

Because if I run independently my get_cap_data with varchar2 datatype as input param then I see my data but the time I call the JSON function ... I get null ..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39601169
>>What I was trying to say is when I change the datatype to number every thing works fine .

I understood that.

Since I cannot create your packages to test on my side because I don't have your tables or data, I set up a test case back in http:#a39600946 that I believe mirrors what you are trying to do.

It has a function that returns a ref cursor that accepts a varchar2 as input.  I then have a pl/sql block that successfully executes that function in what I believe is the exact same way you are trying.

I need to know how my sample test case differs from what you are trying to do since the test case works and you say your code doesn't.

If the code is basically the same, then you have a data problem or an implicit data conversion problem.
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39601256
Issue was with a small spelling mistake ... I was using  CAP_SATUS in place of CAP_STATUS .

And while selecting from my table I was pulling the correct one but in XSLT transformation I made a spelling mistake which made me to post this question :-) ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now