Troy Simpson wrote:
> I am using Oracle9i Release 2 (9.2.0.3.0) with CFMX 6.1
>
> 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]
