Re: VB & Oracle question

You have declared a cursor c1
and then in your code you try and open P_IC try opening C1

You need to go read the documentation  on cursors and learn how they are manipulated as your coding around the usage of the cursor is rather wrong

From The Application Developers Guide - Fundamentals

DECLARE
  Emp_name    VARCHAR2(10);
  Cursor      c1 IS SELECT Ename FROM Emp_tab
                 WHERE Deptno = 20;

BEGIN
  OPEN c1;
  LOOP
     FETCH c1 INTO Emp_name;
     EXIT WHEN c1%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(Emp_name);
  END LOOP;

END; Cheers

--
=================================================
Peter McLarty               E-mail: [EMAIL PROTECTED]
Technical Consultant        WWW: http://www.mincom.com
APAC Technical Services     Phone: +61 (0)7 3303 3461
Brisbane,  Australia        Mobile: +61 (0)402 094 238


                            Facsimile: +61 (0)7 3303 3048

=================================================
A great pleasure in life is doing what people say you cannot do.



    - Walter Bagehot (1826-1877 British Economist)

=================================================
Mincom "The People, The Experience, The Vision"

=================================================

This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. 








"Teresita Castro" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
19-06-2003 10:05 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        VB & Oracle question


Hi!!! 
I create a package in Oracle. 
When I run the line: 
Set adoRS = mCmd.Execute 

VB sent me the next error: 

Run-time error '-214721700 (80040E14)': 
Ora-06550: line 1, column 33: PLS-002001: Identifier 'P_IC' must be declare. 
Ora-06550: line1, column 7: PLS/SQL: Statemnt ignored. 

What I am doing wrong???????? 
This is the code


CREATE OR REPLACE PACKAGE LAWSON1.PACK_ICTRANS AS
CURSOR c1 IS
SELECT ITEM,DOC_TYPE,DOCUMENT FROM ICTRANS;
 
TYPE t_row IS REF CURSOR  RETURN c1%ROWTYPE;
PROCEDURE CL_CURSOR(p_item in varchar2, P_IC  OUT t_row);
 
END PACK_ICTRANS;
 
CREATE OR REPLACE PACKAGE BODY LAWSON1.PACK_ICTRANS AS 
PROCEDURE CL_CURSOR(p_item in varchar2, P_IC  OUT t_row)
  IS
 
  BEGIN
 
     OPEN P_IC FOR
     SELECT ITEM,DOC_TYPE,DOCUMENT FROM ICTRANS WHERE ITEM=p_item;
  END CL_CURSOR;
END PACK_ICTRANS;


This is the code in VB: 

Private Sub cmdTestOracle_Click() 
Dim mCmd As ADODB.Command 
Dim mCmdPrm1 As New ADODB.Parameter 
Set adoRS = New ADODB.Recordset 
If Open_cnOracle Then 

adoRS.CursorType = adOpenDynamic


    sSQL = "{call PACK_ICTRANS.CL_CURSOR(?, {resultset  1000, P_IC})}"

      ' adoRS.Open sSQL, gcnOracle, , , adCmdTable

      Set mCmd = New ADODB.Command

      With mCmd

        .CommandText = sSQL

        .CommandType = adCmdText

        .ActiveConnection = gcnOracle

       

        Set mCmdPrm1 = .CreateParameter("p_item", adVarChar, adParamInput, 32, "0010096")

         .Parameters.Append mCmdPrm1

      End With

    Set adoRS = New ADODB.Recordset

    mCmdPrm1 = "0010096"

    Set adoRS = mCmd.Execute <-----Error here

    MsgBox adoRS.Fields(0)



End If 
End Sub 


Function Open_cnOracle() As Boolean 

Dim oMsgSplitter As New LawsonErrMsgSplitter.Splitter 
Dim vaPieces As Variant 

On Error GoTo ErrorConectarOracle: 
Set gcnOracle = New ADODB.Connection 
With gcnOracle 
ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=burke00;Persist Security Info=True;User ID=lawson1;Data Source=LAWS" 
.CommandTimeout = 0 
.Open 
Open_cnOracle = True 
End With 


Exit Function 
ErrorConectarOracle: 
vaPieces = oMsgSplitter.SplitMsg(Err.Description) 
MsgBox vaPieces(mpText) 
Open_cnOracle = False 

End Function