CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcompilationcursor
Darwiin
Darwiin

Reputation: 3

Compilation error when creating pl/sql stored procedure

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.

Upvotes: 0

Views: 156

Answers (1)

Klas Lindbäck
Klas Lindbäck

Reputation: 33283

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.

Upvotes: 2

Related Questions