Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: VB & Oracle question

Re: VB & Oracle question

From: <Peter.McLarty_at_mincom.com>
Date: Wed, 18 Jun 2003 18:36:49 -0700
Message-ID: <F001.005B4955.20030618181418@fatcity.com>


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

Received on Wed Jun 18 2003 - 20:36:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US