C # application of calling methods and stored procedures in Oracle package

C # application of calling methods and stored procedures in Oracle package

23:01:21 | category: SQL/Oracle | label: | large font size, small/medium subscription

The following describes how to call the Oracle package in C #, including the package method and stored procedure.

First, create the following package in Oracle:
Package is divided into two parts: spec and body.
1. spec is declared part. Create or replace package firstpage is

Type outlist is ref cursor;

Procedure p_get (maxrow in number, minrow in number, return_list out outlist );

Function f_get (STR in varchar2) return varchar2;

End firstpage;
/

2. The body is the function implementation part. Create or replace package body firstpage is

Procedure p_get (maxrow in number, minrow in number, return_list out outlist)
Is
Begin
Open return_list
Select * from (select a. *, rownum rnum from ips_wl_innoluxpn A where rownum <= maxrow) Where rnum> = minrow;
End;

Function f_get (STR in varchar2)
Return varchar2
Is
Str_temp varchar2 (200): = 'Good luck! ';
Begin
Str_temp: = str_temp | STR;
Return str_temp;
End f_get;

End firstpage;
/

Above, a package named firstpage is created in Oracle, which contains a stored procedure named p_get. It has three parameters: maxrow and minrow, result_list is a cursor used to store the returned dataset.

II. C # part of the Code: String connstr = "Data Source = e4mt; user id = mnt; Password = mnt ";
Oracleconnection orcn = new oracleconnection (connstr );

// C # using the function in the package
Oraclecommand cmd = new oraclecommand ("firstpage. f_get", orcn );
Cmd. commandtype = commandtype. storedprocedure;
Oracleparameter p1 = new oracleparameter ("str", oracletype. varchar, 10 );
P1.direction = parameterdirection. input;
P1.value = "Andy ";
Oracleparameter P2 = new oracleparameter ("result", oracletype. varchar, 100 );
P2.direction = parameterdirection. returnvalue;
Cmd. Parameters. Add (P1 );
Cmd. Parameters. Add (P2 );
Orcn. open ();
Cmd. executenonquery ();
Orcn. Close ();

// C # use procedure in the package
Cmd = new oraclecommand ("firstpage. p_get", orcn );
Cmd. commandtype = commandtype. storedprocedure;
P1 = new oracleparameter ("maxrow", oracletype. number );
P1.direction = parameterdirection. input;
P1.value = 50;
P2 = new oracleparameter ("minrow", oracletype. number );
P2.direction = parameterdirection. input;
P2.value = 10;
Oracleparameter P3 = new oracleparameter ("return_list", oracletype. cursor );
P3.direction = parameterdirection. output;
Cmd. Parameters. Add (P1 );
Cmd. Parameters. Add (P2 );
Cmd. Parameters. Add (P3 );

Datatable dt = new datatable ();
Oracledataadapter da = new oracledataadapter (CMD );
Da. Fill (DT );

Foreach (datarow row in DT. Rows)
{
}