oracle procedures accepting user defined types as paramaters
Posted: Sat 08 Oct 2011 00:11
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.
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.