Home » SQL & PL/SQL » SQL & PL/SQL » Wrapper Procedure (Oracle 11g)
| Wrapper Procedure [message #619877] |
Fri, 25 July 2014 12:07  |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
I have 4 procedure having different input variables and output as refcursors.
I need to create a stand alone procedure/package as wrapper to call all the above 4 proc.
I want to know how the individual return value and refcursors can mapped to wrapper's output value and ref cursors.
procedure proc5
(
i_value1 in varchar2,
i_value2 in varchar2,
i_value3 in varchar2,
i_value4 in date,
i_value5 in number,
O_first_cur OUT SYS_REFCURSOR,
O_second_cur OUT SYS_REFCURSOR,
O_third_CUR OUT SYS_REFCURSOR,
O_fourth_CUR OUT SYS_REFCURSOR,
O_fifth_CUR OUT SYS_REFCURSOR,
O_sixth_CUR OUT SYS_REFCURSOR,
O_SQLERR_CODE OUT VARCHAR2,
O_SQLERRMSG OUT VARCHAR2
)
as
if ( i_value2 = 'A' ) then
pkg1.proc1(
i_value1 in varchar2,
i_value2 in varchar2,
O_sixth_CUR OUT SYS_REFCURSOR
);
pkg1.proc2( i_value1 in varchar2,
i_value2 in varchar2,
i_value5 in number,
i_value4 in date,
O_first_cur OUT SYS_REFCURSOR,
O_second_cur OUT SYS_REFCURSOR,
O_third_CUR OUT SYS_REFCURSOR,
O_fourth_CUR OUT SYS_REFCURSOR,
O_fifth_CUR OUT SYS_REFCURSOR
);
end if
end proc5;
[Updated on: Fri, 25 July 2014 12:13] Report message to a moderator |
|
|
|
| Re: Wrapper Procedure [message #619879 is a reply to message #619877] |
Fri, 25 July 2014 12:29  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Quote:I need to create a stand alone procedure/package as wrapper to call all the above 4 proc.
If the four procedures are a part of the same functionality, then wrap them inside a package and call them inside it. The order of execution would be a matter of your choice, I mean, since you are using procedural language, the procedures will be executed serially one after another.
Having said that, if you want to execute the next procedure based on the return value of the previous statement executed, then have an out parameter in that previously called procedure and write your conditions to call subsequent procedures.
Quote:I want to know how the individual return value and refcursors can mapped to wrapper's output value and ref cursors.
Sorry, I don't understand what you mean. Could you please elaborate.
|
|
|
|
| Re: Wrapper Procedure [message #619883 is a reply to message #619879] |
Fri, 25 July 2014 13:26 |
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
|
Senior Member |
|
|
If its a serial execution i would not require wrapper.
Requirement is, i need to give this wrapper procedure to external team they dont have access to our DB.
Through this wrapper they will call required proc dynamically.
Quote:I want to know how the individual return value and refcursors can mapped to wrapper's output value and ref cursors.
I want to map 4 individual output cursors to wrapper output cursors so that calling system will get the result set.
|
|
|
|
|
|
| Re: Wrapper Procedure [message #619949 is a reply to message #619933] |
Sat, 26 July 2014 09:15 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
If your question is about overloading, then consider this example. You will note please that all this code will compile since syntactically it is correct. But also note that the procedure name is then same even though it is used in this case seven different times in the same package. This is an example of overloading.
In order for this to work, at runtime the database must be able to figure out which version of the procedure you are trying to invoke. For this it must refer to the specifications (the definition) of each individual procedure and match that to the invocation you are making. However, this is not always possible as this example shows. Note that in some cases it is not readily apparent which variation of the procedure is being requested and thus Oracle raises an exception.
The point here is that as long as your procedure definitions are sufficiently different such that the database can figure out which version of a procedure your usage wants, it will work.
As always the best way to get your answer may be your own testing. You should set up your package and then test calling it under different situations.
Good luck. Kevin
10:06:34 SQL> create or replace package kev_specification_test is
10:06:35 2
10:06:35 3 procedure px;
10:06:35 4
10:06:35 5 procedure px (i1 in integer);
10:06:35 6
10:06:35 7 procedure px (i1 in integer,o1 out integer);
10:06:35 8
10:06:35 9 procedure px (i1 in integer,i2 in integer);
10:06:35 10
10:06:35 11 procedure px (i1 in integer,o1 out integer,o2 out integer);
10:06:35 12
10:06:35 13 procedure px (i1 in integer,i2 in integer, o1 out integer);
10:06:35 14
10:06:35 15 procedure px (i1 in integer,i2 in integer, o1 out integer,o2 out integer);
10:06:35 16
10:06:35 17 end;
10:06:35 18 /
Package created.
Elapsed: 00:00:00.17
10:06:36 SQL>
10:06:36 SQL> create or replace package body kev_specification_test is
10:06:36 2
10:06:36 3 procedure px is begin null; end;
10:06:36 4
10:06:36 5 procedure px (i1 in integer) is begin null; end;
10:06:36 6
10:06:36 7 procedure px (i1 in integer,o1 out integer) is begin null; end;
10:06:36 8
10:06:36 9 procedure px (i1 in integer,i2 in integer) is begin null; end;
10:06:36 10
10:06:36 11 procedure px (i1 in integer,o1 out integer,o2 out integer) is begin null; end;
10:06:36 12
10:06:36 13 procedure px (i1 in integer,i2 in integer, o1 out integer) is begin null; end;
10:06:36 14
10:06:36 15 procedure px (i1 in integer,i2 in integer, o1 out integer,o2 out integer) is begin null; end;
10:06:36 16
10:06:36 17 end;
10:06:36 18 /
Package body created.
Elapsed: 00:00:00.17
10:06:36 SQL>
10:06:36 SQL>
10:06:36 SQL>
10:06:36 SQL> begin
10:06:36 2 kev_specification_test.px;
10:06:36 3 end;
10:06:36 4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
10:06:36 SQL>
10:06:36 SQL> declare
10:06:36 2 i1_v integer;
10:06:36 3 begin
10:06:36 4 kev_specification_test.px (i1_v);
10:06:36 5 end;
10:06:36 6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
10:06:36 SQL>
10:06:36 SQL> declare
10:06:36 2 i1_v integer;
10:06:36 3 o1_v integer;
10:06:36 4 begin
10:06:36 5 kev_specification_test.px (i1_v,o1_v);
10:06:36 6 end;
10:06:36 7 /
kev_specification_test.px (i1_v,o1_v);
*
ERROR at line 5:
ORA-06550: line 5, column 4:
PLS-00307: too many declarations of 'PX' match this call
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored
Elapsed: 00:00:00.32
10:06:37 SQL>
10:06:37 SQL> declare
10:06:37 2 i1_v integer;
10:06:37 3 o1_v integer;
10:06:37 4 o2_v integer;
10:06:37 5 begin
10:06:37 6 kev_specification_test.px (i1_v,o1_v,o2_v);
10:06:37 7 end;
10:06:37 8 /
kev_specification_test.px (i1_v,o1_v,o2_v);
*
ERROR at line 6:
ORA-06550: line 6, column 4:
PLS-00307: too many declarations of 'PX' match this call
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
Elapsed: 00:00:00.32
10:06:37 SQL>
10:06:37 SQL> declare
10:06:37 2 i1_v integer;
10:06:37 3 i2_v integer;
10:06:37 4 o1_v integer;
10:06:37 5 begin
10:06:37 6 kev_specification_test.px (i1_v,o1_v,o2_v);
10:06:37 7 end;
10:06:37 8 /
kev_specification_test.px (i1_v,o1_v,o2_v);
*
ERROR at line 6:
ORA-06550: line 6, column 41:
PLS-00201: identifier 'O2_V' must be declared
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored
Elapsed: 00:00:00.32
10:06:37 SQL>
10:06:37 SQL> declare
10:06:37 2 i1_v integer;
10:06:37 3 i2_v integer;
10:06:37 4 o1_v integer;
10:06:37 5 o2_v integer;
10:06:37 6 begin
10:06:37 7 kev_specification_test.px (i1_v,i2_v,o1_v,o2_v);
10:06:37 8 end;
10:06:37 9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 02:07:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
