TOraStoredProc - multi-params where one is a ref cursor

[steve_321]

In Oracle I have created a package and it has a procedure that has 2 parameters, one is a varchar2 and the second is a ref cursor. I am using Borland C++ 6 and want to use a TOraStoredProc to execute the procedure and return a dataset.

Here is the package:

Code: Select all

create or replace package "Dev".Test
AS
  type curvar_type is ref cursor return address_type%rowtype;

  procedure get_address_types (str in varchar2, curvar_out out curvar_type);
END;

Code: Select all

create or replace package body "Dev".Test
AS
  procedure get_address_types 
    (str in varchar2, curvar_out out curvar_type)
  AS
    local_cur curvar_type;
  BEGIN
    open local_cur for select * from address_type;
    curvar_out := local_cur;
  END;
END Test;

Initially I had only the ref cursor (param curvar_out) as a parameter, and I am able to use the TOraStoredProc to execute it and return a dataset, and loop though the dataset as one would with a TOraQuery.

However, adding a parameter I cannot get it to work.

Here is the C++ code:

Code: Select all

 TOraStoredProc* procTest = NULL;
  TParam* param = NULL;
  String type, displayOrder;

  if (!procTest)
  {
    procTest = new TOraStoredProc(0);
    procTest->Session = oraSession;
    procTest->StoredProcName = "dev.test.get_address_types";

    procTest->ParamCheck = false;

    param = procTest->Params->CreateParam(
      ftString,
      "str",
      ptInput);

    procTest->Params->AddParam(param);
  }

  String sql;
  try
  {
    procTest->ParamByName("str")->AsString = "Hello";
    procTest->Open();
  }
  catch (const Exception& e)
  {
    String errMsg = e.Message;
    int k = 5;
  }

Here is the error:

ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'GET_ADDRESS_TYPES'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Any help would be appreciated.

[Plash]

There are errors in your C++ code:
1. The ParamCheck property cannot be used for TOraStoredProc component.
2. Two parameters should be created for the TOraStoredProc component before you call the Open method. ODAC can create parameters for the stored procedure automatically or you can create it manually.
To create parameters automatically, call the PrepareSQL method of the TOraStoredProc component.
If you create parameters manually, you should create both parameters: STR and CURVAR_OUT.
3. If you create parameters manually with the CreateParam method, you should not call the AddParam method.

[steve_321]

This appears to work, thank you very much that is very helpful and easy to apply!