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
Announcement
Collapse
No announcement yet.
X
-
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.
JohnLast edited by john_austria; 20 Aug 2015, 06:53.
Leave a comment:
-
Update current status:
- I managed to call a Stored Procedure with
Code:
<customSQL>CALL deleteABC2($criteria.ABC_ID)</customSQL>
- I managed to call a Stored Function with
Code:
<customSQL>SELECT deleteABC($values.ABC_ID) FROM DUAL</customSQL>
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
BlamaLast edited by Blama; 18 Aug 2015, 01:47.
Leave a comment:
- I managed to call a Stored Procedure with
-
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.
Leave a comment:
-
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?
Originally posted by Isomorphic View PostFor "update", "add" and "remove" operationTypes, PreparedStatement.executeUpdate() is called and expects an integer return value (number of rows affected).
Best regards
Blama
Leave a comment:
-
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.
Leave a comment:
-
Hi Isomorphic,
Originally posted by Isomorphic View Postthe docs already explain the standard Java calls we're making.
Because for me, the suggested approach of
Code:<customSQL>call deleteOrder($criteria.orderNo)</customSQL>
Best regards
Blama
Leave a comment:
-
Update:
Code:<customSQL>SELECT deleteABC($values.ABC_ID) FROM DUAL</customSQL>
Is this path the right one and if so, how do I get the returned number?
Best regards
Blama
Leave a comment:
-
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.
Leave a comment:
-
Calling Oracle Stored Procedure or Function from SmartGWT 5.0p
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:
Code:<operationBinding operationType="remove" operationId="removeABC" serverMethod="removeABC" requiresRole="deleteABCOwn, deleteABCSameLE"> <customSQL>deleteABC($values.ABC_ID)</customSQL> </operationBinding>
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:
Code:CREATE OR REPLACE FUNCTION deleteABC(abcId IN NUMBER) return NUMBER AS BEGIN --dummy return 1000; END; /
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
BlamaTags: None
Leave a comment: