Call Procedure In Declaration Part...
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
Call Procedure In Declaration Part...
Hi Can I call one Procedure in another procedure declaration part?
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...
<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...
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
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
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
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.
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.
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
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;
/
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;
/
-
- Posts: 40
- Joined: Sat Mar 01, 2008 3:39 pm
- Location: India
Who is online
Users browsing this forum: Ahrefs [Bot] and 1 guest