Running a stored procedure from within framework

[Roland Tschoeke]

Hi,

We presently have a need to run an Oracle Stored Procedure, and we would like to create a batch process that would run on our batch node during the off-peak hours.  How would I go about invoking a stored procedure from a sub-classed SysBatchJob.

I played with the NexJ supplied “sql-execute” function, but that appears to be limited to “prepared statements”.  I need to invoke an “execute immediate” command to run the stored procedure.

i.e.  EXECUTE IMMEDIATE ‘CALL NEXJSVDB.LoadTier(”All”, 1000)’;

Or, is there a way to invoke a command that will run a PL/SQL block?
i.e.
SQL> DECLARE
2  BEGIN
3        EXECUTE IMMEDIATE ‘CALL NEXJSVDB.LoadTier(”All”, 1000)’;
4  END;
5  /

I also noticed that the documentation for “sql-execute” states:
(For internal use only. Will be deprecated in the near future.)

If this is being deprecated, what will replace the ability to run sql against data that is not in the Model?

Thanks,

Roland
Apex Dev Lead

0

[Evan Wee]

Hi Roland,

(sql-execute) does support executing stored procedure. We have implemented this pattern a number of times for clients needing to fully rebuild their ACL tables.

Given that sql-execute is being used by a large number of our clients, there are no plans to deprecate the function in the near future.

Snippet:
(sql-execute
(((invocation-context)’metadata)’getDataSource “DefaultRelationalDatabase”)
“EXEC rebuildEntityACL”
)

Cheers,
Jon

0

[Author]

Posts