How to execute stored function in Oracle PL/SQL

Question

I want to execute this stored function, and insert data in table t I tried to find solution, but unsuccessfully

CREATE TABLE t (id number
              , name varchar2(32)
              , time date);

CREATE OR REPLACE PACKAGE t_api AS
    FUNCTION func_ins (
        p_row IN t%rowtype
    ) RETURN t.id%TYPE;
END t_api;
/

CREATE OR REPLACE PACKAGE BODY t_api AS
FUNCTION func_ins (
    p_row IN t%rowtype
) RETURN t.id%TYPE 
IS
    l_id t.id%TYPE;
BEGIN
    INSERT INTO t VALUES p_row RETURNING id INTO l_id;    
    RETURN l_id;
END func_ins;
END t_api;
/


declare
p_row t%rowtype;
begin
p_row.id := 1;
p_row.name := 'name';
p_row.time := sysdate;
t_api.func_ins(p_row);
end;
/

I got

PLS-00221

Thanks in advance

What is the specific problem you are having? Please show how the function is being called. — OldProgrammer
– OldProgrammer, Commented Apr 30, 2020 at 0:23

VN'sCorner · Accepted Answer · 2020-04-30 07:47:41Z

1

It works perfectly ,however i wouldn't recommend this design as its not a good practice to perform a DML within a function. Rather create a procedure instead of function and retrieve the Id using the out parameter.

Anonymous block to test the function when table is empty.You assign values for %ROWTYPE variable and insert.

  declare 
    t_row t%rowtype;
    x t.id%type;
    begin

    t_row.id := 2;
    t_row.name := 'Test2';
    t_row.time := sysdate;

     x :=  t_api.func_ins(t_row);

     dbms_output.put_line('x '||x);

    end;

Output is

x 2

Modified code with Procedure to achieve the same result is below,

CREATE OR REPLACE PACKAGE t_api AS
    FUNCTION func_ins (
        p_row IN t%rowtype
    ) RETURN t.id%TYPE;
  PROCEDURE   proc_ins (
    p_row IN t%rowtype,
    l_id out t.id%TYPE
); 
END t_api;
/

CREATE OR REPLACE PACKAGE BODY t_api AS
FUNCTION func_ins (
    p_row IN t%rowtype
) RETURN t.id%TYPE 
IS
    l_id t.id%TYPE;
BEGIN
    INSERT INTO t VALUES p_row RETURNING id INTO l_id;    
    RETURN l_id;
END func_ins;
PROCEDURE proc_ins (
    p_row IN t%rowtype,
    l_id out t.id%TYPE
) 
IS

BEGIN
    INSERT INTO t VALUES p_row RETURNING id INTO l_id;    

END proc_ins;
END t_api;
/

Anonymous block to test the procedure,

declare 
t_row t%rowtype;
x t.id%type;
begin


t_row.id := 3;
t_row.name := 'Test3';
t_row.time := sysdate;

 t_api.proc_ins(t_row,x);
 dbms_output.put_line('x '||x);


end;

Output is

x 3

edited Apr 30, 2020 at 7:47

answered Apr 30, 2020 at 3:18

VN'sCorner

1,5521 gold badge9 silver badges13 bronze badges

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

7 Comments

Pavlos04 Over a year ago

Thanks a lot, but I can't insert in table with your answer

Belayer Over a year ago

Why not? What do you get? What does it actually do? Just saying you can't insert is completely useless.

Pavlos04 Over a year ago

When I execute declare t_row t%rowtype; x t.id%type; begin select * into t_row from t; x := t_api.func_ins(t_row); end; I got ORA-01403 NO_DATA_FOUND error

Pavlos04 Over a year ago

I want to insert data in table with this function. Table is empty

VN'sCorner Over a year ago

Modified the code now to insert when the table is empty. Here you assign values to %ROWTYPE variable and then you insert.I have tested the code.

|

Collectives™ on Stack Overflow

How to execute stored function in Oracle PL/SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related