[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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!

0
tz478
Asked:
tz478
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
PAQ_ManCommented:
Question Closed, 500 points refunded.
PAQ_Man
Community Support Moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now