Home » SQL & PL/SQL » SQL & PL/SQL » Performing DML operations on Pipelined functions (Oracle 10g)
Performing DML operations on Pipelined functions [message #303490] Fri, 29 February 2008 09:43 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Hi I need to be able to perform DML operations on pipelined functions. How do I do it?
Here is my code snippet. But the package body does not compile.
It says invalid table name. How can this be corrected?

create or replace package test_plsql_tab is

  
   TYPE phk_test_rec IS RECORD
       (name_id   varchar2(10),
        name      varchar2(20),
        salary    number,
        dept_name varchar2(20)
       );
   
   TYPE phk_test_tab IS TABLE OF phk_test_rec;
   
   g_phk_test_tab phk_test_tab;
   

  
  FUNCTION get_phk_test_rec RETURN phk_test_tab
          PIPELINED ;
          
  procedure main;

end test_plsql_tab;


create or replace package body test_plsql_tab is

  FUNCTION get_phk_test_rec RETURN phk_test_tab
          PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION;
     BEGIN
          FOR i IN 1 .. g_phk_test_tab.COUNT
          LOOP
               PIPE ROW(g_phk_test_tab(i));
          END LOOP;
          RETURN;
     END;
     
     procedure main is
     cursor y is select * from table(test_plsql_tab.get_phk_test_rec);
     begin
        g_phk_test_tab := phk_test_tab();
        g_phk_test_tab.DELETE;
        
        g_phk_test_tab.EXTEND;
        g_phk_test_tab(1).name_id := 10;
        g_phk_test_tab(1).name:='Prashant';
        g_phk_test_tab(1).salary:=1000000;
        g_phk_test_tab(1).dept_name:='ENG';
        g_phk_test_tab.EXTEND;
        g_phk_test_tab(2).name_id := 20;
        g_phk_test_tab(2).name:='Hari';
        g_phk_test_tab(2).salary:=10000;
        g_phk_test_tab(2).dept_name:='COM';
        g_phk_test_tab.EXTEND;
        g_phk_test_tab(3).name_id := 30;
        g_phk_test_tab(3).name:='Prasad';
        g_phk_test_tab(3).salary:=20000;
        g_phk_test_tab(3).dept_name:='TEC';
        
        delete from table(test_plsql_tab.get_phk_test_rec)
        where name_id=20;
        
        for j in y
        loop
        dbms_output.put_line('name_id: '||j.name_id);
        dbms_output.put_line('name: '||j.name);
        dbms_output.put_line('salary: '||j.salary);
        dbms_output.put_line('department Name: '||j.dept_name);
        end loop;
         
        
     end;
end test_plsql_tab;
Re: Performing DML operations on Pipelined functions [message #303499 is a reply to message #303490] Fri, 29 February 2008 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, pipelined function are just for select.

Regards
Michel
Re: Performing DML operations on Pipelined functions [message #303516 is a reply to message #303499] Fri, 29 February 2008 12:14 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Ok Thanks Michel.
Re: Performing DML operations on Pipelined functions [message #303583 is a reply to message #303490] Fri, 29 February 2008 21:25 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
But, you might want to look at something like this:

create table temp_not_related_to_problem (a number)
/

create or replace type o_date is object (adate date)
/
create or replace type c_date is table of o_date
/

create or replace function temp_f1 return c_date pipelined is
begin
   pipe row(o_date(sysdate));
end;
/
show errors

create or replace view temp_v1
as
select *
from table(cast(temp_f1 as c_date))
/

select * from temp_v1;

create or replace trigger io_temp_v1
instead of insert or update or delete on temp_v1
for each row
begin null;
   -- do what ever you want like this
   if deleting then delete from temp_not_related_to_problem; end if;
end;
/
show errors

insert into temp_not_related_to_problem values (1);
commit;

select * from temp_not_related_to_problem;
select * from temp_v1;

delete from temp_v1;

select * from temp_not_related_to_problem;
select * from temp_v1;

/*
drop table temp_not_related_to_problem;
drop view temp_v1;
drop function temp_f1;
drop type c_date;
drop type o_date;
*/


By wrapping the table function in a view, it becomes updatable using instead-of-triggers. Run the code, check the results. For more information, google instead of triggers.

Running the code will show you that we are able to update the view that shows results from your table function. Is this updating the table function? Guess it depends upon your perspective.

Good luck, Kevin
Previous Topic: How to interpret explain plan
Next Topic: need help in writing pl/sql script
Goto Forum:
  


Current Time: Fri Dec 09 11:33:00 CST 2016

Total time taken to generate the page: 0.10633 seconds