Stored Procedure for select

Hi  All,

I have three different procedures and called the four individual procedures at a time at application level.But client does not want the mention the method we have implemented .he want all four procedures to be called at a time in a single call.

for your reference i am attaching the code to you.

Regards,
GSK
proc.zip
gotetioracleAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
flow01Connect With a Mentor Commented:
?  They are there :
p1_result_list, p2_result_list, p3_result_list, p4_result_list.

I showed the principle for combining the procedures , you can work it out the way you (or better your client) wants
0
 
flow01Commented:
tree or four ?
but you can  combine procedures

create procedure
procedure execute _all(here al arguments of the 4 procedures-  you will have to rename some because their names will appear more then one time)
is
begin
   procedure1 (arguments belonging to it);
   ..
   procedure4 (arguments belonging to it);

end;

you will have to decide if you want to combine arguments (for example p_manifest_id ) ,
especially out arguments ( P_TOTAL_LENGTH , P_ERROR_MESSAGE )
but your ref_cursor arguments cannot be combined

one more question if the 4 calls are in an application : how does a client know if they are in the application or in the database ?
0
 
flow01Commented:
PROCEDURE EXECUTE_ALL(
      p1_rotation_no         IN       gch_manifest.rotation_no%TYPE,
      p1_manifest_id         IN       gch_manifest.manifest_id%TYPE,
      p1_bol_no              IN       gch_manifest.bol_no%TYPE,
      p1_from_created_date   IN       DATE,
      p1_to_created_date     IN       DATE,
      p1_act_type            IN       VARCHAR2,
      p1_result_list         OUT      sys_refcursor,
      p1_total_length        OUT      NUMBER,
      p1_error_message       OUT      VARCHAR2,
                P2_MANIFEST_ID         IN GCH_MANIFEST_CONTAINER.MANIFEST_ID%TYPE,
                P2_RESULT_LIST       OUT SYS_REFCURSOR,
                P2_TOTAL_LENGTH      OUT NUMBER,
                P2_ERROR_MESSAGE     OUT VARCHAR2,
                            P3_MANIFEST_ID       IN GCH_MANIFEST_GOODS.MANIFEST_ID%TYPE,
                            P3_RESULT_LIST       OUT SYS_REFCURSOR,
                            P3_TOTAL_LENGTH      OUT NUMBER,
                            P3_ERROR_MESSAGE     OUT VARCHAR2,
                P4_MANIFEST_GOODS_ID IN GCH_MANIFEST_VEHICLE.MANIFEST_GOODS_ID%TYPE,
                P4_RESULT_LIST       OUT SYS_REFCURSOR,
                P4_TOTAL_LENGTH      OUT NUMBER,
                P4_ERROR_MESSAGE     OUT VARCHAR2
            ) AS
BEGIN
 sel_manifest_list(
       p1_rotation_no        
       p1_manifest_id        
       p1_bol_no            
       p1_from_created_date  
       p1_to_created_date    
       p1_act_type          
       p1_result_list        
       p1_total_length      
       p1_error_message) ;
       
 SEL_CONTAINER_LIST                  
   (                    
       P2_MANIFEST_ID    
       P2_RESULT_LIST    
       P2_TOTAL_LENGTH  
       P2_ERROR_MESSAGE    );
SEL_MANIFEST_GOODS_LIST(                  
                  P3_MANIFEST_ID    
                  P3_RESULT_LIST    
                  P3_TOTAL_LENGTH  
                  P3_ERROR_MESSAGE);        
SEL_VEHICLE_LIST                            
  (                        
      P4_MANIFEST_GOODS_ID
      P4_RESULT_LIST      
      P4_TOTAL_LENGTH      
      P4_ERROR_MESSAGE );
END;
0
 
gotetioracleAuthor Commented:
flow01,
i need three out parameters as cursors
acording to manifest id
0
All Courses

From novice to tech pro — start learning today.