how to call a stored procedure within a stored procedure ?

how can I call a stored procedure within a stored procedure. sp_non_data2 has same parameters as sp_non_data1. I need to call sp_non_data2 to from sp_non_data1 if v_user_type = 1 else just execute sp_non_data1
PROCEDURE sp_non_data1 (
      p_std_id            IN       VARCHAR2,
      p_TEST   OUT      t_TEST
   )
   IS
      v_user_type   PLS_INTEGER;
   BEGIN
      v_user_type := f_get_user_type (p_std_id);
 
      -- implies an SCM user 
     IF v_user_type = 1 THEN
         p_selected_report := sp_non_data2(p_std_id);
      END IF;
 
      OPEN p_selected_reports FOR
		select *from students;
 
   END sp_non_data1;

Open in new window

tech_questionAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Is this what you are looking for?

The return statement after the call to sp_non_data2 will exit the procedure.
PROCEDURE sp_non_data1 (
      p_std_id            IN       VARCHAR2,
      p_TEST   OUT      t_TEST
   )
   IS
      v_user_type   PLS_INTEGER;
   BEGIN
      v_user_type := f_get_user_type (p_std_id);
 
      -- implies an SCM user 
     IF v_user_type = 1 THEN
         p_selected_report := sp_non_data2(p_std_id);
         return;
      END IF;
 
      OPEN p_selected_reports FOR
		select *from students;
 
   END sp_non_data1;

Open in new window

0
tech_questionAuthor Commented:
p_selected_reports:= sp_non_data2(p_std_id); (gives me an error at this line)
0
johnsoneSenior Oracle DBACommented:
Is sp_non_data2 a function or a procedure?  Based on your post it appears to be a function.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

tech_questionAuthor Commented:
it is a procedure.
0
johnsoneSenior Oracle DBACommented:

PROCEDURE sp_non_data1 (
      p_std_id            IN       VARCHAR2,
      p_selected_reports   OUT      t_TEST
   )
   IS
      v_user_type   PLS_INTEGER;
   BEGIN
      v_user_type := f_get_user_type (p_std_id);
 
      -- implies an SCM user 
     IF v_user_type = 1 THEN
         sp_non_data2(p_std_id, p_selected_reports);
      END IF;
 
      OPEN p_selected_reports FOR
		select *from students;
 
   END sp_non_data1;

Open in new window

0
tech_questionAuthor Commented:
the stored procedure does not return data when it goes to this procedure. Do I need to catch the returned cursor from sp_non_data2. sp_non_data2 has the same signature as sp_non_data1
     IF v_user_type = 1 THEN
         sp_non_data2(p_std_id, p_selected_reports);
      END IF;

0
Jinesh KamdarCommented:
>> the stored procedure does not return data when it goes to this procedure

What is it exactly that u intend to do after calling the SP2? Does that SP2 also return an OUT CURSOR?
0
tech_questionAuthor Commented:
yes it also returns an OUT CURSOR (it has the same signature as sp_non_data1). after it executes sp_non_data2 which also has an output cursor. It should return the output from that stored procedure and return (terminate). If the condition is not met (v_user_type is not 1) then it should continue executing sp_non_data1 and then terminate.
IF v_user_type = 1 THEN
         sp_non_data2(p_std_id, p_selected_reports);
      END IF;
0
SujithData ArchitectCommented:
If both procedures have the same signature, use this code. Make sure that the refcursor is declared as an OUT parameter in both procedures.
PROCEDURE sp_non_data1 (
      p_std_id            IN       VARCHAR2,
      p_TEST   OUT      t_TEST
   )
   IS
      v_user_type   PLS_INTEGER;
   BEGIN
     v_user_type := f_get_user_type (p_std_id);
 
      -- implies an SCM user 
    IF v_user_type = 1 THEN
       sp_non_data2(p_std_id, p_TEST);
    ELSE    
      OPEN p_TEST FOR
	   select *from students;
     END IF;
 
END sp_non_data1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.