Is there a limeit on the no. of input output parameters in ADO calling Oracle Stored Proc


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Is there a limeit on the no. of input output parameters in ADO calling Oracle Stored Proc

Hybrid View

  1. #1
    adohelp Guest

    Is there a limeit on the no. of input output parameters in ADO calling Oracle Stored Proc


    I have been trying to return a recordset from Oracle using Microsoft ODBC
    for Oracle and defining each output parameter as a PL/SQL table. I can't
    use a ref cursor since I need to manipulate the resultset in the stored procedure.
    ( can't do it on the front end due to security constraints) I am able to
    successfully call the procedure fromn SQl/PLus but when calliong from VB
    using ADO 2.7 I get an error

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful
    completion

    I am able to successfully get the results the procedure if I have a total
    of 7 parameters (input and output parameters) but it fails if I have more
    than 7 parameters no matter what type of parameter I use

    The following is the Package


    CREATE OR REPLACE PACKAGE PAYROLL.PBStest
    AS

    TYPE tbl_POLICY_UNIT_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    TYPE tbl_POLICY_SUFFIX_CDE IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
    TYPE tbl_POLICY_YEAR IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
    TYPE tbl_GROUP_IDN IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
    TYPE tbl_policy_idn IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;


    PROCEDURE SP_TestPayRoll(
    i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,

    i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,

    i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,

    i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,

    o_group_idn OUT tbl_GROUP_IDN,
    o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
    o_policy_year OUT tbl_POLICY_YEAR,
    o_policy_idn OUT tbl_policy_idn
    );
    END PBStest;


    CREATE OR REPLACE PACKAGE BODY PAYROLL.pbstest
    AS
    /************************************************************
    Constants
    *************************************************************/
    c_blank_1 CONSTANT CHAR (1) := ' ';
    c_blank_3 CONSTANT CHAR (3) := ' ';
    v_split_dte BOOLEAN DEFAULT FALSE;
    varrecordcount NUMBER DEFAULT 0;
    v_splitdte BOOLEAN DEFAULT FALSE;
    PROCEDURE SP_TestPayRoll (
    i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,

    i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,

    i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,

    i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,

    o_group_idn OUT tbl_GROUP_IDN,
    o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
    o_policy_year OUT tbl_POLICY_YEAR,
    o_policy_idn OUT tbl_policy_idn
    )

    IS

    CURSOR policy_cur
    IS
    SELECT
    pol.group_idn,
    pol.policy_unit_num,
    pol.policy_year,
    pol.policy_idn
    FROM pbs_policy_information pol
    WHERE pol.policy_unit_num = i_policy_unit_num
    AND pol.group_idn = i_group_idn
    AND pol.policy_year = i_policy_year;

    reccount NUMBER DEFAULT 0;
    BEGIN
    FOR policyrec IN policy_cur
    LOOP
    reccount := reccount + 1;
    o_group_idn (reccount) := policyrec.group_idn;
    o_policy_unit_num (reccount) := policyrec.policy_unit_num;
    o_policy_year (reccount) := policyrec.policy_year;

    o_policy_idn (reccount) := policyrec.policy_idn;

    END LOOP;

    END SP_TestPayRoll;
    END pbstest;
    /

    And I am invoking it in the following manner:

    Function getPayroll()
    Dim cmdPayroll As New ADODB.Command
    Dim strCmd As String
    Dim iCount As Integer
    Dim rsPayroll As Recordset

    On Error GoTo ErrHandler:
    Const cConnection = "SERVER=myserver;driver={Microsoft ODBC for Oracle};UID=XXXXX;PWD=XXXXXX;"

    Dim objConnection As New ADODB.Connection

    '* Create the connection object

    objConnection.ConnectionString = cConnection
    objConnection.CursorLocation = 3 '* adUseClient
    objConnection.Open

    Const cProcName = "{call payroll.pbstest.sp_testpayroll(?,?,?,?,{resultset
    100,o_group_idn,o_policy_unit_num,o_policy_year,o_policy_idn})}"

    cmdPayroll.CommandText = cProcName


    cmdPayroll.CommandType = adCmdText

    cmdPayroll.ActiveConnection = objConnection
    '

    cmdPayroll.Parameters(0).Type = adVarChar
    cmdPayroll.Parameters(0).Direction = 1 '* adParamInput
    cmdPayroll.Parameters(0).Value = CStr(" ") '* Set the input param
    '

    cmdPayroll.Parameters(1).Type = adDouble
    cmdPayroll.Parameters(1).Direction = 1 '* adParamInput
    cmdPayroll.Parameters(1).Value = 1574316 '* Set the input param '

    cmdPayroll.Parameters(2).Type = adVarChar
    cmdPayroll.Parameters(2).Direction = 1 '* adParamInput
    cmdPayroll.Parameters(2).Value = "2000" '* Set the input param '

    cmdPayroll.Parameters(3).Type = adChar
    cmdPayroll.Parameters(3).Direction = 1 '* adParamInput
    cmdPayroll.Parameters(3).Value = CStr(" ")

    Set rsPayroll = cmdPayroll.Execute()
    rsPayroll.MoveFirst
    Debug.Print rsPayroll.GetString()

    Exit Function

    ErrHandler:
    Dim x As String
    x = Err.Description
    End Function



  2. #2
    Michael McGrath Guest

    Re: Is there a limeit on the no. of input output parameters in ADO calling Oracle Stored Proc


    Yes,
    I have found the same problem. In the work that I do I must return data
    using PL/SQL as well. The only work around was to concatenate the data placing
    it a large varchar2 data type. On the Client side I would break it apart
    again. Since I had to do that I only returned one table array.

    Mike


    "adohelp" <vivekvipul@hotmail.com> wrote:
    >
    >I have been trying to return a recordset from Oracle using Microsoft ODBC
    >for Oracle and defining each output parameter as a PL/SQL table. I can't
    >use a ref cursor since I need to manipulate the resultset in the stored

    procedure.
    >( can't do it on the front end due to security constraints) I am able to
    >successfully call the procedure fromn SQl/PLus but when calliong from VB
    >using ADO 2.7 I get an error
    >
    >[Microsoft][ODBC driver for Oracle][Oracle]ORA-00000: normal, successful
    >completion
    >
    >I am able to successfully get the results the procedure if I have a total
    >of 7 parameters (input and output parameters) but it fails if I have more
    >than 7 parameters no matter what type of parameter I use
    >
    >The following is the Package
    >
    >
    >CREATE OR REPLACE PACKAGE PAYROLL.PBStest
    >AS
    >
    >TYPE tbl_POLICY_UNIT_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    >TYPE tbl_POLICY_SUFFIX_CDE IS TABLE OF CHAR(1) INDEX BY BINARY_INTEGER;
    >TYPE tbl_POLICY_YEAR IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER;
    >TYPE tbl_GROUP_IDN IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
    >TYPE tbl_policy_idn IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
    >
    >
    >PROCEDURE SP_TestPayRoll(
    > i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,


    >
    > i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,
    >
    > i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,


    >
    > i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,
    >
    > o_group_idn OUT tbl_GROUP_IDN,
    > o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
    > o_policy_year OUT tbl_POLICY_YEAR,
    > o_policy_idn OUT tbl_policy_idn
    > );
    >END PBStest;
    >
    >
    >CREATE OR REPLACE PACKAGE BODY PAYROLL.pbstest
    >AS
    >/************************************************************
    > Constants
    >*************************************************************/
    > c_blank_1 CONSTANT CHAR (1) := ' ';
    > c_blank_3 CONSTANT CHAR (3) := ' ';
    > v_split_dte BOOLEAN DEFAULT FALSE;
    > varrecordcount NUMBER DEFAULT 0;
    > v_splitdte BOOLEAN DEFAULT FALSE;
    > PROCEDURE SP_TestPayRoll (
    > i_GROUP_IDN IN PBS_POLICY_INFORMATION.GROUP_IDN%TYPE,


    >
    > i_POLICY_UNIT_NUM IN PBS_POLICY_INFORMATION.POLICY_UNIT_NUM%TYPE,
    >
    > i_POLICY_YEAR IN PBS_POLICY_INFORMATION.POLICY_YEAR%TYPE,


    >
    > i_POLICY_SUFFIX_CDE IN PBS_POLICY_INFORMATION.POLICY_SUFFIX_CDE%TYPE,
    >
    > o_group_idn OUT tbl_GROUP_IDN,
    > o_policy_unit_num OUT tbl_POLICY_UNIT_NUM,
    > o_policy_year OUT tbl_POLICY_YEAR,
    > o_policy_idn OUT tbl_policy_idn
    > )
    >
    > IS
    >
    > CURSOR policy_cur
    > IS
    > SELECT
    > pol.group_idn,
    > pol.policy_unit_num,
    > pol.policy_year,
    > pol.policy_idn
    > FROM pbs_policy_information pol
    > WHERE pol.policy_unit_num = i_policy_unit_num
    > AND pol.group_idn = i_group_idn
    > AND pol.policy_year = i_policy_year;
    >
    > reccount NUMBER DEFAULT 0;
    > BEGIN
    > FOR policyrec IN policy_cur
    > LOOP
    > reccount := reccount + 1;
    > o_group_idn (reccount) := policyrec.group_idn;
    > o_policy_unit_num (reccount) := policyrec.policy_unit_num;
    > o_policy_year (reccount) := policyrec.policy_year;


    >
    > o_policy_idn (reccount) := policyrec.policy_idn;


    >
    > END LOOP;
    >
    > END SP_TestPayRoll;
    >END pbstest;
    >/
    >
    >And I am invoking it in the following manner:
    >
    >Function getPayroll()
    >Dim cmdPayroll As New ADODB.Command
    >Dim strCmd As String
    >Dim iCount As Integer
    >Dim rsPayroll As Recordset
    >
    >On Error GoTo ErrHandler:
    > Const cConnection = "SERVER=myserver;driver={Microsoft ODBC for Oracle};UID=XXXXX;PWD=XXXXXX;"
    >
    > Dim objConnection As New ADODB.Connection
    >
    > '* Create the connection object
    >
    > objConnection.ConnectionString = cConnection
    > objConnection.CursorLocation = 3 '* adUseClient
    > objConnection.Open
    >
    >Const cProcName = "{call payroll.pbstest.sp_testpayroll(?,?,?,?,{resultset
    >100,o_group_idn,o_policy_unit_num,o_policy_year,o_policy_idn})}"
    >
    >cmdPayroll.CommandText = cProcName
    >
    >
    >cmdPayroll.CommandType = adCmdText
    >
    >cmdPayroll.ActiveConnection = objConnection
    >'
    >
    >cmdPayroll.Parameters(0).Type = adVarChar
    >cmdPayroll.Parameters(0).Direction = 1 '* adParamInput
    >cmdPayroll.Parameters(0).Value = CStr(" ") '* Set the input param
    >'
    >
    >cmdPayroll.Parameters(1).Type = adDouble
    >cmdPayroll.Parameters(1).Direction = 1 '* adParamInput
    >cmdPayroll.Parameters(1).Value = 1574316 '* Set the input param '
    >
    >cmdPayroll.Parameters(2).Type = adVarChar
    >cmdPayroll.Parameters(2).Direction = 1 '* adParamInput
    >cmdPayroll.Parameters(2).Value = "2000" '* Set the input param '
    >
    >cmdPayroll.Parameters(3).Type = adChar
    >cmdPayroll.Parameters(3).Direction = 1 '* adParamInput
    >cmdPayroll.Parameters(3).Value = CStr(" ")
    >
    >Set rsPayroll = cmdPayroll.Execute()
    >rsPayroll.MoveFirst
    > Debug.Print rsPayroll.GetString()
    >
    >Exit Function
    >
    >ErrHandler:
    >Dim x As String
    > x = Err.Description
    >End Function
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center