Forum Discussion

jcornelius's avatar
jcornelius
New Contributor III
5 years ago

Snap for JDBC that runs stored procedure and returns status and results...like SQL execute snap

I need a snap like the SQL execute snap that returns both the results and the status but is for JDBC stored procedure…does one exist
pipeline
mbowen's avatar
mbowen
Employee
5 years ago

Hi John:

For which database? You can do quite a bit in the SQL Execute snap – SELECT, CREATE, even invoke a stored function (well, at least for Oracle). Oracle has the notion of IN (parameters passed in) and OUT parameters (values returned from the procedure), and you can even define a stored function which returns a value and also a result in an OUT parameter – don’t know that this is a common pattern though.

function f1(x_out OUT varchar2) return number AS
BEGIN 
    x_out := 'OK';
    RETURN 1; 
END f1;

We do support stored procedures for most of our databases, but to what extent depends on the db.

nickshore's avatar
nickshore
New Contributor II
5 years ago

Hi,

Just jumping on this thread as we have a requirement to execute an Oracle stored procedure that has a IN/OUT parameter set which I know isn’t support via the Stored Procedure snap. Are you saying this should be achievable through the Oracle Execute snap?

Thanks

Nick

  • mbowen's avatar
    mbowen
    Employee
    5 years ago

    Hi Nick:

    Jumping in on thread is perfectly fine. The Oracle Execute snap can’t invoke stored procedures. Can you tell me more about your requirement and how the Stored Procedure snap can’t handle it ?

    • Anendu's avatar
      Anendu
      New Contributor II
      4 years ago

      Hello Mbowen,

      I am currently running Stored Procedure against SQL database using JDBC execute SNAP and its returning me on only the execution status .

      DB Version :- SQL server 2017

      Can you please help, How to run stored procedure in JDBC SNAP?

      Thanks,
      Anendu

      • mbowen's avatar
        mbowen
        Employee
        4 years ago

        Hi @Anendu

        Our Generic JDBC Execute doesn’t wrap a CallableStatement which is the typical object used to invoke a stored procedures. It allows registering out parameters, etc. However, we may be able to invoke a stored procedure if it has no output parameters and returns no result set – pretty restrictive!

        Your questions suggest that you do want to get results back. Please clarify ? As mentioned above, you can invoke a stored function in a SELECT. Nick shows how to invoke a stored procedure in an anonymous PL/SQL block to load a JSON blob, but that’s Oracle – nevertheless, all success stories helpful.

        So, tell me a bit more about your stored procedure. BTW, we do have SQL Server snap pack which has a Stored Procedure snap which has very good support for stored procedures.