60477_en-US

liliconcom

launch a PL/SQL (stored in Oracle database) with returning cursor

Hi all,

I would like to launch a PL/SQL (stored in Oracle database) procedure into JMP 14

Through menu File -> Database -> OpenTable and in Advanced button, I select the procedure and give all parameters in the SQL window but when I clic on Execute SQL button, it fails.

Below the SQL construct automaticaly by JMP
CALL "domain"."my_oracle_pro" output()


Thanks for your help

Re: launch a PL/SQL (stored in Oracle database) with returning cursor

Try running it in JSL.  JMP can not handle OUT parameters from PL/SQL btw.  Here's a simple example showing how to do this in JSL; you'll need to supply some of the parameters as indicated.

// Create an ODBC connection to your Oracle database.  Replace <TNS_NAME> with your TNS entry for the
// database in question, <USERNAME> and <PASSWORD> with the proper credentials for your database
// Also make sure the driver has the correct name.
dbc = create database connection("Driver={Oracle in OraClient11g64_home1};Dbq=<TNS_NAME>;UID=<USERNAME>;PWD=<PASSWORD>;");

// Put values in for parameters to PL/SQL procedure call
param1 = "foo";
param2 = "bar";
sql_statement = evalinsert("begin myschema.myprocedure('^param1^', '^param2^')");
show(sql_statement);

execute sql(sql_statement);

close database connection(dbc);
</PASSWORD></USERNAME></TNS_NAME></PASSWORD></USERNAME></TNS_NAME>