Compilation error when creating pl/sql stored procedure

Question

I'm trying to create a stored procedure that used REF CURSOR. I am using this website to find some examples. First I created types:

CREATE OR REPLACE TYPE ROW_TYPE IS OBJECT (
"COL1" CHAR(3 BYTE), 
"COL2" NUMBER(4,0)
)

CREATE TYPE ROW_TYPE_TABLE AS TABLE OF ROW_TYPE;

Then created packages:

CREATE OR REPLACE PACKAGE package AS 
FUNCTION get_by_id(p_id CHAR) RETURN ROW_TYPE_TABLE PIPELINED;
END package;

But when creating package body using the following command I get the following message PLS-00103: Encountered the symbol "FOR"

I have checked on Oracle official website how to use the open-for statement but can't find my mistake.

CREATE OR REPLACE PACKAGE BODY package AS
FUNCTION get_by_id(p_idCHAR) RETURN SERV_TYPE_TABLE PIPELINED IS
    OUT_REC SERV_TYPE_TABLE := SERV_TYPE_TABLE(null,null);
    servCursor sys_refcursor;
    OPEN servCursor FOR 'SELECT * FROM SERV WHERE COL1= :1' USING p_id;

    LOOP 
        FETCH servCursor INTO   OUT_REC.COL1,   OUT_REC.COL;
        EXIT WHEN servCursor%NOTFOUND;
        PIPE ROW(OUT_REC);
    END LOOP;
    CLOSE servCursor;
    RETURN;
END get_by_id;
END package ;

Do you see any problem in the code submitted?

Regards.

add BEGIN after servCursor sys_refcursor; line

A.B.Cade
– A.B.Cade

2014-02-11 14:07:27 +00:00
Commented Feb 11, 2014 at 14:07 — A.B.Cade
– A.B.Cade, Commented Feb 11, 2014 at 14:07

Klas Lindbäck · Accepted Answer · 2014-02-11 14:55:01Z

2

PL/SQL functions have the structure:

FUNCTION <function name>(parameter1 <type>, ...) RETURN <return type> IS
    <variable declarations>
BEGIN
    <function body (code)>
END <function name>;

You forgot the BEGIN.

answered Feb 11, 2014 at 14:55

Klas Lindbäck

33.4k5 gold badges61 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Compilation error when creating pl/sql stored procedure

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