passing query statement [ insert , update ] to function as parameter and execute it

Question

what can i do if i want to pass insert statement to this function as a parameter and executed it in function and return value

create or replace function new_record (p_name your_table.first_name%type)
    return your_table.id%type
is
    return_value your_table.id%type;
begin
    begin
        insert into your_table (id, first_name)
            values (your_seq.nextval, p_first_name)
        returning id into return_value;
    exception
        when dup_val_on_index then
             return_value := 0;
    end;
    return return_value;
end;

use dynamic SQL e.g. EXECUTE IMMEDIATE 'INSERT STATEMENT';

Satya
– Satya

2013-04-08 09:37:50 +00:00
Commented Apr 8, 2013 at 9:37 — Satya
– Satya, Commented Apr 8, 2013 at 9:37

Vasily Komarov · Accepted Answer · 2013-04-08 10:06:02Z

1

use "execute immediate" statement:

This is simple example, just execute this sql and you will see how it works:

declare
  p_param   number := 123;
  l_res varchar2(10);
  l_sqltext varchar(100);
begin
  l_sqltext := 'begin select t.dummy into :1 from dual t where 123 = :2; end;';
  execute immediate l_sqltext using out l_res, in p_param;
  dbms_output.put_line(l_res);
end;

answered Apr 8, 2013 at 10:06

Vasily Komarov

1,4259 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

passing query statement [ insert , update ] to function as parameter and execute it

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related