Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06503: PL/SQL: Function returned without value
ORA-06503: PL/SQL: Function returned without value [message #204051] Fri, 17 November 2006 08:41 Go to next message
toastmax
Messages: 16
Registered: November 2006
Junior Member
Hello

Having a bit of a problem with piplined functions.

Why does this work :

 
SET SERVEROUTPUT ON
DECLARE 

TYPE SARRAY IS TABLE OF VARCHAR2(4000);
CURSOR CU IS SELECT * FROM DX_XML_ATTENDANCE WHERE STUD_ID = 107777 AND BASE_ID = 94;
T_STUD NUMBER(10);
T_BASE NUMBER(10);
T_DATE DATE;
T_MARKS VARCHAR2(1000);
LEN_MARKS NUMBER;
PDATE DATE;
SDATE DATE;
EDATE DATE;
SLEN NUMBER;
WEEKLEN NUMBER;
INIPOS NUMBER;
MARRAY VARCHAR2(1000);
SUBARRAY SARRAY := SARRAY();
SFILL VARCHAR2(14) := '--------------';
EPOS NUMBER;
MY_REC DX_XML_ATTENDANCE%ROWTYPE;

BEGIN 

SUBARRAY.EXTEND(17);
DBMS_OUTPUT.ENABLE(100000000);

OPEN CU;
LOOP 
FETCH CU INTO MY_REC;
EXIT WHEN (CU%NOTFOUND);

T_STUD := MY_REC.STUD_ID;
T_BASE := MY_REC.BASE_ID;
T_DATE := TO_DATE(MY_REC.START_DATE, 'DD/MM/YYYY');
T_MARKS := MY_REC.MARKS;

LEN_MARKS := LENGTH(T_MARKS);
EPOS := LEN_MARKS / 2;
SDATE := ROUND(TO_DATE(T_DATE), 'W') - 1;
INIPOS := TO_NUMBER(TO_CHAR(T_DATE, 'D'));
SLEN := INIPOS + 3;
PDATE := SDATE;
EDATE := SDATE + EPOS;

MARRAY := SUBSTR(T_MARKS, 1, SLEN);
WEEKLEN := LENGTH(MARRAY);

IF WEEKLEN < 14 THEN 
MARRAY := SUBSTR(SFILL, 1, 14 - WEEKLEN) || MARRAY;
END IF;

SUBARRAY(1) := T_STUD;
SUBARRAY(2) := T_BASE;
SUBARRAY(3) := PDATE;

FOR i IN 4 .. 17 LOOP
SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
END LOOP;

DBMS_OUTPUT.PUT_LINE(SUBARRAY(1)||' '||SUBARRAY(2)||' '||SUBARRAY(3)||' '||SUBARRAY(4)||' '||
SUBARRAY(5)||' '||SUBARRAY(6)||' '||SUBARRAY(7)||' '||SUBARRAY(8)||' '||SUBARRAY(9)||' '||
SUBARRAY(10)||' '||SUBARRAY(11)||' '||SUBARRAY(12)||' '||SUBARRAY(13)||' '||SUBARRAY(14)||' '||
SUBARRAY(15)||' '||SUBARRAY(16)||' '||SUBARRAY(17));

WHILE PDATE < EDATE LOOP

PDATE := PDATE + 7;


MARRAY := SUBSTR(T_MARKS, SLEN + 1, 14);

WEEKLEN := LENGTH(MARRAY);

IF WEEKLEN < 14 THEN 
MARRAY := MARRAY || SUBSTR(SFILL, 1, 14 - WEEKLEN);
END IF;

FOR i IN 4 .. 17 LOOP
SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
END LOOP;

SUBARRAY(3) := PDATE;

DBMS_OUTPUT.PUT_LINE(SUBARRAY(1)||' '||SUBARRAY(2)||' '||SUBARRAY(3)||' '||SUBARRAY(4)||' '||
SUBARRAY(5)||' '||SUBARRAY(6)||' '||SUBARRAY(7)||' '||SUBARRAY(8)||' '||SUBARRAY(9)||' '||
SUBARRAY(10)||' '||SUBARRAY(11)||' '||SUBARRAY(12)||' '||SUBARRAY(13)||' '||SUBARRAY(14)||' '||
SUBARRAY(15)||' '||SUBARRAY(16)||' '||SUBARRAY(17));

PDATE := PDATE + 7;
SLEN := SLEN + 14; 
END LOOP;
END LOOP;
END;


and this does not :

CREATE OR REPLACE PACKAGE BODY PARSE_ATTENDANCE AS 

FUNCTION ENUM_MARKS(SEL_SQL IN VARCHAR2) 
RETURN TMP_ATT_DATA_TBL PIPELINED
IS 

V_SQL VARCHAR(1000):= SEL_SQL;
V_CURSOR SYS_REFCURSOR;
V_ROW TMP_ATT_HOLDING:=TMP_ATT_HOLDING(NULL, NULL, NULL, NULL);
T_STUD NUMBER(10);
T_BASE NUMBER(10);
T_DATE DATE;
T_MARKS VARCHAR2(1000);
LEN_MARKS NUMBER;
PDATE DATE;
SDATE DATE;
EDATE DATE;
SLEN NUMBER;
WEEKLEN NUMBER;
INIPOS NUMBER;
MARRAY VARCHAR2(1000);
SUBARRAY SARRAY := SARRAY();
SFILL VARCHAR2(14) := '--------------';
EPOS NUMBER;

BEGIN

SUBARRAY.EXTEND(17);

OPEN V_CURSOR FOR V_SQL;

LOOP

FETCH V_CURSOR INTO V_ROW.STUD_ID, V_ROW.BASE_ID, V_ROW.START_DATE, V_ROW.MARKS;
EXIT WHEN V_CURSOR%NOTFOUND;

T_STUD := V_ROW.STUD_ID;
T_BASE := V_ROW.BASE_ID;
T_DATE := TO_DATE(V_ROW.START_DATE, 'DD/MM/YYYY');
T_MARKS := V_ROW.MARKS;

LEN_MARKS := LENGTH(T_MARKS);
EPOS := LEN_MARKS / 2;
SDATE := ROUND(TO_DATE(T_DATE), 'W') - 1;
INIPOS := TO_NUMBER(TO_CHAR(T_DATE, 'D'));
SLEN := INIPOS + 3;
PDATE := SDATE;
EDATE := SDATE + EPOS;

MARRAY := SUBSTR(T_MARKS, 1, SLEN);
WEEKLEN := LENGTH(MARRAY);

IF WEEKLEN < 14 THEN 
MARRAY := SUBSTR(SFILL, 1, 14 - WEEKLEN) || MARRAY;
END IF;

SUBARRAY(1) := T_STUD;
SUBARRAY(2) := T_BASE;
SUBARRAY(3) := PDATE;

FOR i IN 4 .. 17 LOOP
SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
END LOOP;

PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(1),SUBARRAY(2),SUBARRAY(3),SUBARRAY(4),
SUBARRAY(5),SUBARRAY(6),SUBARRAY(7),SUBARRAY(8),SUBARRAY(9),
SUBARRAY(10),SUBARRAY(11),SUBARRAY(12),SUBARRAY(13),SUBARRAY(14),
SUBARRAY(15),SUBARRAY(16),SUBARRAY(17)));

