Execute Oracle Stored Proc - No Args

Questions and answers on developing, deploying and using plugins and JavaBeans

Execute Oracle Stored Proc - No Args

Postby bcusick » Fri Aug 31, 2018 11:27 pm

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:
Code: Select all
dsResult = plugins.rawSQL.executeStoredProcedure(serverName, "{CALL REFRESH_MV_HOURLY()}", [], [], 10);


Return:
Code: Select all
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:
Code: Select all
dsResult = plugins.rawSQL.executeStoredProcedure(serverName, "{?=CALL REFRESH_MV_HOURLY()}", [], [], 10);


I get this error:
Code: Select all
"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!
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby mboegem » Mon Sep 03, 2018 12:54 am

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:
Code: Select all
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 ;-) )
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Tue Sep 04, 2018 3:24 pm

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! :)
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Tue Jun 02, 2020 9:04 pm

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

The Procedure declares an OUT variable:

Code: Select all
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:

Code: Select all
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??
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby sbutler » Wed Jun 03, 2020 5:24 pm

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:
Code: Select all
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
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Wed Jun 03, 2020 9:14 pm

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

However, when I call it I get this ORA error:
Code: Select all
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:
Code: Select all
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!!!
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Wed Jun 03, 2020 9:24 pm

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

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


And got this error:

Code: Select all
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
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby sbutler » Fri Jun 05, 2020 5:05 pm

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.
Scott Butler
iTech Professionals, Inc.
SAN Partner

Servoy Consulting & Development
Servoy University- Training Videos
Servoy Components- Plugins, Beans, and Web Components
Servoy Guy- Tips & Resources
ServoyForge- Open Source Components
User avatar
sbutler
Servoy Expert
 
Posts: 759
Joined: Sun Jan 08, 2006 7:15 am
Location: Cincinnati, OH

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Fri Jun 05, 2020 7:23 pm

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
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby bcusick » Fri Jun 05, 2020 8:38 pm

No error thrown in the Oracle log.

Looks like a Servoy bug.

Will file a case.

FILED: SVY-15062
https://support.servoy.com/browse/SVY-15062
Bob Cusick
bcusick
 
Posts: 1255
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA

Re: Execute Oracle Stored Proc - No Args

Postby rgansevles » Fri Jun 26, 2020 10:35 am

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

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


Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Plugins and Beans

Who is online

Users browsing this forum: No registered users and 17 guests