Re: VB & Oracle question

From: <Reginald.W.Bailey_at_jpmorgan.com>
Date: Wed, 18 Jun 2003 17:16:41 -0700
Message-ID: <F001.005B48B3.20030618165437@fatcity.com>

Ms. Castro:

In the procedure declaration
PROCEDURE CL_CURSOR(p_item in varchar2, P_IC  OUT t_row) the P_IC is referenced as an OUT variable of type t_row. But to use it, it must be declared somewhere else, usually in the calling program. So, a variable definition should exist in the package specification or body declaring the P_IC variable.

RWB

---

Reginald W. Bailey
IBM Global Services - ETS SW GDSD - Database Management Your Friendly Neighborhood DBA
713-216-7703 (Office) 281-798-5474 (Mobile) 713-415-5410 (Pager)

---

                                                                                       
                                          
                    [EMAIL PROTECTED]                                                  
                                         
                    martmx.com               To:     [EMAIL PROTECTED]                 
                                       
                    Sent by:                 cc:                                       
                                          
                    [EMAIL PROTECTED]       Subject:     VB & Oracle question          
                                         
                    com                                                                
                                          
                                                                                       
                                          
                                                                                       
                                          
                    06/18/2003 07:05                                                   
                                          
                    PM                                                                 
                                          
                    Please respond to                                                  
                                          
                    ORACLE-L                                                           
                                          
                                                                                       
                                          
                                                                                       
                                          

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jun 18 2003 - 19:16:41 CDT