The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.

Welcome to The EntitySpaces Community Sign in | Join | Help

Home

Forums

Photos

The EntitySpaces Community » EntitySpaces API » Defects » How to call stored procedures in a Package in Oracle 10g?

How to call stored procedures in a Package in Oracle 10g?

Last post 07-10-2007, 4:43 PM by curtis. 20 replies.

Page 1 of 2 (21 items) 1 2 Next >
	Sort Posts: Previous Next

03-23-2007, 9:50 AM 1326

Y.Wu
Joined on 03-08-2007
Posts 5

How to call stored procedures in a Package in Oracle 10g?

I am using EntitySpace Version 2007.0.0304.0 with Oracle 10g. My MyGeneration version is 1.2.0.2.

Is there any way I can call stored procedures in a package on Oracle 10g?

The new esUtility class allows me to set the Schema and pass in Stored Procedure name when calling the ExecuteNonQuery method just as shown by the following link:

http://community.entityspaces.net/forums/thread/699.aspx

But where can I specify the package name which contains the stored procedure I want to call? In ADO.NET, I can concatenate the package name and the stored name together with a "." (dot). This does not work in EntitySpaces.

Report abuse

07-05-2007, 3:33 PM 3610 in reply to 1326

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

I couldn't find a response to this question. Where would one define the package name?

' EntitySpaces Version # 2007.0.0528.0
' MyGeneration Version # 1.2.0.7

In my example below the procedure I'd like to call resides in package 'TTN_CUSTOMX'.

Code:

        Public Function ValidateUSERPW(ByVal pUID As String, ByVal pPWD As String, ByRef pResultCode As String, ByRef pResultMessage As String) As Integer
            Dim u As EntitySpaces.Core.esUtility = New EntitySpaces.Core.esUtility
            u.Schema = "RXCPROD"
            Dim esParams As esParameters = New esParameters()
            esParams.Add("pilogin", pUID, esParameterDirection.Input)
            esParams.Add("pipwd", pPWD, esParameterDirection.Input)
            esParams.Add("poretcode", esParameterDirection.Output, DbType.String, 5)
            esParams.Add("poretmsg", esParameterDirection.Output, DbType.String, 2000)
            u.ExecuteNonQuery(esQueryType.StoredProcedure, "TTN_CUSTOMX.VALIDATEUSERPW", esParams)
            MsgBox(esParams.Item("poretcode").Value.ToString)
            MsgBox(esParams.Item("poretmsg").Value.ToString)
        End Function

Report abuse

07-05-2007, 7:26 PM 3616 in reply to 3610

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 3,063

Re: How to call stored procedures in a Package in Oracle 10g?

Have you tried

Code:

u.Catalog = "TTN_CUSTOMX";

Also for pilogin and pipwd you don't have to set the direction, that's the default.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-06-2007, 9:17 PM 3675 in reply to 3616

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

I have now :)

I Get:

System.Data.OracleClient.OracleException was unhandled by user code
ErrorCode=-2146232008
Message="ORA-06550: line 1, column 7:
PLS-00201: identifier 'RXCPROD.TTN_CUSTOMX.VALIDATEUSERPW' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
Source="EntitySpaces.Core"
StackTrace:
       at EntitySpaces.Core.esEntityCollection.ExecuteReader(esQueryType queryType, String query, esParameters parms)
       at EntitySpaces.Core.esUtility.ExecuteReader(esQueryType queryType, String query, esParameters parms)
       at ClubBlazer.TTN_CB_MEMBERS.ValidateUSERPW(String pUID, String pPWD, String& pResultCode, String& pResultMessage) in C:\Naco\DotNet\ClubBlazer\App_Code\Custom\TTN_CB_MEMBERS.vb:line 50
       at Members.Page_Load(Object sender, EventArgs e) in C:\Naco\DotNet\ClubBlazer\Members.aspx.vb:line 16
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

----

This code works however:

Code:

            Dim oraConn As New OracleConnection()
            oraConn.ConnectionString = Conn

            Dim oraCMD As New OracleCommand()
            oraCMD.Connection = oraConn
            oraCMD.CommandText = "TTN_CUSTOMX.VALIDATEUSERPW"
            oraCMD.CommandType = System.Data.CommandType.StoredProcedure

            oraCMD.Parameters.Add(New OracleParameter("PILOGIN", OracleType.VarChar, 2000)).Direction = ParameterDirection.Input
            oraCMD.Parameters.Add(New OracleParameter("PIPWD", OracleType.VarChar, 2000)).Direction = ParameterDirection.Input
            oraCMD.Parameters("PILOGIN").Value = m_PILogin
            oraCMD.Parameters("PIPWD").Value = m_PIPwd

            oraCMD.Parameters.Add(New OracleParameter("PORETCODE", OracleType.VarChar, 2000)).Direction = ParameterDirection.Output
            oraCMD.Parameters.Add(New OracleParameter("PORETMSG", OracleType.VarChar, 2000)).Direction = ParameterDirection.Output


            Try
                oraConn.Open()
                Dim oradr As OracleDataReader
                oradr = oraCMD.ExecuteReader
                oradr.Read()
                m_poRetCode = oraCMD.Parameters("PORETCODE").Value.ToString
                m_poRetMsg = oraCMD.Parameters("PORETMSG").Value.ToString
            Finally
                oraCMD.Dispose()
                oraConn.Close()
            End Try
        End Sub
    End Class

Any clue what I'm doing wrong?

Report abuse

07-07-2007, 4:38 AM 3678 in reply to 3675

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 3,063

Re: How to call stored procedures in a Package in Oracle 10g?

Actually, it just looks like you need to remove that schema I suggested, it cannot find the stored proc, look at the error message, that should tell you why?

PLS-00201: identifier 'RXCPROD.TTN_CUSTOMX.VALIDATEUSERPW' must be declared

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-07-2007, 6:22 AM 3679 in reply to 3678

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

It appears that schema is required:

Code:

[OracleException (0x80131938): ORA-01741: illegal zero-length identifier
]
   EntitySpaces.Core.esEntityCollection.ExecuteReader(esQueryType queryType, String query, esParameters parms) +214
   EntitySpaces.Core.esUtility.ExecuteReader(esQueryType queryType, String query, esParameters parms) +67
   ClubBlazer.TTN_CB_MEMBERS.ValidateUSERPW(String pUID, String pPWD, String& pResultCode, String& pResultMessage) in C:\Naco\DotNet\ClubBlazer\App_Code\Custom\TTN_CB_MEMBERS.vb:51
   Members.Page_Load(Object sender, EventArgs e) in C:\Naco\DotNet\ClubBlazer\Members.aspx.vb:16
   System.Web.UI.Control.OnLoad(EventArgs e) +88
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3036

Report abuse

07-07-2007, 7:12 AM 3680 in reply to 3679

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

u.ExecuteReader("TTN_CUSTOMX", "VALIDATEUSERPW", esParams) does much better, as it finds the procedure in the package, but now there are parameter problems (below is the error)

I've defined the output parms as DbType.String in Entity Spaces, they were previous defined as OracleType.varchar

Code:

System.Data.OracleClient.OracleException was unhandled by user code
  ErrorCode=-2146232008
  Message="ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'VALIDATEUSERPW'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
  Source="EntitySpaces.Core"
  StackTrace:
       at EntitySpaces.Core.esEntityCollection.ExecuteReader(String schema, String storedProcedure, esParameters parameters)
       at EntitySpaces.Core.esUtility.ExecuteReader(String schema, String storedProcedure, esParameters parms)
       at ClubBlazer.TTN_CB_MEMBERS.ValidateUSERPW(String pUID, String pPWD, String& pResultCode, String& pResultMessage) in C:\Naco\DotNet\ClubBlazer\App_Code\Custom\TTN_CB_MEMBERS.vb:line 54
       at Members.Page_Load(Object sender, EventArgs e) in C:\Naco\DotNet\ClubBlazer\Members.aspx.vb:line 16
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Report abuse

