Calling Oracle Stored Procedure or Function from SmartGWT 5.0p

[Blama]

Hi Isomorphic,

what is the best practice to have Oracle do some task and return a number as result?

Oracle Stored Procedure or Function?
Could you give an example? Here you seem to suggest function but with no example.
In the Quick Start Guide and the CustomQuerying-docs you suggest procedure.

I'd think that function is correct, but can't get it to work (from the logs I can see that the velocity expression is working).

.ds.xml:

		<operationBinding operationType="remove" operationId="removeABC" serverMethod="removeABC" requiresRole="deleteABCOwn, deleteABCSameLE">
			<customSQL>deleteABC($values.ABC_ID)</customSQL>
		</operationBinding>

I'm using v10.0p_2015-08-16/PowerEdition Deployment.

I tried "deleteABC($values.ABC_ID)", "CALL deleteABC($values.ABC_ID)" and "EXEC deleteABC($values.ABC_ID)",

leading to different error messages, including:

• ORA-06576: not a valid function or procedure name
• ORA-00900: invalid SQL statement
• Ungültiger SQL-Typ: sqlKind = UNINITIALIZED

My function is:

CREATE OR REPLACE FUNCTION deleteABC(abcId IN NUMBER) return NUMBER AS
BEGIN
--dummy
return 1000;
END;
/

Do you describe calling functions (returning different types) and stored procedures (with one or more OUT parameters) somewhere in detail?

This usecase should be very common, so most likely I just didn't find the docs for it, yet. On the other hand, there are not too many threads in the forums search.

Best regards
Blama

[Isomorphic]

Sorry, this isn't an area where we have an opinion or should have one.

What works best is up to Oracle and their JDBC driver - the docs already explain the standard Java calls we're making.

[Blama]

Update:

<customSQL>SELECT deleteABC($values.ABC_ID) FROM DUAL</customSQL>

seems to execute as it should. But I can't find my return value neither in the server-side DSResponse nor in the client-side DSResponse.

Is this path the right one and if so, how do I get the returned number?

Best regards
Blama

[Blama]

Hi Isomorphic,

the docs already explain the standard Java calls we're making.

so what do you suggest in order to call a stored [procedure|function]? How to get the return value for PreparedStatement.executeUpdate() you are mentioning in the docs?

Because for me, the suggested approach of

<customSQL>call deleteOrder($criteria.orderNo)</customSQL>

is not working.

Best regards
Blama

[Isomorphic]

You might be looking at old docs? The current docs are very explicit:

When calling stored procedures this way, be sure that the <customSQL> operates like a normal SQL statement, so that it can be called via normal JDBC calls. For operationType="fetch", the JDBC API PreparedStatement.executeQuery() is called and expects a ResultSet returned. For "update", "add" and "remove" operationTypes, PreparedStatement.executeUpdate() is called and expects an integer return value (number of rows affected). If your stored procedure uses in/out parameters, returns something other than a ResultSet or affected row count, or in some other way is incompatible with the standard JDBC methods described above, you may need to add code to adjust the return values to what JDBC expects. You can do this by either putting code into the <customSQL> block directly, or by adding a second stored procedure that transforms the outputs of the first procedure.

Beyond this advice - get Oracle to return data via standard JDBC APIs - we can't help further. We don't provide training or support for Oracle's PL/SQL, it's a Oracle product.

[Blama]

Hi Isomorphic,

we are looking at the same docs.
I'll try the same with stored procedure next in order to make for it is not because of function vs stored procedure. I was wondering how to return the "integer return value" for a stored procedure without using IN/OUT parameters?

For "update", "add" and "remove" operationTypes, PreparedStatement.executeUpdate() is called and expects an integer return value (number of rows affected).

I know that this perhaps is beyond your support, but either your consultants or an other user will almost definitely have solved this problem before.

Best regards
Blama

[Isomorphic]

Yes, other users familiar with Oracle PL/SQL may chime in. However, we would recommend searching and/or posting to a forum dedicated to Oracle with JDBC, since this kind of question might be common enough to be covered in a FAQ if you find the right forum.

[Blama]

Update current status:

• I managed to call a Stored Procedure with

  <customSQL>CALL deleteABC2($criteria.ABC_ID)</customSQL>

  without any OUT parameters. Procedures with OUT parameters give me errors (like the docs say).
  
• I managed to call a Stored Function with

  <customSQL>SELECT deleteABC($values.ABC_ID) FROM DUAL</customSQL>

  but could not access the returned value.
  Tricks with name="retval" customSQL="true"-fields and AS retval in the SQL did not work either.

I think that the function-way should work as well, but it is OK for me now the way it is because I can work around this by using a stored procedure and having it store its results in the database.

@Isomorphic: Searching Oracle JDBC related pages did help as I can't add java code (e.g. to register OUT-parameters). With stored functions it should just work IMHO as my function returns a number value, which is what is expected for the signature of executeUpdate().

So if anyone knows of a way to get the return value of a function call, please let me know.

Best regards
Blama

[john_austria]

Hi Blama,

try
SELECT deleteABC($values.ABC_ID) as retval FROM DUAL
then you should get a recordset when called with datasource.fetch() e.g.

Retrieve the returned value with getAttributeAsInt('retval') from the first (and only) record returned.

When using Oracle table functions call
SELECT * FROM TABLE (deleteABC($values.ABC_ID)).

In this case a recordset is returned, possibly containing more than one record.
But: Creating Oracle table functions is not as simple as creating microsoft table functions, but that's another story.

John

[Blama]

Hi John,

thank you for the advice. I'll try it. It is possible that I did not test operationType=fetch+function so far.

Best regards
Blama