IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.
Course Of The Month
3 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
Active 6 days ago
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);
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?
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
Active 6 days ago
this should do:
string sql = "select WEBUSER.F_UpdateParticipant(@0) FROM DUAL";
string res = _db.db.ExecuteScalar<string>(sql, json);
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.
Active 6 days ago
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
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
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;
}
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
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Using C# How can I sample the current mouse position in a window | 16 | 63 | |
| Difference between Leaflet and MapBox? | 5 | 47 | |
| PowerShell: foreach where object notmatch? | 17 | 83 | |
| Need Help With GDI+ in VB.Net | 8 | 44 |
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month3 days, 2 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.