Link to home
Start Free TrialLog in
Avatar of tz478
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.StoredProcedure
   oOraCmd.CommandText = "HDWPD001.myPkg.mySP()"
   oOraCmd.Parameters.Add(New OracleParameter("cur", OracleType.Cursor)).Direction = 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!

Avatar of djhex
djhex

Please see this.

AS I see You dont have errors in your asp.net code. maybe in the storedprocedure.

http://ora-06550.ora-code.com/
Avatar of tz478

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.
Is your stored proc returning zero rows? I got the same error when there were no rows to be returned in the cursor.
Avatar of tz478

ASKER

It returned many rows.
Avatar of tz478

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.
ASKER CERTIFIED SOLUTION
Avatar of PAQ_Man
PAQ_Man
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial