Home Forums Scheme Running a stored procedure from within framework

Viewing 1 reply thread
  • Author
    Posts
    • #8893
      Roland TschoekeRoland Tschoeke
      Participant

      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
    • #8968
      Evan WeeEvan Wee
      Moderator

      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
Viewing 1 reply thread
  • You must be logged in to reply to this topic.