WHILE PDATE < EDATE LOOP

PDATE := PDATE + 7;


MARRAY := SUBSTR(T_MARKS, SLEN + 1, 14);

WEEKLEN := LENGTH(MARRAY);

IF WEEKLEN < 14 THEN 
MARRAY := MARRAY || SUBSTR(SFILL, 1, 14 - WEEKLEN);
END IF;

FOR i IN 4 .. 17 LOOP
SUBARRAY(i) := SUBSTR(MARRAY, i - 3, 1);
END LOOP;

SUBARRAY(3) := PDATE;

PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(1),SUBARRAY(2),SUBARRAY(3),SUBARRAY(4),
SUBARRAY(5),SUBARRAY(6),SUBARRAY(7),SUBARRAY(8),SUBARRAY(9),
SUBARRAY(10),SUBARRAY(11),SUBARRAY(12),SUBARRAY(13),SUBARRAY(14),
SUBARRAY(15),SUBARRAY(16),SUBARRAY(17)));

PDATE := PDATE + 7;
SLEN := SLEN + 14; 

END LOOP;
END LOOP;
END ENUM_MARKS;
END PARSE_ATTENDANCE;


(This is then called like SELECT * FROM
TABLE(
PARSE_ATTENDANCE.ENUM_MARKS(
'SELECT STUD_ID, BASE_ID, START_DATE, MARKS
FROM DX_XML_ATTENDANCE WHERE STUD_ID = 107777
AND BASE_ID = 94'))

I get the same error, around this section near the bottom :


PDATE := PDATE + 7;
SLEN := SLEN + 14;

Can any one help?

Re: ORA-06503: PL/SQL: Function returned without value [message #204055 is a reply to message #204051] Fri, 17 November 2006 09:02 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
From the PL/SQL manual

CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS
  out_rec TickerType := TickerType(NULL,NULL,NULL);
  in_rec p%ROWTYPE;
BEGIN
  LOOP
    FETCH p INTO in_rec; 
    EXIT WHEN p%NOTFOUND;
    -- first row
    out_rec.ticker := in_rec.Ticker;
    out_rec.PriceType := 'O';
    out_rec.price := in_rec.OpenPrice;
    PIPE ROW(out_rec);
    -- second row
    out_rec.PriceType := 'C';   
    out_rec.Price := in_rec.ClosePrice;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE p;
  RETURN;
END;
/



...

A pipelined table function must have a RETURN statement that does not return a value. The RETURN statement transfers the control back to the consumer and ensures that the next fetch gets a NO_DATA_FOUND exception.

Re: ORA-06503: PL/SQL: Function returned without value [message #204058 is a reply to message #204055] Fri, 17 November 2006 09:10 Go to previous message
toastmax
Messages: 16
Registered: November 2006
Junior Member
I have been hammering away at this for over a week now!


Many thanks for you help, it worked, finally!
Previous Topic: load/select data into subpartition
Next Topic: Setting data_precision with SQL
Goto Forum:
  


Current Time: Wed Dec 07 05:08:30 CST 2016

Total time taken to generate the page: 0.08424 seconds