oracle procedures accepting user defined types as paramaters

[krchome]

Hi,

I wish to call and execute an oracle stored procedure within a package.The sproc accepts two parameters as input which are of user defined types(UDT). How do I implement the same in asp.net 4.0? For help the following code snippets are shown for the Oracle types:
PROCEDURE Save_worksheet (Pworksheetdaytbl IN Ts_worksheet_day_tbl,
Pworksheetdaysectiontbl IN Ts_worksheet_day_section_tbl,
Puser IN Varchar2 DEFAULT USER
)

TS_WORKSHEET_DAY_TBL
TYPE "TS_WORKSHEET_DAY_TBL" AS TABLE OF WORKSHEET_DAY_Type;
TYPE "TS_WORKSHEET_DAY_TYPE" AS OBJECT
(
ID NUMBER,
WORKSHEET_ID NUMBER,
DAY_DATE DATE,
DAY_RANK NUMBER,
NUM_OF_CALLOUT NUMBER,
IF_STANDBY VARCHAR2(1),
IF_ALTERNATE_DAY VARCHAR2(1)
)


Day section is similiar

Do you have an example that i can follow to get this working with devart dot connect.

I need to call and execute the above stored procedure from asp.net 4 application.

My asp.net application code is something as follows;

string connstr = "user id=aaa;password=bbb;server=xxx";//This is the OCI mode of connection to work with Oracle Objects
OracleConnection conn = new OracleConnection(connstr);
conn.Open();


OracleObject worksheetdaytype = new OracleObject("TS_WORKSHEET_DAY_TYPE", conn);
worksheetdaytype["ID"] = 1;
worksheetdaytype["WORKSHEET_ID"] = 1;
worksheetdaytype["DAY_DATE"] = Convert.ToDateTime("06/10/2011");
worksheetdaytype["DAY_RANK"] = 4;
worksheetdaytype["NUM_OF_CALLOUT"] = 3;
worksheetdaytype["IF_STANDBY"] = "N";
worksheetdaytype["IF_ALTERNATE_DAY"] = "N";

//OracleTable worksheetdaytype_tbl = new OracleTable(worksheetdaytype);
// OracleTable worksheetdaytype_tbl = new OracleTable("TS_WORKSHEET_DAY_TBL");
OracleObject worksheetdaytype_tbl = new OracleObject("TS_WORKSHEET_DAY_TBL", conn);
worksheetdaytype_tbl["ID"] = 1;
worksheetdaytype_tbl["WORKSHEET_ID"] = 1;
worksheetdaytype_tbl["DAY_DATE"] = Convert.ToDateTime("06/10/2011");
worksheetdaytype_tbl["DAY_RANK"] = 4;
worksheetdaytype_tbl["NUM_OF_CALLOUT"] = 3;
worksheetdaytype_tbl["IF_STANDBY"] = "N";
worksheetdaytype_tbl["IF_ALTERNATE_DAY"] = "N";

OracleObject worksheetdaysectiontype_tbl = new OracleObject("TS_WORKSHEET_DAY_SECTION_TBL", conn);



OracleObject worksheetdaysectiontype = new OracleObject("TS_WORKSHEET_DAY_SECTION_TYPE", conn);
worksheetdaysectiontype_tbl["ID"]= 1;
worksheetdaysectiontype_tbl["WORKSHEET_ID"] = 1;
worksheetdaysectiontype_tbl["WORKSHEET_DAY_ID"] = 1;
worksheetdaysectiontype_tbl["SECTION_RANK"] = 1;
worksheetdaysectiontype_tbl["START_TIME"] = "08:00";
worksheetdaysectiontype_tbl["END_TIME"] = "10:00";


OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "TS_WORKSHEET_UTL.Save_worksheet";
cmd.Parameters.Add(new OracleParameter("Pworksheetdaytbl", OracleDbType.Table));
cmd.Parameters["Pworksheetdaytbl"].ObjectTypeName = "TS_WORKSHEET_DAY";
cmd.Parameters["Pworksheetdaytbl"].Value = worksheetdaytype_tbl;
cmd.Parameters.Add(new OracleParameter("Pworksheetdaysectiontbl", OracleDbType.Table));
cmd.Parameters["Pworksheetdaysectiontbl"].ObjectTypeName = "TS_WORKSHEET_DAY_SECTION";
cmd.Parameters["Pworksheetdaysectiontbl"].Value = worksheetdaysectiontype_tbl;

cmd.Parameters.Add(new OracleParameter("Puser", "abba"));

cmd.ExecuteNonQuery();

I am getting an error object reference not set to an instance of object. Please some in your devart team should help me.

[Shalex]

1. User-defined types declared in a package cannot be used outside of this package. We suggest defining UDTs globally.

2. Here is a small example which illustrates the way how OracleTable can be passed as a parameter of stored procedure:

Code: Select all

DDL:
CREATE OR REPLACE TYPE t_record AS object (id integer, username varchar2(10));
/
CREATE OR REPLACE TYPE T_RECORDS AS
    TABLE OF T_RECORD;
/

CREATE OR REPLACE PACKAGE TBLPACKAGE AS
PROCEDURE tblPROCEDURE(p_in IN T_RECORDS);
END TBLPACKAGE;
/
CREATE OR REPLACE PACKAGE BODY TBLPACKAGE AS
PROCEDURE tblPROCEDURE(p_in IN T_RECORDS)
IS
BEGIN
null;
END tblPROCEDURE;
END TBLPACKAGE;
/


C#:
    using (OracleConnection conn = new OracleConnection()) {
        conn.ConnectionString = "server=orcl1120;uid=***;pwd=***;";
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "TBLPACKAGE.tblPROCEDURE";

        OracleTable tbl = new OracleTable(OracleType.GetObjectType("T_RECORDS", conn));

        OracleObject obj = new OracleObject(OracleType.GetObjectType("T_RECORD", conn));
        obj["id"] = 1;
        obj["username"] = "abcd";
        tbl.Add(obj);

        cmd.Parameters.Add("p_in", OracleDbType.Table).Value = tbl;
        cmd.ExecuteNonQuery();
    }