Swadhin Ray
asked on
Varchar input issue on function
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 ('16270066 44')','162 7006644' )
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 ....
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
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 ....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
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;
/
ASKER
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 ..
What is different between what you are tying to do and my test case?
ASKER
What I was trying to say is when I change the datatype to number every thing works fine .
Here is my code :
Now if I run my above code like below:
select my_data.make_json_fnc('my_ data.get_c ap_data(24 36282)', 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 ..
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('cap', 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('cap', 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> </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;
Now if I run my above code like below:
select my_data.make_json_fnc('my_
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 ..
>>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.
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.
ASKER
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 :-) ...
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 :-) ...
ASKER
Here is the code which I am using under JSON function :
Open in new window
The problem is here :
Open in new window
As I am trying to execute it inside the JSON function ..