Solved

PL/SQL Function Problem

Posted on 2006-11-01
14
1,358 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
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 31

Expert Comment

by:awking00
ID: 17849372
You need a semi-colon after the second END IF statement.
0
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CDC audit 17 85
Top 100 6 103
report returning null 21 52
MySQL ERROR 1045 (28000) 2 40
Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now