asked on

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

flow01

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 ?

flow01

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;

gotetioracle

ASKER

flow01,
i need three out parameters as cursors
acording to manifest id

ASKER CERTIFIED SOLUTION

flow01

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial