Call Procedure In Declaration Part...

[keerti_rose]

Hi Can I call one Procedure in another procedure declaration part?

[Babar]

Basic Syntex for a Procedure:
<b><font color="blue">DECLAGE </b></font id="blue"><font color="green">(Optional, used to declare variables, types, cursors etc)</font id="green">
...
...
<b><font color="blue">BEGIN</font id="blue"></b>
<font color="green">(required, all executables are coded here, since calling a
procedure is an executable you call here) </font id="green">
...
...
<b><font color="blue">EXCEPTION </b></font id="blue"><font color="green">(optional, need to handle errors) </font id="green">
...
...
<b><font color="blue">END</b></font id="blue"> <font color="green">(required).</font id="green">

I hope this answers your question, if I am wrong please correct me...

[ahmadbilal]

I believe in declaration part of a procedure you can call function but not procedure look

CREATE OR REPLACE PROCEDURE APPS.edw_drop_btree_ind (owner VARCHAR2, table_name VARCHAR2) AUTHID CURRENT_USER AS
/* $Header: EDWDRIND.pls 115.1.310.3 2001/08/31 18:52:16 pkm ship $*/

x_index_name varchar(30);
sql_stmt varchar(2000);
cur_stmt varchar2(2000);
x_table_name varchar2(30);
x_owner varchar2(30):= exe_2_word(P_id); (exe_2_word is a function)



Just try to change your procedure to function if possible and let us know

[Babar]

SQL> CREATE OR REPLACE PACKAGE test AS
2 FUNCTION test1(p_val IN number)
3 RETURN NUMBER;
4 END test;
5 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY test AS
2 FUNCTION test1(p_val IN number)
3 RETURN NUMBER IS
4 VAL NUMBER;
5 BEGIN
6 VAL := p_val + 10;
7 RETURN VAL;
8 END test1;
9 END test;
10 /

Package body created.

SQL> create or replace procedure test2 is
2 l_num number := test.test1(20);
3 begin
4 if l_num > 60 then
5 dbms_output.put_line('You passed less then 50.');
6 else
7 dbms_output.put_line('You passed greater then 50.');
8 end if;
9 end test2;
10 /

Procedure created.

SQL> execute test2;
You passed greater then 50.

PL/SQL procedure successfully completed.

[admin]

Thanks to all of you but I am still interested to know from keerti_rose that why he/she is interested to call procedure in the declaration? Thanks

[keerti_rose]

Hi All..Thanks A lot for this..

The reason Why I am asking you this..One of my Collegues wrote one proc Like that using Procedure in Declaration Prat..I told him it's not possible..On his request i posted the same in this community.

FYR..Plese find the Proc below

CREATE OR REPLACE PROCEDURE DBA_USP_ARCH_TRANS_DATA(p_arch_date in date default sysdate)
AS

--The procedure to be scheduled to run once everyday for archiving data from all transaction tables
--There need be an entry in TBL_PARTITION_PARAMS for all transaction tables to be archived
--In case of error, log to TBL_ARCH_ERR_LOG? and continue archiving for rest of the tables in the list


cursor CURS_TBL_PARTITION_PARAMS is
select
OLTP_TABLE_NAME,
arch_table_name,
decode(arch_tablespace_name,NULL,NULL,' TABLESPACE '||arch_tablespace_name) arch_tablespace_name,
arch_strg_log_comprs_clause
from TBL_PARTITION_PARAMS
order by priority_val;

REC_CURS_TBL_PARTITION_PARAMS CURS_TBL_PARTITION_PARAMS%rowtype;

v_errm varchar2(1000);
v_out number(1);

<b>procedure in_sp_log_partition_err(p_tabname in varchar2, p_err_type in varchar2, p_errm in varchar2,p_arch_dt in date) as
pragma autonomous_transaction;
begin

Insert into TBL_PARTITION_ERRLOGS(TABLE_NAME,ERR_TYPE,ERROR_MESSAGE,ARCH_DATE) values
(p_tabname, p_err_type, p_errm,TRUNC(p_arch_dt));
commit;
end;</b>
BEGIN


for v_curs_var1 in CURS_TBL_PARTITION_PARAMS
loop

DBA_USP_CREATE_PARTITION(v_curs_var1.arch_table_name,p_arch_date,'dd-MON-yyyy',v_curs_var1.arch_tablespace_name,v_curs_var1.arch_strg_log_comprs_clause,v_ERRM,v_OUT);
If V_OUT<>0 then
--log to partition log table
in_sp_log_partition_err(v_curs_var1.arch_table_name,'CREATE_PARTITION',v_ERRM,p_arch_date);
else

DBA_USP_SWAP_PARTITIONS(v_curs_var1.arch_table_name ,v_curs_var1.oltp_table_name,p_arch_date,V_ERRM,V_OUT);
If V_OUT<>0 then
--log to partition log table
in_sp_log_partition_err(v_curs_var1.arch_table_name,'SWAP_PARTITION',v_ERRM,p_arch_date);

end if;
end if;


end loop;

EXCEPTION
WHEN OTHERS THEN
in_sp_log_partition_err('','ORACLE_ERROR',SQLERRM,p_arch_date);

END;
/

[keerti_rose]

Hi Friends...!

We can call one Procedure in another procedure declaration part by Using <b>Pragma Autonomous transaction</b>;