Execute Oracle Stored Proc - No Args

Classic Servoy
1

Hey folks,

I’m trying to execute an Oracle stored procedure that doesn’t have any arguments - and doesn’t return any values (it just updates a materialized view)… and I can’t get it to work.

Code:

dsResult = plugins.rawSQL.executeStoredProcedure(serverName, "{CALL REFRESH_MV_HOURLY()}", [], [], 10);

Return:

com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'REFRESH_MV_HOURLY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 ORA-06550: line 1, column 7:
PLS-00201: identifier 'REFRESH_MV_HOURLY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

When I add the ?= before the name of the stored procedure:

dsResult = plugins.rawSQL.executeStoredProcedure(serverName, "{?=CALL REFRESH_MV_HOURLY()}", [], [], 10);

I get this error:

"com.servoy.j2db.persistence.RepositoryException: com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Missing IN or OUT parameter at index:: 1"

Has anyone got something similar to work?

Thanks!

2

Hi Bob,

For a project I have to use an Oracle DB.
The stored procedure I have to use doesn’t have arguments either, but instead of passing 2 empty arrays, I’m passing ‘null’.
So:

dsResult = plugins.rawSQL.executeStoredProcedure(serverName, "{CALL REFRESH_MV_HOURLY()}", null, null, 10);

I doubt if this makes any difference, but worth a try.
Another thing is that I don’t expect any result in my situation. It’s just a procedure to get something done on DB side, which Servoy isn’t involved in.

Are you sure the stored procedure itself does return values? If not it is impossible for Servoy to retrieve any values. (Guess you figured that one out already ;-) )

3

Hey Marc,

Thanks! That made it work!

Also - once I double-checked the name of the stored proc… (the DBA renamed it to “REFRESH_MVS_HOURLY” from “REFRESH_MV_HOURLY”) - all is good!

You sir, are a gentleman and a scholar! :)

4

UPDATE: When there is an error - we want to receive the error back.

The Procedure declares an OUT variable:

create or replace PROCEDURE  "REFRESH_MVS_HOURLY" 
(
 
    p_error OUT VARCHAR2) 
    AS
    
BEGIN

   p_error := NULL;


   BEGIN
 dbms_mview.REFRESH( LIST => 'MV_FORMSAPP_STAFF1', METHOD => 'C' );
 
       EXCEPTION
            WHEN OTHERS THEN
                 p_error := 'Refresh procedure error occurred. ' || SUBSTR(SQLERRM, 1, 200);
    END;

End Refresh_MVS_HOURLY ;

The calling code is this:

ds = plugins.rawSQL.executeStoredProcedure(sServerName, "{? = CALL CRMSADMIN.REFRESH_MVS_HOURLY()}", [java.sql.Types.VARCHAR], [1], 1);

So, I think it’s all OK - but the returning dataset always has no rows (even when there is an error thrown when running in Oracle Developer)

Any thoughts??

5

If you’re in a recent version of Servoy (8.3+), they have stored procedures integrated in a better way.

On your DB Server connection settings area, check the box “Enable Procedures”. That will make server read the stored procedures from that database.
Then in code, it will give you code completion showing you the procedures available under the datasources.sp scope.

Example:

datasources.sp.servername.mystoredproc();

It returns a dataset, so you can also then create a datasource from it dynamically if you wish to display data from a procedure in Servoy. They did a video on it here: https://www.youtube.com/watch?v=s4JBfONSO_k

6

WOW! That’s awesome - and a LOT easier!

However, when I call it I get this ORA error:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'PROCEDURE_TEST'

SO, I created a new, test procedure - that only returned a string:

create or replace PROCEDURE PROCEDURE_TEST

( p_message OUT VARCHAR2) 
    AS
    
BEGIN

p_message := NULL;
   BEGIN
p_message := 'This is a procedure test.';
    END;
    
END PROCEDURE_TEST;

And I still get the same error… what am I missing?

THANK YOU VERY MUCH for your help, Scott!!!

7

Just FYI - I tried it with the RAWSQL plugin as well:

plugins.rawSQL.executeStoredProcedure(sServerName, "{?=call procedure_test()}", [java.sql.Types.VARCHAR], [1], 1);

And got this error:

ERROR com.servoy.j2db.util.Debug - ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'PROCEDURE_TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
8

i don’t have an Oracle DB handy to test that, but maybe its a driver version issue or minor syntax on the procedure? The test one should work, in theory. So I would verify its using the right driver, and if so, try to see what SQL Servoy is actually using. I don’t remember if those show up in the Performance Data page on Servoy Admin, but check there first. If not, ask the Oracle DBA to tell you what ran and failed. Then find out what the proper syntax is to call it, and see if you can do something different with the raw sql plugin to get it to go through, or submit a case to Servoy with the problem details.

9

Hey Scott,

Thanks!

Before posting here - I’d already tried to view the SQL in the Performance Data page - no luck. I’d also confirmed the driver version.

SO - on to the DBA! :D

Thanks for your insights and help!

Bob

10

No error thrown in the Oracle log.

Looks like a Servoy bug.

Will file a case.

FILED: SVY-15062

11

The stored procedure call syntax for the oracle procedure is stlightly different:

var ds = plugins.rawSQL.executeStoredProcedure(sServerName, "{call procedure_test(?)}", [java.sql.Types.VARCHAR], [1], -1);

Rob