Hi João,

I developed a sample extension that exemplifies what you need.

In Oracle, the following sample stored procedure was created:

  create or replace PROCEDURE ECHOPROCEDURE 

  (

    paramIN IN varchar2,

    paramOUT OUT varchar2

  )

  AS

  BEGIN

   paramOUT := paramIN;

  END ECHOPROCEDURE;

As you can see, this stored procedure has two parameters: one input and one output. The output parameter echoes back the input parameter.

I gave execute privileges to the OutSystems platform runtime user, and developed an extension with a single action - ExecuteEchoProcedure - with one input parameter and one output parameter, like the stored procedure. The Oracle stored procedure is invoked in the extension code as follows:

	public void MssExecuteEchoProcedure(string ssparamIN, out string ssparamOUT) {                 

	    IDbTransaction tran = DBTransactionManager.Current.GetTransaction();

	

	    try {

	        // create call

	        IDbCommand cmd = DBHelper.CreateCommand(tran,

	                                        "call ECHOPROCEDURE(@paramIN, @paramOUT)");

	        // set command parameters

	        DBHelper.AddParameter(cmd, "@paramIN", DbType.String, ssparamIN);                

	        IDbDataParameter outParam = DBHelper.AddParameter(cmd, "@paramOUT", DbType.String);                

	        outParam.Direction = ParameterDirection.Output;

	        outParam.Size = 256; 

	

	        // execute command

	        DBHelper.ExecuteScalar(cmd);

	

	        // retrieve output

	        ssparamOUT = (String)outParam.Value;

	    } finally {

	        DBTransactionManager.Current.ReleaseTransaction(tran);

	    }

	} // MssExecuteEchoProcedure        

I tested it with the above stored procedure in the oracle database, and it worked as expected.
Hope this helps.

Best Regards,
Ricardo Ferreira

Thank you for your help.

Where can we find the API specification for the DBHelper and DBTransactionManager classes?

No API specification is available for DBHelper and DBTransactionManager classes. These classes are internal to the platform, and are prone to change in future versions.

Alternatively, you can try to solve your problem using an Advanced Query: the idea is to create another stored procedure in Oracle that executes your procedure and returns the output variable via a SELECT statement. Afterwards, you just need to call the stored procedure in the Advanced Query using the "call" statement and match the output structure to retrieve the output variable value.

Best Regards,
Ricardo Ferreira

I also tried that solution and believe this can't be done in Oracle (although it works in SQL Server).

In Oracle you cannot return results from anonynous PL/SQL blocks (anonymous blocks are procedures, they allways return void).

I believe that, in Oracle, without using the platform internal classes, there is no way to do this and still allow the developer to use the procedure within the platform running transaction.

Hi Ricardo,

can you explain how to do it to call a function?

Suppose we have a simple function that return a NUMBER, without any parameter.

CREATE OR REPLACE FUNCTION RETVALUE
RETURN NUMBER IS BEGIN
RETURN 3;
END RETVALUE;

How we build the IDbCommand and IDbDataParameter to recieve the value?

Thanks.

Ivan Soares

Hi Ivan,

You can retrieve the output value of a function by executing an SQL Query.
For the function you defined, it will be something like:

SELECT RETVALUE()
FROM DUAL

Which will output this result:

RETVALUE()
----------------------
3

To use this in the OutSystems Platform, you just need to use an Advanced Query node.

Best Regards,
Ricardo Ferreira

Hi Ricardo,

an Advanced Query works for this example but for a procedure with out parameters and more complex does not work, as mentioned above (Re : Stored procedure out parameters 2008-02-01 13:07:58).

What I really want to know is how to do it to call a function from c# .net and retrieve the value.
Can you explain?

Thanks in advance.

Ivan Soares

Hello Ivan,

To call an oracle function from an extension, you just have to execute a reader for "select RETVALUE from dual", read the only record and obtain the result value. The .NET code of the extension is shown below, which you can found in the attached extension.

public void MssExecuteFunction(out int ssparamOUT) {

	IDbTransaction tran = DBTransactionManager.Current.GetTransaction();

	IDataReader reader = null;


	try {

		// create call

		IDbCommand cmd = DBHelper.CreateCommand(tran, "select RETVALUE from dual");

	   

		// execute command                

		reader = DBHelper.ExecuteReader(cmd);

		// read the only record returned

		reader.Read();

		// retrieve the first column result

		ssparamOUT = reader.GetInt32(0);

	  

	} finally {

		if (reader != null)

			reader.Close();

		DBTransactionManager.Current.ReleaseTransaction(tran);

	}

} // MssExecuteFunction

Best Regards,
Ricardo Ferreira

Hi Ricardo,

thanks for your help.
If you don’t mind, how we do for a function like this:

CREATE OR REPLACE FUNCTION RETVALUE
( paramIN IN varchar2, paramOUT OUT varchar2)
RETURN NUMBER IS
BEGIN
paramOUT := paramIN;
RETURN 3;
END RETVALUE;

With your solution we can’t catch the output parameter and the return value too.
My problem is how to define the input, output and return DBHelper parameters and read them.

Thanks in advance.
Ivan Soares

Hi Ivan,

In that case you will need to merge both examples:

First of all your function has two parameters, one input, and another output.
For calling the function you will need to pass both parameters, so the SQL for the command should be:

IDbCommand cmd = DBHelper.CreateCommand(tran, "select RETVALUE(@paramIN, @paramOUT) from dual");

After that you will need to define on c# the command's parameters, @paramIN and @paramOUT:

DBHelper.AddParameter(cmd, "@paramIN", DbType.String, ssparamIN);                

IDbDataParameter outParam = DBHelper.AddParameter(cmd, "@paramOUT", DbType.String);

outParam.Direction = ParameterDirection.Output;

outParam.Size = 256;

And now, just execute it. By calling DBHelper.ExecuteReader(cmd) your function will be executed and you will be able to retrieve its return value and output parameter value as well.

// execute command                

reader = DBHelper.ExecuteReader(cmd);

// read the only record returned
reader.Read();

// retrieve the first column result

int functionReturn = reader.GetInt32(0);

You just got the function return to the local variable "functionReturn" which you can assign to its corresponding extension output parameter.

To retrieve the value of the output parameter, you will just have to access to the "Value" property of IDbDataParameter. For obtaining the output value of @paramOut:

string outParamReturn = (String)outParam.Value;

You just got the output parameter value to the local variable "outParamReturn " which you can assign to its corresponding extension output parameter.

Hope this helps

Best Regards,
Ricardo Ferreira

Hi Ricardo,

I'm getting this error:

ORA-06572: Function string has out arguments

Cause: A SQL statement references either a packaged, or a stand-alone, PL/SQL function that contains an OUT parameter in its argument list. PL/SQL functions referenced by SQL statements must not contain the OUT parameter.

Is there any way to call RETVALUE but not inside of a SQL statement, just like you do to call the procedure ECHOPROCEDURE?

Something like:

IDbCommand cmd = DBHelper.CreateCommand(tran,"call RETVALUE(@paramIN, @paramOUT)");

DBHelper.AddParameter(cmd, "@paramIN", DbType.String, ssparamIN);
IDbDataParameter outParam = DBHelper.AddParameter(cmd, "@paramOUT", DbType.String);
outParam.Direction = ParameterDirection.Output;
outParam.Size = 256; …
…

How we define the return parameter?

Thank you.

Ivan Soares

Apparently Oracle doesn't like calling functions with output parameters in SQL Statements...

From my quick research (google), I didn't found an alternative way of explicitly invoking a function, similar to the call statement for stored procedures.

From my point of view, the best for you is really to call a stored procedure. You can either convert the function into a procedure, with an additional output parameter to represent the function result, or you can create a wrapper procedure that calls the function inline and outputs its return by an output parameter.

Check the example in the following link on how to call a function in another function. I believe you can do the same to call a function inside your procedure, assigning it to an output variable.
https://www.thescripts.com/forum/thread637406.html

Hope this helps