PL/SQL Function Problem

Toast_Boy,

It is hard to help without the errors. Can you post them please.

There are some obvious problems such as the line "SFILL VARCHAR2(14) := '--------------'" should be terminated with a semicolon.

Regards

Chedgey

Managed to figure out how to use 'Oracle SQL Developer', which is a free download.

I have managed to get these error messages :

Error(60,2): PLS-00103: Encountered the symbol "FOR" when expecting one of the following:     ; The symbol ";" was substituted for "FOR" to continue.
Error(70,26): PLS-00103: Encountered the symbol ";" when expecting one of the following:     . ( ) * % & = - + < / > at in is mod not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like    between || The symbol ")" was substituted for ";" to continue.
Error(75,18): PLS-00103: Encountered the symbol "=" when expecting one of the following:     . ( * @ % & = - + ; < / > at in is mod not rem    <an exponent (**)> <> or != or ~= >= <= <> and or like    between || The symbol ". was inserted before "=" to continue.  

Looks like I hav'nt put the correct syntax in somewhere. As I am not too familar with PL/SQL, can I have some guidance?

You need a semi-colon after the second END IF statement.

I don't think that I am getting the answers I want.

How about this, can I call a function from within a function.

So the scenario would be this :

CREATE OR REPLACE FUNCTION GET_PERIOD_START(IN_DATE IN DATE)
RETURN DATE AS
X DATE;
BEGIN
X := ROUND(TO_DATE(IN_DATE), 'W') + 1;
RETURN X;
END

CREATE OR REPLACE FUNCTION GET_NEXT_WEEK(IN_DATE IN DATE)
RETURN DATE AS
X DATE;
BEGIN
X := GET_PERIOD_START(IN_DATE) + 7;
RETURN X;
END

Is this type of thing possible?

Hi Toast,
   Yes it is possible. Try creating a package and then call a function within a function.

Thanks
Sanjay Samuel.

It seems to work, however, now I am getting PLS-00363 and PLS-00363 errors now.

Here is the full code :

CREATE OR REPLACE TYPE TMP_ATT_DATA_OBJ IS OBJECT (STUD_ID VARCHAR2(50),BASE_ID VARCHAR2(50),START_DATE DATE,MON_AM VARCHAR2(1),MON_PM VARCHAR2(1),TUE_AM VARCHAR2(1),TUE_PM VARCHAR2(1),WED_AM VARCHAR2(1),WED_PM VARCHAR2(1),THU_AM VARCHAR2(1),THU_PM VARCHAR2(1),FRI_AM VARCHAR2(1),FRI_PM VARCHAR2(1),SAT_AM VARCHAR2(1),SAT_PM VARCHAR2(1),SUN_AM VARCHAR2(1),SUM_PM VARCHAR2(1));
/
CREATE OR REPLACE PACKAGE PARSE_ATTENDANCE
IS
      TYPE TMP_ATT_DATA_TBL IS TABLE OF TMP_ATT_DATA_OBJ;
      TYPE SARRAY IS VARRAY(16) OF VARCHAR(30);
      FUNCTION GET_PERIOD_START(IN_DATE IN DATE) RETURN DATE;
      FUNCTION GET_INI_POS(IN_DATE IN DATE) RETURN DATE;
      FUNCTION ENUM_MARKS(C_CURSOR IN SYS_REFCURSOR) RETURN TMP_ATT_DATA_TBL;
END PARSE_ATTENDANCE;
/
CREATE OR REPLACE PACKAGE BODY PARSE_ATTENDANCE AS

FUNCTION GET_PERIOD_START(IN_DATE IN DATE)
RETURN DATE IS
      X DATE;
BEGIN
      X := ROUND(TO_DATE(IN_DATE), 'W') + 1;
RETURN X;
END;

FUNCTION GET_INI_POS(IN_DATE IN DATE)
RETURN NUMBER IS
      X NUMBER;
BEGIN
      X := TO_NUMBER(TO_CHAR(IN_DATE, 'D'));
RETURN X;
END;

FUNCTION ENUM_MARKS(C_CURSOR IN SYS_REFCURSOR)
RETURN TMP_ATT_DATA_TBL PIPELINED
IS

L_REC      DX_XML_ATTENDANCE%ROWTYPE;
PDATE DATE;
SDATE DATE;
EDATE DATE;
SLEN NUMBER;
WEEKLEN NUMBER;
INIPOS NUMBER;
I NUMBER;
MARRAY VARCHAR2(1000);
SUBARRAY SARRAY;
SFILL VARCHAR2(14) := '--------------';

BEGIN

LOOP
      FETCH C_CURSOR INTO L_REC;
      EXIT WHEN (C_CURSOR%NOTFOUND);

SDATE := GET_PERIOD_START(L_REC.START_DATE);
INIPOS := GET_INI_POS(L_REC.START_DATE);
SLEN := INIPOS;
PDATE := SDATE;
EDATE := SDATE + (LENGTH(L_REC.MARKS) / 2);
MARRAY := SUBSTR(L_REC.MARKS, 1, SLEN);
WEEKLEN := LENGTH(MARRAY);

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

SUBARRAY(0) := L_REC.STUD_ID;
SUBARRAY(1) := L_REC.BASE_ID;
SUBARRAY(2) := PDATE;

FOR I IN 3 .. 16
LOOP
      SUBARRAY(I) := SUBSTR(MARRAY, I + 1 - 3, 1);
      I := I + 1;
END LOOP;

PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(0),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)));

