?
Solved

how to call a stored procedure within a stored procedure ?

Posted on 2008-01-31
9
Medium Priority
?
2,041 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:tech_question
9 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 20791165
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
 

Author Comment

by:tech_question
ID: 20791238
p_selected_reports:= sp_non_data2(p_std_id); (gives me an error at this line)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20791295
Is sp_non_data2 a function or a procedure?  Based on your post it appears to be a function.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:tech_question
ID: 20791388
it is a procedure.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 20791579

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
 

Author Comment

by:tech_question
ID: 20791840
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20792390
>> 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
 

Author Comment

by:tech_question
ID: 20793785
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 20794595
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month7 days, 2 hours left to enroll

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question