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.
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_20879432.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!

LVL 1
tz478Asked:
Who is Participating?
 
PAQ_ManConnect With a Mentor Commented:
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0
 
djhexCommented:
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/
0
 
tz478Author Commented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dharmesh_amityCommented:
Is your stored proc returning zero rows? I got the same error when there were no rows to be returned in the cursor.
0
 
tz478Author Commented:
It returned many rows.
0
 
tz478Author Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.