07-07-2007, 12:43 PM 3685 in reply to 3680

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 3,063

Re: How to call stored procedures in a Package in Oracle 10g?

We have our July 9th maintenance release fully tested, locked, and loaded. Important, if there is any chance of getting a fix in this release you need to post these three things in the new few hours or so:

1) The Create Table statement for the table in question

2) The stored proc text

3) Your oracle connection string without the password (I want to see how you define your connection string)

Please post these ASAP as Time time is of the essence.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-07-2007, 12:48 PM 3686 in reply to 3685

David.Parsons
Joined on 01-14-2007
Posts 1,421

Re: How to call stored procedures in a Package in Oracle 10g?

Also, please repost the ValidateUSERPW() method, as I think it has changed since your original post.

David Neal Parsons
www.entityspaces.net

Report abuse

07-07-2007, 3:22 PM 3690 in reply to 3685

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

I think I have together what you need:

=========================

Code to create package cca:

CREATE OR REPLACE Package CCA
  IS
    PROCEDURE esTest(
      sInParm   IN VARCHAR2,
      sOutParm  OUT VARCHAR2);

  END CCA;

CREATE OR REPLACE Package BODY CCA
IS


   PROCEDURE esTest(
      sInParm   IN VARCHAR2,
      sOutParm Out varchar2)
   IS

   BEGIN
     sOutParm := sInParm || ' Returned Value';
   END esTest;

END CCA;

============

Code: a sample table creation
-- Create table
create table TTN_CB_QUESTIONS
(
  CB_FIELD_ID  NUMBER(10) not null,
  DESCRIPTION  VARCHAR2(100) not null,
  CUSTOM_ORDER NUMBER,
  CREATED      DATE default SYSDATE,
  MODIFIED     DATE default SYSDATE
)
tablespace REZ_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TTN_CB_QUESTIONS
  add constraint PK_FIELD_ID primary key (CB_FIELD_ID)
  using index 
  tablespace REZ_INDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 80K
    minextents 1
    maxextents unlimited
  );

 =========

Code (custom class):
'===============================================================================
'                     EntitySpaces(TM) by EntitySpaces, LLC
'                 A New 2.0 Architecture for the .NET Framework
'                          http://www.entityspaces.net
'===============================================================================
'                       EntitySpaces Version # 2007.0.0528.0
'                       MyGeneration Version # 1.2.0.7
'                           7/7/2007 5:01:31 PM
'-------------------------------------------------------------------------------

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Data

Imports EntitySpaces.Interfaces

Namespace BusinessObjects

	Partial Public Class TTN_CB_QUESTIONS 
		Inherits esTTN_CB_QUESTIONS
        Public Function esTest(ByVal pInput As String) As String

            Dim esParams As esParameters = New esParameters()
            esParams.Add("sInParm", pInput, esParameterDirection.Input)
            esParams.Add("sOutParm", esParameterDirection.Output, DbType.String, 2000)
            Me.ExecuteReader("CCA", "ESTEST", esParams)
            esTest = esParams.Item("sOutParm").Value.ToString
        End Function
 


	End Class
 


End Namespace

 and finally, the error:

Code:
Server Error in '/estest' Application.
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ESTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ESTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Source Error:

Line 29:             esParams.Add("sInParm", pInput, esParameterDirection.Input)
Line 30:             esParams.Add("sOutParm", esParameterDirection.Output, DbType.String, 2000)
Line 31:             Me.ExecuteReader("CCA", "ESTEST", esParams)
Line 32:             esTest = esParams.Item("sOutParm").Value.ToString
Line 33:         End Function


Source File: C:\Naco\DotNet\estest\App_Code\Custom\TTN_CB_QUESTIONS.vb    Line: 31

Stack Trace:

Report abuse

07-07-2007, 5:04 PM 3692 in reply to 3690

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 3,063

Re: How to call stored procedures in a Package in Oracle 10g?

