Home Forums Administration Running a stored procedure from within framework

This topic contains 1 reply, has 2 voices, and was last updated by Jonathan Chan Jonathan Chan 1 year, 6 months ago.

  • Author
    Posts
  • #8893
    Roland Tschoeke
    Roland 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
    Jonathan Chan
    Jonathan Chan
    Participant

    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

You must be logged in to reply to this topic.