?
Solved

How to return OUT variable value from Oracle stored procedure to .NET (C#) code?

Posted on 2008-10-19
19
Medium Priority
?
9,663 Views
Last Modified: 2013-12-17
I'm working on an application which may use multiple database types (MySQL, MSSQL, Oracle). Since the queries (and mainly stored procedures calls) are different on these databases the calls are defined in XML (as strings).
E.g. (for MySQL) I call stored procedure this way:
set @id=0; call insert_record(@id, '{0}'); select @id;

I call this within C# code using .ExecuteScalar() - the result is dataset with 1 column and 1 row (ID of the newly inserted row). Procedure insert_record is defined this way:

CREATE PROCEDURE `insert_record`(OUT id INT, p_value VARCHAR(150))
BEGIN
  INSERT INTO data (value)
    VALUES (p_value);
  SET id = LAST_INSERT_ID();
END;

I really don't want to change the C# code because of Oracle implementation and therefore I'm looking for a way how to execute a stored procedure and return its OUT parameter within a dataset.

I'm using the Oracle .NET library (Oracle.DataAccess.Client) provided by Oracle.

Thanks for your help guys :).
0
Comment
Question by:petr_hlucin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
19 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 22785916
The statement

  SET id = LAST_INSERT_ID();

is not PL/SQL statement. Possibly it comes from other DB procedural language.

in Oracle you can use another solution.

1. define a sequence
2. use nextval pseudocolumn to insert the id:

CREATE PROCEDURE insert_record (OUT id INT, p_value VARCHAR(150))
BEGIN
  id := seqience_name.nextval;
  INSERT INTO data (id, value)
              VALUES (id, p_value);
END;
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22785940
Bad syntax:

CREATE PROCEDURE insert_record     ( id OUT INT,       p_value VARCHAR(150))
BEGIN
      id := sequence_name.nextval;
      INSERT INTO data (id, value)
                   VALUES (id, p_value);
END;
/
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22806499
Sorry, I didn't express myself clearly enough. The code I've posted is MySQL code. I need to do the same in Oracle. I know how to create the procedure in Oracle but what I'd really like to know is how to execute the procedure so it returns a dataset containing just 1 record (newly created ID - basically any variable which is used within the procedure).

Anybody?
0
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 
LVL 48

Expert Comment

by:schwertner
ID: 22807087
You have to know how this ID is produced.
I most cases Oracle uses sequences (integer number generators, created
ad hoc by the programmers).

Look at this:

CREATE PROCEDURE insert_record     ( id OUT INT,       p_value VARCHAR(150))
BEGIN
      id := sequence_name.nextval;        INSERT INTO data (id, value)
                   VALUES (id, p_value);
END;
/


id := sequence_name.nextval; retrieves the next number from the sequence.
Additionally it is stored in the OUT parameter what possibly is a error.
So I will fix the code:


CREATE PROCEDURE insert_record     ( id OUT INT,       p_value VARCHAR(150))
   v_id   INTEGER;
BEGIN
      v_id := sequence_name.nextval;        
     INSERT INTO data (id, value)
                   VALUES (id, p_value);
     id := v_id;
END;
/

Now you have to call the procedure and to get the out value.
Of course you have to provide the programatical tool
to produce the ids (I suggest Oracle sequence).
Look in the net and find example how to create it.

0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22807878
Please disregard the code I've provided in previous posts. I'm just looking for a way how to export OUTPUT variable of the procedure to a dataset. I know how sequences and all this staff work.

So I'm looking for a way how to do equivalent of this code (MySQL) in Oracle:

set @id=0; call proc(@id); select @id;

where proc is a stored procedure which has just 1 output parameter. What proc does doesn't have anything to do with my problem.

0
 
LVL 48

Expert Comment

by:schwertner
ID: 22813488
Use the common way.

If you decide to use PL/SQL (there are also C, Java, VB interfaces), then
using anonymous block:

DECLARE
      v_id     NUMBER;
      v_value := VARCHAR2(4000);
BEGIN
      v_valie := 'Some value';
      insert_record  ( v_id,       p_value)
      dbms_output.put_line (v_id);
END;
/
     
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22822920
This one returns null when executed using ExecuteScalar() (doesn't return any record when executed by ExecuteReader()).
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22829937
CREATE PROCEDURE insert_record     ( id OUT INT,       p_value VARCHAR(150))
   v_id   INTEGER;
BEGIN
      v_id := sequence_name.nextval;        
     INSERT INTO data (id, value)
                   VALUES (v_id, p_value);
     id := v_id;
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line(substr(sqlerrm,1,254));
    id := -111;
END;
/


First check it with PL/SQL:

set serveroutput on

DECLARE
      v_id     NUMBER;
      v_value := VARCHAR2(4000);
BEGIN
      v_valie := 'Some value';
      insert_record  ( v_id,       p_value)
      dbms_output.put_line (v_id);
END;
/

0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22886412
Unfortunatelly dbms_output.put_line() writes to kind of standard output but not to the output dataset.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22893176
This is only to check if it really returns the value
and if there are exceptions that prevent the
execution.
After you make sure that the value is really returned
by the stored procedure you con move the focus
of your efforts to the interface with the MS stuff.
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22902764
I know that the correct value is returned as output variable. What I'd like to do is return this value as output dataset.

Current workaround which I use is:
- store this value to a package variable
- select this value

This solution is almost OK however I need to execute the SQL command twice. Does anybody know how to do this at once?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22903318
These worarounds will not hel you because package variables are created for every session and
so every session (assuming you are not using it in one session) will create its own copy of the variable.
But possibly I am wrong and you work in one session. In this case it is possible to use associative arrays (former PL/SQL tables or normally speaking an array).
So to use multisession acces to the resul one possible solution is to store the values in a regulat table.
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22917556
In this case I execute both commands within one session (at least I hope that ODP doesn't change the session while pointer to instance of SqlCommand exists and all commands executed while SqlCommand instance exists are sent within one session). So the current problem is how to execute these 2 commands at once. Anybody?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22923658
Take experiment that will show the truth.
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 22929440
OK, will inform. Anyway I'd be very happy if somebody came up with a solution which wouldn't require executing 2 queries.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22942204
Using PL/SQL table that can store unlimited entries and can be returned as record set in Oracle environmet.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 22942219
Using PL/SQL table that can store unlimited entries and can be returned as record set in Oracle environmet.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1500 total points
ID: 22942242
Using PL/SQL table that can store unlimited entries and can be returned as record set in Oracle environmet.
0
 
LVL 4

Author Comment

by:petr_hlucin
ID: 23061136
OK, to close this - there are several solutions to this problem but none of them can be executed using 1 query. I accept schwertner's solution but I've used mine.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question