From Teresita.Castro@s-martmx.com Wed, 18 Jun 2003 18:19:00 -0700 From: "Teresita Castro" Date: Wed, 18 Jun 2003 18:19:00 -0700 Subject: Re: VB & Oracle question Message-ID: MIME-Version: 1.0 Content-Type: text/plain I declare the t_row  here:   CREATE OR  REPLACE PACKAGE LAWSON1.PACK_ICTRANS ASCURSOR c1 ISSELECT ITEM,DOC_TYPE,DOCUMENT  FROM ICTRANS;TYPE t_row IS REF CURSOR  RETURN c1%ROWTYPE;    --Declaration PROCEDURE CL_CURSOR(p_item in varchar2, P_IC  OUT t_row);END PACK_ICTRANS;Where  and how can I declare de P_IC variable?   I don't know a lot of Oracle I was following a book trying to have sucessfull  with this code.       >>> [EMAIL PROTECTED] 06/18/03 06:54PM >>>Ms. Castro:In the procedure declarationPROCEDURE 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, itmust be declared somewhere else, usually in the calling program.  So, avariable definition should exist in the package specification  or bodydeclaring the P_IC variable.RWB============================================================================================================Reginald W. BaileyIBM Global Services - ETS SW GDSD - Database ManagementYour Friendly Neighborhood DBA713-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.ExecuteVB sent me the next error:Run-time error '-214721700 (80040E14)':Ora-06550: line 1,  column 33: PLS-002001: Identifier 'P_IC' must bedeclare.Ora-06550: line1,  column 7: PLS/SQL: Statemnt ignored.What I am doing  wrong????????This is the codeCREATE OR  REPLACE PACKAGE LAWSON1.PACK_ICTRANS ASCURSOR c1 ISSELECT 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 ASPROCEDURE 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.CommandDim mCmdPrm1 As New ADODB.ParameterSet  adoRS = New ADODB.RecordsetIf Open_cnOracle ThenadoRS.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 IfEnd SubFunction  Open_cnOracle() As BooleanDim oMsgSplitter As New  LawsonErrMsgSplitter.SplitterDim vaPieces As VariantOn Error GoTo  ErrorConectarOracle:Set gcnOracle = New ADODB.ConnectionWith gcnOracle.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=burke00;PersistSecurity Info=True;User ID=lawson1;Data Source=LAWS".CommandTimeout = 0.OpenOpen_cnOracle = TrueEnd WithExit FunctionErrorConectarOracle:vaPieces = oMsgSplitter.SplitMsg(Err.Description)MsgBox vaPieces(mpText)Open_cnOracle = FalseEnd 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.comSan Diego, California        -- Mailing list and web hosting services---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).