Solved

PL/SQL Function Problem

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

Expert Comment

by:awking00
ID: 17849372
You need a semi-colon after the second END IF statement.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying 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
TypeError: Users.save is not a function 3 68
Process mapping 5 67
Need a SQL Server 2014 plug-in to scan the DB schema 4 59
SQL Query assistance 16 45
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

685 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