Solved

PL/SQL Function Problem

Posted on 2006-11-01
14
1,386 Views
Last Modified: 2008-01-09
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
Comment
Question by:Toast_Boy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 6

Expert Comment

by:chedgey
ID: 17848843
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
 
LVL 1

Author Comment

by:Toast_Boy
ID: 17849093
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
 
LVL 32

Expert Comment

by:awking00
ID: 17849372
You need a semi-colon after the second END IF statement.
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 1

Author Comment

by:Toast_Boy
ID: 17867729
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
 
LVL 1

Expert Comment

by:ms_sanjay
ID: 17871957
Hi Toast,
   Yes it is possible. Try creating a package and then call a function within a function.

Thanks
Sanjay Samuel.
0
 
LVL 1

Author Comment

by:Toast_Boy
ID: 17881387
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
 
LVL 1

Accepted Solution

by:
ms_sanjay earned 250 total points
ID: 17886377
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
 
LVL 1

Expert Comment

by:ms_sanjay
ID: 17903584
Hi Toast Boy,
   Have you tried the code which I posted. Let me know how it goes.

Thanks
Sanjay Samuel.
0
 
LVL 1

Author Comment

by:Toast_Boy
ID: 17904893
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
 
LVL 1

Expert Comment

by:ms_sanjay
ID: 17906574
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
 
LVL 1

Expert Comment

by:ms_sanjay
ID: 17952920
Hi Toast Boy,
   Any luck with this. Let me know.

Thanks
Sanjay Samuel.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Database Owner 3 45
CentOS Backup Options 3 66
Joomla installation cannot see any mysql databases 4 60
replacate wordpress websites 3 23
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question