Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Solved
Petapoco - Getting return from Oracle function
Posted on 2016-08-30
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:
This function returns:
I don't really want to go back to the ADO way of doing these types of queries.
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;
This function returns:
{"Success":true}
or
{"Success":false}
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();
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();
I don't really want to go back to the ADO way of doing these types of queries.
[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
-
8
4
12 Comments
I think you can make your life much simpler ...
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 ...
select WEBUSER.F_UpdateParticipant(@1) from dual;
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);
Active today
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?
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?
Active today
Ok, I've been able to get it to successfully update the record, however, I cannot get the return value at all.
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
string sql = "DECLARE res VARCHAR2(30); BEGIN return WEBUSER.F_UpdateParticipant(@0); END;";
object res = _db.db.ExecuteScalar<string>(sql, json);
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
this should do:
string sql = "select WEBUSER.F_UpdateParticipant(@0) FROM DUAL";
string res = _db.db.ExecuteScalar<string>(sql, json);
Active today
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.
sure you can, using this inside the procedure
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm
Active today
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
Active today
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;
}
Active today
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.
Well, I also redid the output parameter creation, too.
Featured Post
Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax:
(CODE)
This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month5 days, 20 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.