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
|
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
