How to call different data base object from another data base obejct in oracle

************************************Call procedure within trigger*******************************************************

create or replace procedure p_1 is
begin
dbms_output.put_line(‘SAJAL’);
end;

create or replace trigger t_1
before insert on saj
for each row
begin
p_1;
end;

************************************Call function within trigger*******************************************************

create or replace function f_1(no number) return number is
sal number;
begin
select salary into sal from saj where id=no;
return(no);
end;

create or replace trigger t_1
before insert on saj
for each row
begin
if f_1(1)>2 then
dbms_output.put_line(‘aaaa’);
end if;
end;

or

create or replace trigger t_1
before insert on saj
for each row
declare
l number;
begin
l:=f_1(1);
dbms_output.put_line(l);
end;

**********************************Call function(return) within trigger***************************************

create or replace function f_1(saj_id number)return number is
sal number;
begin
select salary into sal from saj where id=saj_id;
return(sal);
end;

create or replace trigger t_1
before insert on saj
for each row
declare
v_1 number;
begin
v_1:=f_1(1);
dbms_output.put_line(v_1);
end;

**********************************Call function(return) within procedure***************************************

create or replace function f_1(saj_id number)return number is
sal number;
begin
select salary into sal from saj where id=saj_id;
return(sal);
end;

create or replace procedure p_1 is
l number:=10;
begin
l:=f_1(1);
dbms_output.put_line(l);
end;

**********************************Call procedure within function***************************************

create or replace procedure p_1 is
l number:=10;
begin
dbms_output.put_line(l);
end;
/

create or replace function f_1 return number is
begin
p_1;
return(1);
end;

**********************************Function within function***************************************

create or replace function f1
return varchar2
is
l number;
id varchar2(20);
function inner (no number) return number
is
begin
select employee_id into id from employees
where employee_id=no;
return(1);
end;
begin
l:=inner(198);
dbms_output.put_line(l);
return(2);
end;

Advertisements