Oracle is very finicky. I've added support for 13 databases to MyGeneration and I can tell you that Oracle is the most unforgiving. Particularly the outcursor stuff. Your code is calling ExecuteReader yet no result set is being returned. I was able to get your proc to work using ExecuteNonQuery which doesn't expect a result set to be returned.

First, my proc (your's basically but created in our MyGeneration oracle schema)

Code:

CREATE PROCEDURE "MYGENERATION"."ESTEST" 
( 
    sInParm IN VARCHAR2, 
    sOutParm Out varchar2 
) 
IS 

BEGIN 
    sOutParm := sInParm || ' Returned Value'; 
END esTest;

Now my code, however, forgive me, my Oracle test app is in C#.

Code:

esUtility u = new esUtility();
esParameters parms = new esParameters();

parms.Add("sInParm", "42", esParameterDirection.Input, DbType.String, 2000);
parms.Add("sOutParm", esParameterDirection.Output, DbType.String, 2000);
u.ExecuteNonQuery("MYGENERATION", "ESTEST", parms);

string o = parms["sOutParm"].Value.ToString();

The output was ....

42 Returned Value

Also, we have a table called "AggregateTest" and this code invokes it's LoadByPrimaryKey stored procedure just fine returning a DataReader

Code:

esUtility u = new esUtility();
esParameters parms = new esParameters();

parms.Add("pID", 1);
IDataReader reader = u.ExecuteReader("MYGENERATION", "esAggregateTestLoadByPK", parms);

reader.Read();

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-07-2007, 7:59 PM 3698 in reply to 3692

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

After changing the executereader to executenonquery, all works as expected. I don't understand why it works in one place and not in another, But you know what? There is a lot I don't understand, so, I'm not going to worry over it. Thanks for your superior response as usual.

Curtis

Report abuse

07-10-2007, 9:08 AM 3759 in reply to 3698

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

One more challenge for you :)

Create an es example that calls the following procedure (the data table is defined in an example above)

Code:

CREATE OR REPLACE Package BODY CCA
IS


   PROCEDURE esTest(poCursor out sys_refcursor)
   IS

   BEGIN
   open pocursor for
   select * from ttn_cb_questions;
   end estest;

END CCA;

I've tried numerous options (filldatatable, filldataset, and ..

Code:

    Public Sub GetCursor()
        Dim u As esUtility = New esUtility
        Dim reader As IDataReader
        reader = u.ExecuteReader("CCA", "ESTEST")
        reader.Read()
    End Sub

but no luck

I realize that I could for this example created a view to return the same information, but I am just using this as a simple example. Some of the procedures I need to call are fairly complex.

Curtis

Report abuse

07-10-2007, 9:23 AM 3760 in reply to 3759

Mike.Griffin
Joined on 01-14-2007
Indianapolis
Posts 3,063

Re: How to call stored procedures in a Package in Oracle 10g?

Look at my post from above that does this:

Code:

esUtility u = new esUtility();
esParameters parms = new esParameters();

parms.Add("pID", 1);
IDataReader reader = u.ExecuteReader("MYGENERATION", "esAggregateTestLoadByPK", parms);

reader.Read();

Notice that it calls one of our generated store procedures, the LoadByPrimaryKey proc in fact. It uses an outcursor and I use ExecuteReader() to execute it. Try looking at one of our stored procedures and see how it might be different from yours. The stored procedure template for oracle is shipped with EntitySpaces.

EntitySpaces | Twitter | BLOG | Please honor our Software License

Report abuse

07-10-2007, 10:35 AM 3764 in reply to 3760

curtis
Joined on 01-31-2007
Posts 34

Re: How to call stored procedures in a Package in Oracle 10g?

I looked at the Oracle generated stored procedures, and see two differences:

1) i'm wasnt' passing any input parms (I added one for grins, still no go)

2) The generated procedures are not in packages. You created a package "EntitySpaces", but the procedures were not created in that package.

' EntitySpaces Version # 2007.0.0528.0
' MyGeneration Version # 1.2.0.7

Report abuse

Page 1 of 2 (21 items) 1 2 Next >