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 codeCREATE 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 isthe 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
Re: VB Oracle question
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
Re: VB Oracle question
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.RWBReginald
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 wrongThis 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
isthe 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).
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 McLartyE-mail: [EMAIL PROTECTED]
Technical ConsultantWWW: http://www.mincom.com
APAC Technical Services Phone: +61 (0)7 3303 3461
Brisbane, AustraliaMobile: +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
VB/Oracle question
If I was going to distribute a client VB application connecting to Oracle, would I need to also distribute the Oracle Client tools or is there a small driver package to install without all the tools? Thanks, Brian King mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian King INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: VB/Oracle question
How are you connecting - if you are using odbc you will need the Oracle client software. -Original Message- Sent: Wednesday, September 26, 2001 10:50 AM To: Multiple recipients of list ORACLE-L If I was going to distribute a client VB application connecting to Oracle, would I need to also distribute the Oracle Client tools or is there a small driver package to install without all the tools? Thanks, Brian King mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian King INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw, John B INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: VB/Oracle question
You will need to distribute the drivers as well, of course, it depends on the type of interface, ADO, DAO, OLE. Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, September 26, 2001 11:50 AM To: Multiple recipients of list ORACLE-L If I was going to distribute a client VB application connecting to Oracle, would I need to also distribute the Oracle Client tools or is there a small driver package to install without all the tools? Thanks, Brian King mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian King INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: VB/Oracle question
We will be connecting OLEDB... -Original Message- B Sent: Wednesday, September 26, 2001 12:55 PM To: Multiple recipients of list ORACLE-L How are you connecting - if you are using odbc you will need the Oracle client software. -Original Message- Sent: Wednesday, September 26, 2001 10:50 AM To: Multiple recipients of list ORACLE-L If I was going to distribute a client VB application connecting to Oracle, would I need to also distribute the Oracle Client tools or is there a small driver package to install without all the tools? Thanks, Brian King mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian King INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw, John B INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian King INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
