Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11456
  • Last Modified:

SQL*Plus Syntax for calling Stored Procedure (some have IN Parameters , some don't)

Thru the SQL*Plus client's console  I am able to execute  the Open SQL below:
   select * from user_objects where object_type = "PACKAGE";

I can also execute the JLOs_Proc1 and JLOs_Proc1 Stored Procs below (from a VC++ client application)

?
How do I call the JLOs_Proc1 and JLOs_Proc1 Stored Procs from the SQL*Plus  prompt
?

create or replace package JLOs_pkg is
type genericCursor is ref cursor;
end JLOs_pkg;
/
create or replace procedure JLOs_Proc1 ( sp_cursor OUT JLOs_pkg.genericCursor )
is
begin
open sp_cursor  for select * from user_objects;
end;
/
create or replace procedure JLOs_Proc2 ( prm_1 IN varchar2, sp_cursor OUT JLOs_pkg.genericCursor )
is
begin
open sp_cursor  for select * from user_objects where object_type = prm_1 order by created;
end;
/
create or replace procedure JLOs_Proc3 ( prm_1 IN varchar2, prm_2 IN number, sp_cursor OUT JLOs_pkg.genericCursor )
is
begin
open sp_cursor  for select * from user_objects where object_type = prm_1 and object_id = prm_2;
end;
0
jjlloorrttiizz
Asked:
jjlloorrttiizz
2 Solutions
 
seazodiacCommented:
in sqlplus window, you call stored procedure in one of two ways;


BEGIN
<procedure_name with params>;
END;
/

or

execute <procedure_name with params>;

0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, seazodiak gave you the syntax for executing PL\SQL procedures from SQL*Plus.  To execute functions, the syntax is slightly different (you just list them in a select statement like you would list a column name), but I am not sure that SQL*Plus supports executing stored procedures that return ref cursors.  In SQL*Plus, I would normally just execute the select statement directly, and not try to add the PL\SQL layer in between.
0
 
BobMcCommented:
I run most of my reports this way, so I get the flexibilty of PL/SQL to generate the data sets, and the (often under-rated) built in report formatting capabilities of SqlPlus


From SqlPlus:

VARIABLE bob REFCURSOR;

EXECUTE JLOs_Proc1 (:bob);

PRINT :bob;


HTH
Bob
0
 
earth man2Commented:
SQL> create or replace procedure JLOs_Proc3 ( prm_1 IN varchar2, prm_2 IN number, sp_cursor OUT sys_refcursor )
is
begin
open sp_cursor  for select * from user_objects where object_type = prm_1 and object_id = prm_2;
end;
/
SQL> variable cur refcursor;
SQL> execute JLOs_Proc3('JAVA SOURCE',38877, :cur );
SQL> print cur;

dmf_fnd_ww_dirList
                                    38877                JAVA SOURCE
16-JUL-04 17-JUL-04 2004-07-17:00:25:54 VALID   N N N
0
 
jjlloorrttiizzAuthor Commented:
Thanks Bob, for the sample code for the Proc that takes no In parms (It worked the first time)

Thanks earthman2, for the sample code for the Proc that takes In parms (It worked the first time, after I plugged in values for my environment)

- This is my first - I hope I followed correct EE Protocol, regarding splitting points and accepting the first correct comment/answer...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now