How to call Oracle PL/SQL (ODP. Net)

Source: Internet
Author: User
Tags dname oracleconnection

The following code uses system. Data. oracleclient (. NET for Oracle Data Provider ).

1. How to call functions with return values in Oracle PL/SQL? It can be called by matching the command and parameter objects.

The added parameter is matched as the return value and parameter. The first parameter is the return value, and then the parameters passed into PL/SQL.

Private void button#click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.say_hello ";

Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;

// Param returnvalue:
Cmd. Parameters. Add ("po_return", oracletype. varchar, 20 );
Cmd. Parameters ["po_return"]. Direction = parameterdirection. returnvalue;

// Param 1: note that here pi_name must match the name in PLSQL (Case Insensitive), otherwise a ORA-06550 error is thrown.
Cmd. Parameters. Add ("pi_name", oracletype. varchar, 10 );
Cmd. Parameters ["pi_name"]. Direction = parameterdirection. input;
Cmd. Parameters ["pi_name"]. value = "Firefox ";

Try
{
Conn. open ();
Cmd. executenonquery ();
String strret = cmd. Parameters ["po_return"]. value. tostring ();
MessageBox. Show (strret );
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
Conn. Close ();
}

}

Well, use some tips to call PL/SQL functions with returned values: Select... from dual

/// <Summary>
/// Call the function of oracle.
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>
Private void button2_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";

Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand ();

Cmd. Connection = conn;
Cmd. commandtype = commandtype. text;
Cmd. commandtext = "select plsql_hello_world.say_hello (: pi_name) from dual ";

Cmd. Parameters. Add ("pi_name", oracletype. varchar, 20 );
Cmd. Parameters ["pi_name"]. Direction = parameterdirection. input;
Cmd. Parameters ["pi_name"]. value = "Firefox ";

Try
{
Conn. open ();
String strret = cmd. executescalar () as string;
MessageBox. Show (strret );
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
Conn. Close ();
}
}

How to return the cursor. Here, cursor can only be output parameter. You must use executereader () to obtain cursor using command ()

Private void button3_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.open_one_cursor ";

Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;

// Param 1:
Cmd. Parameters. Add ("n_empno", oracletype. Number, 2 );
Cmd. Parameters ["n_empno"]. Direction = parameterdirection. input;
Cmd. Parameters ["n_empno"]. value = 0;
// Param 2:
Cmd. Parameters. Add ("io_cursor", oracletype. cursor );
Cmd. Parameters ["io_cursor"]. Direction = parameterdirection. output;

Oracledatareader ODR = NULL;

Try
{
Conn. open ();
ODR = cmd. executereader ();
While (ODR. Read ())
{
MessageBox. Show (ODR. getvalue (0). tostring ());
}
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
If (ODR! = NULL)
ODR. Close ();
Conn. Close ();
}
}

Of course, you can also use oracledataadapter:

Private void button5_click (Object sender, system. eventargs E)
{
String connectionstring = "User ID = Scott; Password = tiger; Data Source = Firefox ;";
String strplsql = "plsql_hello_world.open_one_cursor ";

Oracleconnection conn = new oracleconnection (connectionstring );
Oraclecommand cmd = new oraclecommand (strplsql, Conn );
Cmd. commandtype = commandtype. storedprocedure;

// Param 1:
Cmd. Parameters. Add ("n_empno", oracletype. Number, 2 );
Cmd. Parameters ["n_empno"]. Direction = parameterdirection. input;
Cmd. Parameters ["n_empno"]. value = 30;
// Param 2:
Cmd. Parameters. Add ("io_cursor", oracletype. cursor );
Cmd. Parameters ["io_cursor"]. Direction = parameterdirection. output;

Oracledataadapter ADP = new oracledataadapter ();
ADP. selectcommand = cmd;

Try
{
Datatable dt = new datatable ();
ADP. Fill (DT );
This. Maid = DT;
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
}

 

Appendix: PL/SQL

Create or replace package body plsql_hello_world is </P> <p> function say_hello (pi_name in varchar2) return varchar2 is <br/> v_hello varchar2 (20 ); <br/> begin <br/> If pi_name is null then <br/> v_hello: = 'Hello world! '; <Br/> else <br/> v_hello: = 'hello' | pi_name; <br/> end if; <br/> return (v_hello ); <br/> end say_hello; </P> <p> procedure open_one_cursor (n_empno in number, <br/> io_cursor in out type_cursor) <br/> is <br/> v_cursor type_cursor; <br/> begin <br/> If n_empno = 10 or n_empno = 20 <br/> or n_empno = 30 then <br/> open v_cursor for <br/> select EMP. empno, <br/> EMP. ename, <br/> Dept. deptno, <br/> Dept. dname <br/> from EMP, DEPT <br/> where EMP. deptno = Dept. deptno <br/> and EMP. deptno = n_empno; <br/> else <br/> open v_cursor for <br/> select EMP. empno, <br/> EMP. ename, <br/> Dept. deptno, <br/> Dept. dname <br/> from EMP, DEPT <br/> where EMP. deptno = Dept. deptno; <br/> end if; <br/> io_cursor: = v_cursor; <br/> end open_one_cursor; </P> <p> procedure open_two_cursors (empcursor out type_cursor, <br/> deptcursor out type_cursor) <br/> is <br/> v_cursor1 type_cursor; <br/> v_cursor2 type_cursor; <br/> begin <br/> open v_cursor1 for select * from EMP; <br/> open v_cursor2 for select * from Dept; <br/> empcursor: = v_cursor1; <br/> deptcursor: = v_cursor2; <br/> end open_two_cursors; </P> <p> end plsql_hello_world; <br/>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.