Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1414
  • Last Modified:

PL/SQL Function Problem

I'm trying to use Pipelined functions to tranform data into a more usable format.

The data looks like this :

START_DATE      BASE_ID  USER_ID          MARKS
25/05/2006         23           1234               XXXXX#####XXXXX''''''XXXXX~ ETC

and I want to transform the data into :


1234        23  21/08/2006  -   -   -   -   -   -   -   -   X   X   #   #   #   #
1234        23  28/08/2006  #   #   X   X   *   *   *   *   *   *   #   #   #   #
1234        23  04/09/2006  *   *   *   *   *   *   *   *   *   *   #   #   #   #
1234        23  11/09/2006  *   *   *   *   *   *   *   *   *   *   #   #   #   #
~ETC

I am able to do this in VB no problem, I establish the week beginning and end date
from the start date, and parse the marks into am/pm segments Mon to Sun.

I want now to be able to create a virtual view using the pipeline function in PL/SQL.

I have made a start :

CREATE OR REPLACE FUNCTION ENUM_MARKS(C_CURSOR IN SYSREFCURSOR)
RETURN TMP_ATT_DATA_TBL PIPELINED
AS

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

BEGIN

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

SDATE := ROUND(TO_DATE(L_REC.START_DATE), 'W') + 1;
INIPOS := TO_CHAR(L_REC.START_DATE, 'D');
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;

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

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

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

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

      WEEKLEN := LENGTH(MARRAY);

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

      FOR I IN 3 .. 16
            SUBARR(I) := SUBSTR(MARRAY, I + 1 - 3, 1);
      NEXT I
      
      SUBARR(2) := PDATE;
      
PIPE ROW(TMP_ATT_DATA_OBJ(SUBARR(0),SUBARR(1),SUBARR(2),SUBARR(3),SUBARR(4),
                  SUBARR(5),SUBARR(6),SUBARR(7),SUBARR(8),SUBARR(9),
                  SUBARR(10),SUBARR(11),SUBARR(12),SUBARR(13),SUBARR(14),
                  SUBARR(15),SUBARR(16));

      PDATE := PDATE + 7;
      SLEN := SLEN + 14;
      
      EXIT WHEN PDATE := EDATE;
END LOOP;
END LOOP;
END;

This is then called :

SELECT * FROM TABLE(ENUM_MARKS(CURSOR(SELECT * FROM MARKS)))

I have created the user objects used in the function, when I have compiled the function, there are errors, but as I am new to PL/SQL programming, I don't know how to debug.

Can anyone give me any pointers where the function is wrong.

0
Toast_Boy
Asked:
Toast_Boy
1 Solution
 
chedgeyCommented:
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
0
 
Toast_BoyAuthor Commented:
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?
0
 
awking00Commented:
You need a semi-colon after the second END IF statement.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Toast_BoyAuthor Commented:
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?
0
 
ms_sanjayCommented:
Hi Toast,
   Yes it is possible. Try creating a package and then call a function within a function.

Thanks
Sanjay Samuel.
0
 
Toast_BoyAuthor Commented:
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
0
 
ms_sanjayCommented:
Hi Toast Boy,
   Try this and let me know if you have any questions.

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 GET_PERIOD_START;

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 GET_INI_POS;

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 := PARSE_ATTENDANCE.GET_PERIOD_START(L_REC.START_DATE);
INIPOS := PARSE_ATTENDANCE.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 ENUM_MARKS;

END PARSE_ATTENDANCE;


I made the follwoing changes

1.END GET_PERIOD_START;
2.END GET_INI_POS;
3.END ENUM_MARKS;
4.SDATE := PARSE_ATTENDANCE.GET_PERIOD_START(L_REC.START_DATE);
INIPOS := PARSE_ATTENDANCE.GET_INI_POS(L_REC.START_DATE);

Regards
Sanjay Samuel.
0
 
ms_sanjayCommented:
Hi Toast Boy,
   Have you tried the code which I posted. Let me know how it goes.

Thanks
Sanjay Samuel.
0
 
Toast_BoyAuthor Commented:
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?

0
 
ms_sanjayCommented:
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.
0
 
ms_sanjayCommented:
Hi Toast Boy,
   Any luck with this. Let me know.

Thanks
Sanjay Samuel.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now