Solve the problem that dataset is returned when the stored procedure is executed in Oracle in C #.

Solution 1

After the stored procedure is executed, dataset is returned:

1. There must be an output parameter cursor in the stored procedure to return the Stored Procedure

 
 

  
  
 
  
-- Create a temporary table returned by the Stored Procedure
 
  
  
 
  
Create global temporary table tmp_his_pptn_jp
 
  
  
 
  
(
 
  
  
 
  
Stcd varchar2 (12) NotNull,
 
  
  
 
  
Stnm varchar2 (50 ),
 
  
  
 
  
Addvcd varchar2 (6 ),
 
  
  
 
  
Rgnnm varchar2 (50 ),
 
  
  
 
  
Hisavg number (13, 3 ),
 
  
  
 
  
ACCP number (10, 1 ),
 
  
  

  
JP number (10, 2)
 
  
  
 
  
)
 
  
  
 
  
On commit Delete rows; -- [1] delete data after the transaction is completed
 
  
  
 
  
Alter table tmp_his_pptn_jp
 
  
  
 
  
Add primary key (stcd );
 
  
  
 
  
-- Create a stored procedure
 
  
  
 
  
Create or replace procedure proc_rain_jp (
 
  
  
 
  
V_stcds varchar2, -- requires v_ptm1, v_ptm2 not to crash into years, and returns the temporary table tmp_his_pptn_jp
 
  
  
 
  
V_ptm1 varchar2,
 
  
  
 
  
V_ptm2 varchar2,
 
  
  

  
V_cs out sys_refcursor
 
  
  
 
  
)
 
  
  
 
  
As
 
  
  
 
  
-- Define variables ......
 
  
  
 
  
Begin
 
  
  
 
  
-- Data processing ......
 
  
  
 
  
Open v_cs for select * From tmp_his_pptn_jp;
 
  
  
 
  
Return;
 
  
  
 
  
End;
 
 
 

2. Run the stored procedure in C # using Oracle

 
 

  
  

  
Idbconnection con =This. Dbinterface. createconnection ();// Custom data access interface 
 
  
  
 
  
Con. open ();
 
  
  
 
  
 
 
  
  
 
  
Idbtransaction trans = con. begintransaction ();
 
  
  
 
  
Cmd = con. createcommand ();
 
  
  
 
  
Cmd. Transaction = trans;// SET transaction for command 
 
  
  
 
  
 
 
  
  
 
  
Cmd. commandtype = system. Data. commandtype. storedprocedure;
 
  
  

  
Cmd. commandtext ="Proc_rain_jp";
 
  
  
 
  
System. Data. oracleclient. oracleparameter P;
 
  
  
 
  
P =NewSystem. Data. oracleclient. oracleparameter ("V_stcds", System. Data. oracleclient. oracletype. varchar, 2000 );
 
  
  
 
  
Cmd. Parameters. Add (P );
 
  
  
 
  
P. Direction = system. Data. parameterdirection. input;
 
  
  
 
  
P. value = STC;
 
  
  
 
  
 
 
  
  

  
P =NewSystem. Data. oracleclient. oracleparameter ("V_ptm1", System. Data. oracleclient. oracletype. varchar, 20 );
 
  
  
 
  
Cmd. Parameters. Add (P );
 
  
  
 
  
P. Direction = system. Data. parameterdirection. input;
 
  
  
 
  
P. value =This. Getparamvalue ("Sdate");
 
  
  
 
  
 
 
  
  
 
  
P =NewSystem. Data. oracleclient. oracleparameter ("V_ptm2", System. Data. oracleclient. oracletype. varchar, 20 );
 
  
  
 
  
Cmd. Parameters. Add (P );
 
  
  

  
P. Direction = system. Data. parameterdirection. input;
 
  
  
 
  
P. value =This. Getparamvalue ("Edate");
 
  
  
 
  
 
 
  
  
 
  
 
 
  
  
 
  
// Output Dataset 
 
  
  
 
  
P =NewSystem. Data. oracleclient. oracleparameter ("V_cs", System. Data. oracleclient. oracletype. cursor );
 
  
  
 
  
Cmd. Parameters. Add (P );
 
  
  
 
  
P. Direction = system. Data. parameterdirection. output;// Set it to output 
 
  
  

  
 
 
  
  
 
  
Dataset DS =NewDataset ();
 
  
  
 
  
Idbdataadapter da =NewSystem. Data. oracleclient. oracledataadapter (CMDAsSystem. Data. oracleclient. oraclecommand );
 
  
  
 
  
Da. Fill (DS );
 
  
  
 
  
 
 
  
  
 
  
Trans. Commit ();
 
  
  
 
  
If(Con. State! = Connectionstate. Closed)
 
  
  
 
  
...{
 
  
  
 
  
Try 
 
  
  

  
...{
 
  
  
 
  
Con. Close ();
 
  
  
 
  
}
 
  
  
 
  
Catch 
 
  
  
 
  
...{
 
  
  
 
  
}
 
  
  
 
  
}
 
  
  
 
  
ReturnDS. Tables [0];
 
  
  
 
  
 
 
 
 

3. Pay attention to the above C #CodeWe open a transaction. Why:

If we do not use transactions, there is no problem in sqlplus debugging, but in yes. net execution time will be reported ORA-08103: Object no longer exists error, the reason is in the stored procedure, the temporary table creation option is changed from on commit Delete rows [1] to on commit preserve rows. when querying temporary table data on the. NET page, more duplicate records are required for each query. The reason is that the session connection of Oracle is not completed. Therefore, records must be inserted before each execution of the stored procedure. Why is Oracle session not over? It must be an ASP. NET service.ProgramThe connection to the database is maintained in the data connection pool. However, for the sake of performance, we cannot use the connection pool. In this way, temporary tables based on Oracle sessions cannot be used.

Return to the temporary table based on Oracle transactions, that is, use on commit Delete rows to create the temporary table. Then, the ODP transaction processing mechanism is called in the ASP. NET application, so that the problem of returning dataset through Oracle execution in C # can be solved!

Note:

(1) theoretically, do not execute commit in the stored procedure, that is, do not use PL/SQL transaction processing in the stored procedure; otherwise, Asp. NET page cannot obtain data, because after commit, the data in the temporary table is automatically cleared.

(2) theoretically, the Oracle transaction processing mechanism provided by oledb or Microsoft should also work without ODP.

 

Solution 2

-- Baotou
Create or replace package pkg_test
As
Type mycursor is ref cursor;
Function get (p_id number) return mycursor;
End pkg_test;


-- Package body
Create or replace package body pkg_test
As
-- Input ID to return the record set function
Function get (p_id number) return mycursor is
RC mycursor;
Strsql varchar2 (200 );
Begin
If p_id = 0 then
Open RC for select a. user_name from fnd_user;
Else
Strsql: = 'select A. user_name from fnd_user A where a. user_id =: p_id ';
Open RC for strsql using p_id;
End if;
Return RC;
End get;

End pkg_test;

-- Call Test
Set serverout on
Declare
W_rc pkg_test.mycursor;
W_name varchar2 (100 );
Begin
W_rc: = pkg_test.get (0 );
Loop
Fetch w_rc into w_name;
Exit when w_rc % notfound;
Dbms_output.put_line (w_name );
End loop;
End;
/

 

We can see that the second scheme adds a cursor Declaration (type mycursor is ref cursor) in the header definition. In fact, in the first scheme, we use "sys_refcursor ", this is what the system has defined for us. Therefore, the second solution seems a bit redundant.