CODEWITHSUNDEEP
sqloracle-databaseplsql
Ivan Babuder
Ivan Babuder

Reputation: 1

Trying to execute function in PL/SQL but I am getting error

CREATE OR REPLACE FUNCTION f_dohvati_post 
(
    post_id int) 
    RETURN postovi%ROWTYPE AS redak postovi%ROWTYPE;

BEGIN
    SELECT *
    INTO redak
    from postovi
    WHERE id = post_id;
    RETURN redak;
END;

I tried executing function with

Exec f_dohvati_post(1);

And i am getting this error ->

Error starting at line : 29 in command -
BEGIN f_dohvati_post(1); END;
Error report -
ORA-06550: line 1, column 7:
PLS-00221: 'F_DOHVATI_POST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Upvotes: 0

Views: 162

Answers (1)

Littlefoot
Littlefoot

Reputation: 143088

I don't have your table so I created one:

SQL> create table postovi as select deptno id, dname, loc from dept;

Table created.

SQL> create or replace function f_dohvati_post(post_id int)
  2    return postovi%rowtype
  3  as
  4    redak postovi%rowtype;
  5  begin
  6    select *
  7      into redak
  8      from postovi
  9      where id = post_id;
 10    return redak;
 11  end;
 12  /

Function created.

It is a function, so you don't "execute" it, but:

SQL> select f_dohvati_post(10) from dual;
select f_dohvati_post(10) from dual
       *
ERROR at line 1:
ORA-00902: invalid datatype


SQL>

Whoops! Won't work either! So, what to do? Create your own type:

SQL> create or replace type t_row as object
  2    (id      number,
  3     dname   varchar2(10),
  4     loc     varchar2(10));
  5  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

SQL> create or replace function f_dohvati_post(post_id int)
  2    return t_tab
  3  as
  4    redak t_tab;
  5  begin
  6    select t_row(id, dname, loc)
  7      bulk collect into redak
  8      from postovi
  9      where id = post_id;
 10    return redak;
 11  end;
 12  /

Function created.

SQL> select * from table(f_dohvati_post(10));

        ID DNAME      LOC
---------- ---------- ----------
        10 ACCOUNTING NEW YORK

SQL>

Now it works.

Upvotes: 1

Related Questions