Hi,

I have a Oracle 8i stored procedure that returns a Ref_cursor.
I want to execute this from VB6 application using ADO command object
I will create parameter for input parameters as adchar and ad date
but what do I give for output reference cursor when adding it to parameters list

If I pass only the input parameters and say .execute to the command object it gives me error
ORA-06550: line 1, column 7
PLS-00306: wrong number or types of arguments in call to
'VIEW GET ALL'
ORA-06550: line 1, column 7
PL/SQL: Statement ignored


It is like this

PROCEDURE VIEW_GET_ALL
(IN_CERT_NBR IN VARCHAR2,
IN_MPOL_NBR IN VARCHAR2,
IN_START_DATE IN DATE,
IN_END_DATE IN DATE,
IN_DEBIT_OR_CREDIT_IND IN CHAR,
REF_CURSOR OUT TREF_CURSOR)
AS
vCERT_NBR CHAR(20);
vMPOL_NBR CHAR(10);
vSTART_DATE DATE;
vEND_DATE DATE;
vDEBIT_CREDIT_D CHAR(1);
vDEBIT_CREDIT_C CHAR(1);
BEGIN

-- MAKE SURE THE INPUT VARIABLES ARE CORRECTLY PADDED
-- BECAUSE 8i REALLY LIKES TO TRIM STRINGS
vCERT_NBR := IN_CERT_NBR;
vMPOL_NBR := IN_MPOL_NBR;


-- DETERMINE IF DEBIT, CREDIT, OR BOTH RECORDS ARE BEING SELECTED
IF IN_DEBIT_OR_CREDIT_IND = 'D' THEN
-- RETURN ONLY DEBIT RECORDS
vDEBIT_CREDIT_D := 'D';
vDEBIT_CREDIT_C := 'D';
ELSIF IN_DEBIT_OR_CREDIT_IND = 'C' THEN
-- RETURN ONLY CREDIT RECORDS
vDEBIT_CREDIT_D := 'C';
vDEBIT_CREDIT_C := 'C';
ELSE
-- RETURN BOTH DEBIT AND CREDIT RECORDS
vDEBIT_CREDIT_D := 'D';
vDEBIT_CREDIT_C := 'C';
END IF;

-- CURSOR NOTES:
-- TABLE_TYPE HOLDS THE TABLE THAT THE RECORD CAME FROM
OPEN REF_CURSOR FOR
SELECT 'PH' AS TABLE_TYPE,
ROWNUM,
EFFECT_DATE AS TRAN_DATE,
ORGANIZATION_ID AS CORP_NBR,
RTRIM(ACCT_NBR, ' ') AS ACCT_NBR,
NULL AS TXN_ID,
TRAN_REFERENCE,
LTRIM(TO_CHAR(DISTRIBUTION_AMT * -1,'999999999999999.99'),' ') AS AMOUNT,
NULL AS AMOUNT_TYPE,
LPAD(TO_CHAR(ITEM_TYPE), 2, '0') AS ITEM_TYPE,
BUSINESS_NBR AS VENDOR_NBR,
LPAD(TO_CHAR(REFUND_CODE), 2, '0') AS REFUND_CODE,
NULL AS PREM_TAX,
NULL AS STAFF_INITIALS,
DEBIT_OR_CREDIT_IND,
RTRIM(GL_SHORT_DESC, ' ') AS GL_SHORT_DESC,
RTRIM(JOURNAL_ENTRY_ID, ' ') AS JOURNAL_ENTRY_ID,
PJRN_MISC_INFO,
RECORD_ID,
RTRIM(SLID, ' ') AS SLID,
RTRIM(SUBLEDGER, ' ') AS SUBLEDGER
FROM PAY_HISTORY
WHERE CERT_NBR = vCERT_NBR
AND MPOL_NBR = vMPOL_NBR
AND EFFECT_DATE >= vSTART_DATE
AND EFFECT_DATE <= vEND_DATE
AND DEBIT_OR_CREDIT_IND = vDEBIT_CREDIT_D
ORDER BY 3 DESC, 1, 6 DESC, 7 DESC, 5 DESC, 15 DESC;
EXCEPTION
WHEN OTHERS THEN
RAISE; -- RAISE THE ERROR TO VB
END VIEW_GET_ALL;