I am calling the Oracle Stored procedure "find_NestedTable" with
<cfstoredproc> and I get an empty recordset/query. But when I use
another oracle tool like TOAD or SQLPLUS to call the TEST procedure
which calls find_NestedTable, I get the 7 items printed out.
Could someone give this code a try and see if you get the same results?
Package Spec:
------------------------------------
CREATE OR REPLACE PACKAGE Test_PKG
AS
/***************************************************/
/* Public Package Variables, etc. */
/***************************************************/
-- Define a generic cursor type.
TYPE generic_curtype IS REF CURSOR;
--
--
/***********************************************************
*
**********************************************************/
PROCEDURE find_NestedTable
(
p_p IN VARCHAR2 DEFAULT '1,3,6,22,30,66,98',
p_cur IN OUT generic_curType
);
/***********************************************************
*
**********************************************************/
PROCEDURE TEST;
/***********************************************************
*
**********************************************************/
END;
/
------------------------------------
End Package Spec
--
Package Body:
------------------------------------
CREATE OR REPLACE PACKAGE BODY test_pkg AS
/***********************************************************
*
**********************************************************/
PROCEDURE list2table
(
p_list IN VARCHAR2,
p_tbl IN OUT number_nt_type
)
AS
--
v_str VARCHAR2(100) DEFAULT p_list || ',';
v_pos NUMBER := NULL;
--v_tbl number_nt_type := number_nt_type();
BEGIN
LOOP
v_pos := INSTR( v_str, ',', 1 );
EXIT WHEN v_str IS NULL OR NVL(v_pos,0) <= 1;
p_tbl.EXTEND;
p_tbl( p_tbl.COUNT ) := SUBSTR(v_str,1,v_pos-1);
v_str := SUBSTR( v_str, v_pos+1);
END LOOP;
--
--FOR i IN v_tbl.FIRST..v_tbl.LAST LOOP
-- DBMS_OUTPUT.PUT_LINE( i || ':' || v_tbl( i ) );
--END LOOP;
--
END;
/***********************************************************
*
**********************************************************/
PROCEDURE find_NestedTable
(
p_p IN VARCHAR2 DEFAULT '1,3,6,22,30,66,98',
p_cur IN OUT generic_curType
)
IS
-- should be present
v_p number_nt_type := number_nt_type();-- (1,3,6,22,30,66,98);
BEGIN
list2table( p_p, v_p );
OPEN p_cur FOR SELECT * FROM TABLE(CAST(v_p AS number_nt_type)) c;
END;
/***********************************************************
*
**********************************************************/
PROCEDURE TEST
IS
-- should be present
v_str VARCHAR2(100):= '1,3,6,22,30,66,98';
v_cur generic_curType;
v_num NUMBER;
BEGIN
find_NestedTable( v_str, v_cur );
LOOP
FETCH v_cur INTO v_num;
IF v_cur%NOTFOUND THEN EXIT; END IF;
DBMS_OUTPUT.PUT_LINE( v_num );
END LOOP;
END;
/***********************************************************
*
**********************************************************/
END;
/
------------------------------------
End Package Body
Begin CFMX code:
------------------------------------
<cfstoredproc procedure="#variables.schema#.test_pkg.find_NestedTable"
datasource="#variables.dsn#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="p_p"
value="1,3,6,22,30,66,98" null="yes">
<cfprocresult name="qry" resultset="1" maxrows="20" >
</cfstoredproc>
------------------------------------
End CFMX code
Thanks,
Troy
--
Troy Simpson
Applications Analyst/Programmer, OCPDBA, MCSE, SCSA
North Carolina State University Libraries
Campus Box 7111 | Raleigh | North Carolina
ph.919.515.3855 | fax.919.513.3330
E-mail: [EMAIL PROTECTED]
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
