tz478
asked on
access oracle db via stored procedure
I am trying to get a set of data from oracle by using store procedure with a reference cursor. I got the following err msg:
ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: PL/SQL: Statement ignored
There was an exact same question posted last year on EE. However, when I tried the solution, I still get the same err.
https://www.experts-exchange.com/questions/20879432/data-access-from-Oracle.html
Here is my code:
dim connOra as New OracleConnection(strConn)
connOra.Open()
dim oOraCmd as New OracleCommand(String.Empty , connOra)
oOraCmd.CommandType = Data.CommandType.StoredPro cedure
oOraCmd.CommandText = "HDWPD001.myPkg.mySP()"
oOraCmd.Parameters.Add(New OracleParameter("cur", OracleType.Cursor)).Direct ion = ParameterDirection.Output
dim oDA as New OracleDataAdapter(oOraCmd)
dim oDS as New DataSet
oDA.Fill(oDS)
Here is my oracle procedure(Ref_Cursor has been defined in spec):
CREATE OR REPLACE PACKAGE BODY HDWPD001.myPkg AS
PROCEDURE mySP(cur OUT Ref_CURSOR)
IS
BEGIN
OPEN CUR FOR select * from myTbl;
END mySP;
END myPkg;
I am using oracle 8i and ASP.net 2.0 (VS2005 beta 2). Thanks!
ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: PL/SQL: Statement ignored
There was an exact same question posted last year on EE. However, when I tried the solution, I still get the same err.
https://www.experts-exchange.com/questions/20879432/data-access-from-Oracle.html
Here is my code:
dim connOra as New OracleConnection(strConn)
connOra.Open()
dim oOraCmd as New OracleCommand(String.Empty
oOraCmd.CommandType = Data.CommandType.StoredPro
oOraCmd.CommandText = "HDWPD001.myPkg.mySP()"
oOraCmd.Parameters.Add(New
dim oDA as New OracleDataAdapter(oOraCmd)
dim oDS as New DataSet
oDA.Fill(oDS)
Here is my oracle procedure(Ref_Cursor has been defined in spec):
CREATE OR REPLACE PACKAGE BODY HDWPD001.myPkg AS
PROCEDURE mySP(cur OUT Ref_CURSOR)
IS
BEGIN
OPEN CUR FOR select * from myTbl;
END mySP;
END myPkg;
I am using oracle 8i and ASP.net 2.0 (VS2005 beta 2). Thanks!
ASKER
I tested the store procedure in Oracle and it worked.
After I posted the question, I tested the same code in VS2003 (ASP.net 1.1) and it generated the same error.
After I posted the question, I tested the same code in VS2003 (ASP.net 1.1) and it generated the same error.
Is your stored proc returning zero rows? I got the same error when there were no rows to be returned in the cursor.
ASKER
It returned many rows.
ASKER
I accidentally find out the problem with my code. All I need to do is to remove the parenthesis after stored procedure name. Make it like this:
oOraCmd.CommandText = "HDWPD001.myPkg.mySP"
I am not sure if this makes sense to you guys. I just post this fact here for future reference.
oOraCmd.CommandText = "HDWPD001.myPkg.mySP"
I am not sure if this makes sense to you guys. I just post this fact here for future reference.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AS I see You dont have errors in your asp.net code. maybe in the storedprocedure.
http://ora-06550.ora-code.com/