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.
oracle procedures accepting user defined types as paramaters
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:
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();
}