Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO CALL PROCEDURE FROM ANOTHER TO INSERT VALUES INTO A TABLE.....
HOW TO CALL PROCEDURE FROM ANOTHER TO INSERT VALUES INTO A TABLE..... [message #225749] Wed, 21 March 2007 04:27 Go to next message
iaminto_oracle
Messages: 1
Registered: March 2007
Junior Member
Is it possible to call one procedure from another procedure to insert values into table?
I've a stored proceudre as follows.

create or replace procedure proc_sel_val(a in out sys_refcursor)
is
begin
open a for select salary from employees;
end;

I've to use this procedure to insert values into a table named 'test_tab' like follows.

create or replace procedure proc_ins_val
is
begin
insert into test_tab(sal) ........(here I've to use the proceudre proc_sel_val)
end;

Thanx in advance
Re: HOW TO CALL PROCEDURE FROM ANOTHER TO INSERT VALUES INTO A TABLE..... [message #225752 is a reply to message #225749] Wed, 21 March 2007 04:41 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Quick and dirty:
create table test_tab(sal number)
/

declare
  thecur sys_refcursor;
  thesal number;
  
  procedure proc_sel_val(a in out sys_refcursor)
  is
  begin
    open a for select salary from employees;
  end;
begin
  -- call the procedure
  proc_sel_val(thecur);
  
  -- loop through the fetched values.
  loop 
    -- fetch the value
    fetch thecur into thesal;
    
    -- exit when necessary
    exit when thecur%NOTFOUND;
    
    -- perform the insert
    insert into test_tab(sal) values (thesal);
  end loop;
  
  -- close the cursor
  close thecur;
end;
/

select * from test_tab
/

drop table test_tab
/


MHE
Previous Topic: Between !! Urgent
Next Topic: session time zone
Goto Forum:
  


Current Time: Mon Dec 05 09:13:30 CST 2016

Total time taken to generate the page: 0.14384 seconds