LOOP
       MARRAY := SUBSTR(L_REC.MARKS, SLEN + 1, 14);

      WEEKLEN := LENGTH(MARRAY);

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

      FOR I IN 3 .. 16
      LOOP
            SUBARRAY(I) := SUBSTR(MARRAY, I + 1 - 3, 1);
      I := I + 1;
      END LOOP;
      
      SUBARRAY(2) := PDATE;
      
PIPE ROW(TMP_ATT_DATA_OBJ(SUBARRAY(0),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)));

      PDATE := PDATE + 7;
      SLEN := SLEN + 14;
      
      IF PDATE = EDATE THEN
            EXIT;
      END IF;
END LOOP;
END LOOP;
END;
END PARSE_ATTENDANCE;

I am getting these errors from my compiler :

Error(6,11): PLS-00323: subprogram or cursor 'GET_INI_POS' is declared in a package specification and must be defined in the package body
Error(7,11): PLS-00323: subprogram or cursor 'ENUM_MARKS' is declared in a package specification and must be defined in the package body
Error(42,11): PLS-00307: too many declarations of 'GET_INI_POS' match this call
Error(60,2): PLS-00363: expression 'I' cannot be used as an assignment target

I can't see what is causing the PLS-00323 error, it refers to mispelt functions and not declaring them, but it looks OK.

I don't know about the  PLS-00307, and I am trying to use an array and looping through it, I think datatypes and such are causing the PLS-00363 error.

Any more pointerd would be good

Hi Toast Boy,
   Have you tried the code which I posted. Let me know how it goes.

Thanks
Sanjay Samuel.

Hi Sanjay

You gave me some good pointers, but the code in itself did not work. The problem is that I did not know how to do arrays in PL/SQL. But now I have sorted it out and re-wrote the code, the problem I am having is a succesful loop termination.

Here's a snippet :

WHILE PDATE < EDATE LOOP

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

END LOOP;

I'm getting a 'ORA-06503: PL/SQL: Function returned without value' error at this section.

What would be a better way to do this?

Hi Toast Boy,
   Do something like this.

If PDATE = NULL and SLEN = NULL then
   RETURN 0;
End If

Let  me know if this works.

Thanks
Sanjay Samuel.

Hi Toast Boy,
   Any luck with this. Let me know.

Thanks
Sanjay Samuel.