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: 1224
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 SAN Developer

Image
User avatar
mboegem
 
Posts: 1441
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

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: 1224
Joined: Wed Apr 23, 2003 11:27 pm
Location: Thousand Oaks, CA USA


Return to Plugins and Beans

Who is online

Users browsing this forum: No registered users and 6 guests