Solved

Petapoco - Getting return from Oracle function

Posted on 2016-08-30
12
46 Views
Last Modified: 2016-09-13
I'm running into the same problem as outlined in this post:  
http://stackoverflow.com/questions/35113140/executing-oracle-function-and-getting-back-a-return-value  

I can do this in my Toad client successfully:
declare result varchar2(30);
BEGIN 
  result:=WEBUSER.F_UpdateParticipant(json input_goes here);
  dbms_output.put_line(result); 
END;

Open in new window

and get the return value shown in dbms_output.
This function returns:
{"Success":true} 

or 

{"Success":false} 

Open in new window

But I cannot get the output returned to Petapoco. I've also tried using output params like this:
var result = new Oracle.ManagedDataAccess.Client.OracleParameter("result",Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
var sql = "DECLARE result VARCHAR2(30);" + 
          "BEGIN "+
          "    @0:=WEBUSER.F_UpdateParticipant(@1);" +
          "END;";
_db.db.Execute(sql, result, json);
res = result.ToString();

Open in new window

AND
var result = new Oracle.ManagedDataAccess.Client.OracleParameter("result",Oracle.ManagedDataAccess.Client.OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
var sql = "DECLARE result VARCHAR2(30);" + 
          "BEGIN "+
          "    @result:=WEBUSER.F_UpdateParticipant(@1);" +
          "END;";
_db.db.Execute(sql, result, json);
res = result.ToString();

Open in new window

Yes, used both Execute and ExecuteScalar with same results.  
I don't really want to go back to the ADO way of doing these types of queries.
0
Comment
Question by:EddieShipman
  • 8
  • 4
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I think you can make your life much simpler ...
select WEBUSER.F_UpdateParticipant(@1) from dual;

Open in new window



and don't use the parameter stuff, but a plain "ExecuteScalar" method in "db", though I don't know what "_db" and "_db.db" objects are ...

object res = _db.db.ExecuteScalar(sql, json);

Open in new window

0
 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
Guy,
The @0 is the first parameter in the parameters list in the function call, which is json, If I use @1, I get this error: Specified argument was out of the range of valid values.
I have already tried this this way too, but I MUST get the return value from the function call.
Doing it like above (with @0) returns this error: ORA-00911: invalid character

_db.db is the PetaPoco Db connector.

Do you have any other suggestions?
0
 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
Ok, I've been able to get it to successfully update the record, however, I cannot get the return value at all.
string sql = "DECLARE res VARCHAR2(30); BEGIN return WEBUSER.F_UpdateParticipant(@0); END;";
object res  = _db.db.ExecuteScalar<string>(sql, json);

Open in new window


But res is null because I cannot return anything from the "procedure", I get this if I try:
ORA-06550: line 1, column 33:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 1, column 33:
PL/SQL: Statement ignored
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
this should do:
string sql = "select WEBUSER.F_UpdateParticipant(@0) FROM DUAL";
string res  = _db.db.ExecuteScalar<string>(sql, json);

Open in new window

0
 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
Not exactly... Besides an Update, there is also an insert in the function so we get the cannot perform a DML operation inside a query exception.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
I'll have to ask our Oracle developer about this one, for sure...
0
 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
Ok, after adding that to the function, I'm getting this:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "WEBUSER.F_UPDATEPARTICIPANT", line 153

Open in new window

I'm going to talk to the Oracle developer about how to rewrite this function to work correctly as it is, in my opinion, kind of clunky and stupid in the way it was written.
0
 
LVL 26

Accepted Solution

by:
EddieShipman earned 0 total points
Comment Utility
Ok, Guy, I have a solution. I thought I had tried it before from looking at my previous posts. However, when I wrote the SQL, I included the wrapped version and that is what caused the exception, the wrapped sql. If the SQL is written like below, it works, perfectly.

public string UpdateParticipant(ParticipantUpdate Participant)
{
    string ret = "";
    IsoDateTimeConverter dt = new IsoDateTimeConverter();
    dt.DateTimeFormat = "MM-dd-yyyy"; // we must have this format for our dates
    string json = JsonConvert.SerializeObject(Participant, dt);
    // Creating this output parameter is the key to getting the info back.
    var result = new OracleParameter
    {
        ParameterName = "RESULT",
        Direction = System.Data.ParameterDirection.InputOutput,
        Size = 100,
        OracleDbType = OracleDbType.Varchar2
    };
    // Now, setting the SQL like this using the result as the output parameter is what does the job.
    string sql = $@"DECLARE result varchar2(100); BEGIN  @0 := WEBUSER.F_UpdateParticipant(@1); END;";
    var res = _db.db.Execute(sql, result, json);

    // Now return the value of the Output parameter!!

    ret = result.Value.ToString();
    return ret;
}

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
interesting ...
0
 
LVL 26

Author Comment

by:EddieShipman
Comment Utility
Yes, for some reason, I kept getting some stupid error from Oracle about invalid character "". so I rewrote the SQL without the wrapping and viola!!
Well, I also redid the output parameter creation, too.
0
 
LVL 26

Author Closing Comment

by:EddieShipman
Comment Utility
Self-answered
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now