Thanks for the question, Brian.

Asked: February 22, 2019 - 7:45 pm UTC

Last updated: March 01, 2019 - 1:13 am UTC

Version: 12 c

Viewed 10K+ times! This question is

You Asked

we are in the process of upgrading our oracle database from 11.2 to 12.2

ran into an error: ORA-06553: PLS-306: wrong number or types of arguments in call to

I have created a simplified script that recreates the issue.
the error occurs at compile time.
If I comment out the section that results in the compile time error I can create the same error at run time.
I have added comments that hopefully explain what is going on.
if you want to run the code then you will need to create a directory in oracle called SUBS
and then place in the folder that the SUBS directory maps to a file called small.txt
the file can have anything in it. The only requirements are that the file not be empty and that every line in the file have a length of less than 4000 characters.

Here is detailed info about the versions of oracle we are going from / to:

old version of oracle:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

new version of oracle:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

below is the script that recreates the error

--/*
CREATE OR REPLACE PACKAGE FILES AS

TYPE TRow is Record(RowNbr INTEGER, RowVal VARCHAR2(32767));
Type TFile is Table of TRow;
SubType FileNameType is varchar2(500);
SubType FolderNameType is varchar2(500);

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN TFile pipelined;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType,
    p_MaxLineLength IN INTEGER
)
RETURN TFile pipelined;

FUNCTION DATE_PARAMETERIZE_FILE_NAME
(
    p_FileName IN FileNameType,
    p_ASOF_DT IN DATE
)
RETURN FileNameType;

FUNCTION File_Backup(
    p_SrcFileName IN FileNameType,
    p_SrcFolderName IN FolderNameType,
    p_BackupFileName IN FileNameType,
    p_BackupSrcFolderName IN FolderNameType
)
RETURN BOOLEAN;

FUNCTION File_Exists(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN BOOLEAN;

PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
);

PROCEDURE AppendClob
(    
    p_file    UTL_FILE.FILE_TYPE,
    p_clob    CLOB,
    p_bufferSize BINARY_INTEGER DEFAULT 32767
    
);

END FILES;
commit;
CREATE OR REPLACE PACKAGE BODY FILES AS  
   
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN TFile pipelined IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(4000);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R');
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                v_RowNbr := v_RowNbr + 1;
                UTL_FILE.GET_LINE(v_File,v_Line);
                v_Row.RowNbr := v_RowNbr;
                v_Row.RowVal := v_Line;
                PIPE ROW(v_Row);
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType,
        p_MaxLineLength IN INTEGER
    )
    RETURN TFile pipelined IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(32767);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R',32767);
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                UTL_FILE.GET_LINE(v_File,v_Line,32767);
                if(Length(v_Line) <= p_MaxLineLength) then
                    v_RowNbr := v_RowNbr + 1;
                
                    v_Row.RowNbr := v_RowNbr;
                    v_Row.RowVal := v_Line;
                    PIPE ROW(v_Row);
                end if;
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
    )
    IS
    BEGIN
        IF(File_Exists(p_FileName, p_FolderName)) then
            UTL_FILE.FREMOVE(p_FolderName, p_FileName);            
        end if;
    END;
    
    FUNCTION File_Exists(
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN BOOLEAN        
    As
        v_FileLocation BFILE := NULL;
        v_FileExists BOOLEAN;
    BEGIN
        v_FileLocation := BFILENAME(p_FolderName, p_FileName);
        v_FileExists := DBMS_LOB.FILEEXISTS(v_FileLocation) = 1;
        return v_FileExists;
    END;

    FUNCTION File_Backup(
        p_SrcFileName IN FileNameType,
        p_SrcFolderName IN FolderNameType,
        p_BackupFileName IN FileNameType,
        p_BackupSrcFolderName IN FolderNameType
    )
    RETURN BOOLEAN
    AS
        v_WasSuccess BOOLEAN := FALSE;
    BEGIN
        if(File_Exists(p_SrcFileName, p_SrcFolderName)) then
            UTL_FILE.FRENAME(p_SrcFolderName, p_SrcFileName, p_BackupSrcFolderName, p_BackupFileName, TRUE);
            v_WasSuccess := TRUE;
        end if;
    
        return v_WasSuccess;
    END;
    PROCEDURE AppendClob
    (    
        p_file    UTL_FILE.FILE_TYPE,
        p_clob    CLOB,
        p_bufferSize BINARY_INTEGER DEFAULT 32767
        
    )
    as
        v_bufferSize BINARY_INTEGER := p_bufferSize;
        v_pos     INTEGER := 1;
        v_length number default nvl(dbms_lob.getlength(p_clob), 0);
    BEGIN
    if utl_file.is_open(p_file) then
        
        while(v_pos < v_length)   
        LOOP            
            UTL_FILE.put(p_file, dbms_lob.substr(p_clob, p_bufferSize, v_pos));
            UTL_FILE.FFLUSH(p_file);            
            v_pos := v_pos + v_bufferSize;
        END LOOP; 
        UTL_FILE.NEW_LINE(p_file);      
    end if;
    EXCEPTION
      WHEN OTHERS THEN    
        RAISE;                
    END; 
    
    FUNCTION DATE_PARAMETERIZE_FILE_NAME
    (
        p_FileName IN FileNameType,
        p_ASOF_DT IN DATE
    )
    RETURN FileNameType
    AS
        v_ToReturn FileNameType;
        v_Temp varchar(100);
    BEGIN
        if(TRIM(p_FileName) is not NULL) then
            v_ToReturn := Trim(p_FileName);
            if(p_ASOF_DT is NOT NULL) then
                v_Temp := Extract(YEAR from p_ASOF_DT);
                v_ToReturn := REPLACE(v_ToReturn, 'YYYY', v_Temp);
                
                v_Temp := LPAD(Extract(Month from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MM', v_Temp);
                
                v_Temp := LPAD(Extract(Day from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'DD', v_Temp);
                
                v_Temp := LPAD(Extract(HOUR from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'HH', v_Temp);                
                
                v_Temp := LPAD(Extract(Minute from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MI', v_Temp);                
                
                v_Temp := LPAD(Extract(Second from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'SS', v_Temp);
            end if;
        end if;
        return v_ToReturn;
    END;
           
END FILES;
commit;

--*/
--/*
CREATE OR REPLACE PACKAGE BUG_REPRODUCE AS
    Type PL_TBL Is Table of VARCHAR2(4000);
    
    FUNCTION ERROR_ON_COMPILE(
        p_Rows FILES.TFile    
    )
    RETURN PL_TBL PIPELINED;
    PROCEDURE ENTRY_POINT;
    
    
END BUG_REPRODUCE;
commit;

CREATE OR REPLACE PACKAGE BODY BUG_REPRODUCE AS
----------------------------------------------------------------------
FUNCTION ERROR_ON_COMPILE(
    p_Rows FILES.TFile    
)
RETURN PL_TBL PIPELINED
IS
    v_Str varchar2(4000);
BEGIN
   if p_Rows.COUNT > 0
    THEN
        For indx in p_Rows.FIRST .. p_Rows.LAST
        Loop
            v_Str := p_Rows(indx).ROWVAL;
            PIPE ROW(v_Str);
        END Loop;
    END IF;
    return;
END;
----------------------------------------------------------------------
PROCEDURE ENTRY_POINT
IS
    v_Rows_In_File INTEGER;
    v_FileName FILES.FileNameType;
    v_FolderName FILES.FolderNameType;
BEGIN
    -- in oracle create directory with the following name
    v_FolderName := 'SUBS';
    -- in direction that above directoy is mapped to create file with following name
    -- Note - each line in the file should have a length of less than 4000
    v_FileName := 'small.txt';
    
    
    
    DBMS_OUTPUT.PUT_LINE('this one works');
    select count(*) into v_Rows_In_File
    from TABLE(FILES.LOAD(v_FileName, v_FolderName));
    DBMS_OUTPUT.PUT_LINE('this one works: answer' || to_char(v_Rows_In_File));
    --/*
    --BEGIN SECTION THAT REVEALS COMPILE TIME BUG : ORA-06553: PLS-306: wrong number or types of arguments in call to 'ERROR_ON_COMPILE'
    DBMS_OUTPUT.PUT_LINE('this one cuases compile time error');        
    select count(*) into v_Rows_In_File
    from TABLE(ERROR_ON_COMPILE(FILES.LOAD(v_FileName, v_FolderName)));
    DBMS_OUTPUT.PUT_LINE('this one cuases compile time error : answer ' || to_char(v_Rows_In_File));
    --*/
END;
----------------------------------------------------------------------
END BUG_REPRODUCE;
commit;
--*/
/*  
-- the following statement demonstrates the FILES package is working as expected
select * from TABLE(FILES.LOAD('small.txt', 'SUBS'));
-- the following statement also demonstrates that the FILES package can be called from another package
execute BUG_REPRODUCE.ENTRY_POINT;
--BEGIN SECTION THAT REVEALS RUN TIME BUG : ORA-06553: PLS-306: wrong number or types of arguments in call to 'ERROR_ON_COMPILE'
-- NOTE: that you will first have to get the package to compile by commenting out the offending section that cause the compile time error
-- the following statement will produce error at run time BUG_REPRODUCE
select * from TABLE(BUG_REPRODUCE.ERROR_ON_COMPILE(FILES.LOAD('small.txt', 'SUBS')))
*/

and Chris said...

Yep, it's a bug. I get the same behaviour on 12.2+

Time for you to get in touch with support...

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ouroboros

Racer I., February 25, 2019 - 2:23 pm UTC

Hi,

I wonder, what is the supposed behaviour of passing a pipelined result into a function as a table-parameter? Piping is intended to be a stream but a table-parameter-function would expect the whole result in one go.
Should oracle read the first fully (negating the piping part)? Or make dynamically growing collection?

I would probably try making the outer pipelined function match the inner by expecting a (typed?) cursor :

from TABLE(ERROR_ON_COMPILE(CURSOR(
SELECT * FROM TABLE(FILES.LOAD(v_FileName, v_FolderName)))));

Bonus : try passing the result to itself... ;)

regards,

February 26, 2019 - 2:35 am UTC

Agreed.

In theory, it should always work regardless, i.e, it's all meant to be just sets of data, but yeah, I suspect we've hit on a boundary case here.

A potential workaround is - do we really need to *pipe* a list of files? I mean...how many files are you expecting? I could bulk collect a million file names into a collection and only use a few dozen megabytes...and even in that case, what are we doing with one million files ?!?!?

OS dependency?

AndyP, February 26, 2019 - 11:48 am UTC

Just fyi I ran that code against 11.2.0.4 and 12.1.0.2 on a linux platform and it works fine - no compilation errors and the query output as expected

February 26, 2019 - 12:06 pm UTC

That's the same behaviour I observe, it worked 12.1 and lower, the issue happens on 12.2 and higher.

Follow up to ouroboros comment

Brian Hanks, February 28, 2019 - 5:12 pm UTC

was intrigued by the option that ouroboros suggested.
decide to rework the example to refect that suggestion.
Results in code that compiles and works

Here it is:


CREATE OR REPLACE PACKAGE FILES AS
/******************************************************************************
   NAME:       FILE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        6/6/2017      bhanks       1. Created this package.
   1.1        7/12/2018     bhanks       2. add delete method
   1.2        2/28/2019     bhanks       3. add ref cursor for TFile type
******************************************************************************/

TYPE TRow is Record(RowNbr INTEGER, RowVal VARCHAR2(32767));
Type TFile is Table of TRow;
SubType FileNameType is varchar2(500);
SubType FolderNameType is varchar2(500);
TYPE TFileCursor is REF CURSOR RETURN TRow;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN TFile pipelined;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType,
    p_MaxLineLength IN INTEGER
)
RETURN TFile pipelined;

FUNCTION DATE_PARAMETERIZE_FILE_NAME
(
    p_FileName IN FileNameType,
    p_ASOF_DT IN DATE
)
RETURN FileNameType;

FUNCTION File_Backup(
    p_SrcFileName IN FileNameType,
    p_SrcFolderName IN FolderNameType,
    p_BackupFileName IN FileNameType,
    p_BackupSrcFolderName IN FolderNameType
)
RETURN BOOLEAN;

FUNCTION File_Exists(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN BOOLEAN;

PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
);

PROCEDURE AppendClob
(    
    p_file    UTL_FILE.FILE_TYPE,
    p_clob    CLOB,
    p_bufferSize BINARY_INTEGER DEFAULT 32767
    
);

END FILES;

CREATE OR REPLACE PACKAGE BODY FILES AS  
   
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN TFile pipelined
    IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(4000);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R');
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                v_RowNbr := v_RowNbr + 1;
                UTL_FILE.GET_LINE(v_File,v_Line);
                v_Row.RowNbr := v_RowNbr;
                v_Row.RowVal := v_Line;
                PIPE ROW(v_Row);
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType,
        p_MaxLineLength IN INTEGER
    )
    RETURN TFile pipelined IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(32767);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R',32767);
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                UTL_FILE.GET_LINE(v_File,v_Line,32767);
                if(Length(v_Line) <= p_MaxLineLength) then
                    v_RowNbr := v_RowNbr + 1;
                
                    v_Row.RowNbr := v_RowNbr;
                    v_Row.RowVal := v_Line;
                    PIPE ROW(v_Row);
                end if;
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
    )
    IS
    BEGIN
        IF(File_Exists(p_FileName, p_FolderName)) then
            UTL_FILE.FREMOVE(p_FolderName, p_FileName);            
        end if;
    END;
    
    FUNCTION File_Exists(
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN BOOLEAN        
    As
        v_FileLocation BFILE := NULL;
        v_FileExists BOOLEAN;
    BEGIN
        v_FileLocation := BFILENAME(p_FolderName, p_FileName);
        v_FileExists := DBMS_LOB.FILEEXISTS(v_FileLocation) = 1;
        return v_FileExists;
    END;

    FUNCTION File_Backup(
        p_SrcFileName IN FileNameType,
        p_SrcFolderName IN FolderNameType,
        p_BackupFileName IN FileNameType,
        p_BackupSrcFolderName IN FolderNameType
    )
    RETURN BOOLEAN
    AS
        v_WasSuccess BOOLEAN := FALSE;
    BEGIN
        if(File_Exists(p_SrcFileName, p_SrcFolderName)) then
            UTL_FILE.FRENAME(p_SrcFolderName, p_SrcFileName, p_BackupSrcFolderName, p_BackupFileName, TRUE);
            v_WasSuccess := TRUE;
        end if;
    
        return v_WasSuccess;
    END;
    PROCEDURE AppendClob
    (    
        p_file    UTL_FILE.FILE_TYPE,
        p_clob    CLOB,
        p_bufferSize BINARY_INTEGER DEFAULT 32767
        
    )
    as
        v_bufferSize BINARY_INTEGER := p_bufferSize;
        v_pos     INTEGER := 1;
        v_length number default nvl(dbms_lob.getlength(p_clob), 0);
    BEGIN
    if utl_file.is_open(p_file) then
        
        while(v_pos < v_length)   
        LOOP            
            UTL_FILE.put(p_file, dbms_lob.substr(p_clob, p_bufferSize, v_pos));
            UTL_FILE.FFLUSH(p_file);            
            v_pos := v_pos + v_bufferSize;
        END LOOP; 
        UTL_FILE.NEW_LINE(p_file);      
    end if;
    EXCEPTION
      WHEN OTHERS THEN    
        RAISE;                
    END; 
    
    FUNCTION DATE_PARAMETERIZE_FILE_NAME
    (
        p_FileName IN FileNameType,
        p_ASOF_DT IN DATE
    )
    RETURN FileNameType
    AS
        v_ToReturn FileNameType;
        v_Temp varchar(100);
    BEGIN
        if(TRIM(p_FileName) is not NULL) then
            v_ToReturn := Trim(p_FileName);
            if(p_ASOF_DT is NOT NULL) then
                v_Temp := Extract(YEAR from p_ASOF_DT);
                v_ToReturn := REPLACE(v_ToReturn, 'YYYY', v_Temp);
                
                v_Temp := LPAD(Extract(Month from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MM', v_Temp);
                
                v_Temp := LPAD(Extract(Day from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'DD', v_Temp);
                
                v_Temp := LPAD(Extract(HOUR from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'HH', v_Temp);                
                
                v_Temp := LPAD(Extract(Minute from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MI', v_Temp);                
                
                v_Temp := LPAD(Extract(Second from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'SS', v_Temp);
            end if;
        end if;
        return v_ToReturn;
    END;
           
END FILES;


CREATE OR REPLACE PACKAGE HRADMIN.BUG_REPRODUCE AS
    Type PL_TBL Is Table of VARCHAR2(4000);
    
    FUNCTION ERROR_ON_COMPILE(
        p_Rows IN FILES2.TFileCursor
    )
    RETURN PL_TBL PIPELINED;
    PROCEDURE ENTRY_POINT;
    
    
END BUG_REPRODUCE;

CREATE OR REPLACE PACKAGE BODY HRADMIN.BUG_REPRODUCE AS
----------------------------------------------------------------------
FUNCTION ERROR_ON_COMPILE(
    p_Rows IN FILES.TFileCursor 
)
RETURN PL_TBL PIPELINED
IS
    v_Str varchar2(4000);
    v_Itr FILES.TRow;
BEGIN
    Loop
        fetch p_Rows into v_Itr;
        exit when p_Rows%notfound;
        v_Str := v_Itr.ROWVAL;
        PIPE ROW(v_Str);   
    END LOOP;    
    return;
END;
----------------------------------------------------------------------
PROCEDURE ENTRY_POINT
IS
    v_Rows_In_File INTEGER;
    v_FileName FILES.FileNameType;
    v_FolderName FILES.FolderNameType;        
BEGIN
    -- in oracle create directory with the following name
    v_FolderName := 'SUBS';
    -- in direction that above directoy is mapped to create file with following name
    -- Note - each line in the file should have a length of less than 4000
    v_FileName := 'small.txt';        
    
    DBMS_OUTPUT.PUT_LINE('this one works');
    select count(*) into v_Rows_In_File
    from TABLE(FILES.LOAD(v_FileName, v_FolderName));
    DBMS_OUTPUT.PUT_LINE('this one works: answer' || to_char(v_Rows_In_File));
    
    DBMS_OUTPUT.PUT_LINE('this one also works');        
    select count(*) into v_Rows_In_File
    from TABLE(ERROR_ON_COMPILE(CURSOR(select * from TABLE(FILES.LOAD(v_FileName, v_FolderName)))));
    DBMS_OUTPUT.PUT_LINE('this one also works : answer ' || to_char(v_Rows_In_File));
    --*/
END;
----------------------------------------------------------------------
END BUG_REPRODUCE;

March 01, 2019 - 1:13 am UTC

Thanks for sharing it back to us - helps the community.

correction to typo in prior review

Brian Hanks, February 28, 2019 - 5:14 pm UTC

correct version with out the typo

CREATE OR REPLACE PACKAGE FILES AS
/******************************************************************************
   NAME:       FILE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        6/6/2017      bhanks       1. Created this package.
   1.1        7/12/2018     bhanks       2. add delete method
   1.2        2/28/2019     bhanks       3. add ref cursor for TFile type
******************************************************************************/

TYPE TRow is Record(RowNbr INTEGER, RowVal VARCHAR2(32767));
Type TFile is Table of TRow;
SubType FileNameType is varchar2(500);
SubType FolderNameType is varchar2(500);
TYPE TFileCursor is REF CURSOR RETURN TRow;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN TFile pipelined;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType,
    p_MaxLineLength IN INTEGER
)
RETURN TFile pipelined;

FUNCTION DATE_PARAMETERIZE_FILE_NAME
(
    p_FileName IN FileNameType,
    p_ASOF_DT IN DATE
)
RETURN FileNameType;

FUNCTION File_Backup(
    p_SrcFileName IN FileNameType,
    p_SrcFolderName IN FolderNameType,
    p_BackupFileName IN FileNameType,
    p_BackupSrcFolderName IN FolderNameType
)
RETURN BOOLEAN;

FUNCTION File_Exists(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN BOOLEAN;

PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
);

PROCEDURE AppendClob
(    
    p_file    UTL_FILE.FILE_TYPE,
    p_clob    CLOB,
    p_bufferSize BINARY_INTEGER DEFAULT 32767
    
);

END FILES;

CREATE OR REPLACE PACKAGE BODY FILES AS  
   
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN TFile pipelined
    IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(4000);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R');
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                v_RowNbr := v_RowNbr + 1;
                UTL_FILE.GET_LINE(v_File,v_Line);
                v_Row.RowNbr := v_RowNbr;
                v_Row.RowVal := v_Line;
                PIPE ROW(v_Row);
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType,
        p_MaxLineLength IN INTEGER
    )
    RETURN TFile pipelined IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(32767);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R',32767);
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                UTL_FILE.GET_LINE(v_File,v_Line,32767);
                if(Length(v_Line) <= p_MaxLineLength) then
                    v_RowNbr := v_RowNbr + 1;
                
                    v_Row.RowNbr := v_RowNbr;
                    v_Row.RowVal := v_Line;
                    PIPE ROW(v_Row);
                end if;
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
    )
    IS
    BEGIN
        IF(File_Exists(p_FileName, p_FolderName)) then
            UTL_FILE.FREMOVE(p_FolderName, p_FileName);            
        end if;
    END;
    
    FUNCTION File_Exists(
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN BOOLEAN        
    As
        v_FileLocation BFILE := NULL;
        v_FileExists BOOLEAN;
    BEGIN
        v_FileLocation := BFILENAME(p_FolderName, p_FileName);
        v_FileExists := DBMS_LOB.FILEEXISTS(v_FileLocation) = 1;
        return v_FileExists;
    END;

    FUNCTION File_Backup(
        p_SrcFileName IN FileNameType,
        p_SrcFolderName IN FolderNameType,
        p_BackupFileName IN FileNameType,
        p_BackupSrcFolderName IN FolderNameType
    )
    RETURN BOOLEAN
    AS
        v_WasSuccess BOOLEAN := FALSE;
    BEGIN
        if(File_Exists(p_SrcFileName, p_SrcFolderName)) then
            UTL_FILE.FRENAME(p_SrcFolderName, p_SrcFileName, p_BackupSrcFolderName, p_BackupFileName, TRUE);
            v_WasSuccess := TRUE;
        end if;
    
        return v_WasSuccess;
    END;
    PROCEDURE AppendClob
    (    
        p_file    UTL_FILE.FILE_TYPE,
        p_clob    CLOB,
        p_bufferSize BINARY_INTEGER DEFAULT 32767
        
    )
    as
        v_bufferSize BINARY_INTEGER := p_bufferSize;
        v_pos     INTEGER := 1;
        v_length number default nvl(dbms_lob.getlength(p_clob), 0);
    BEGIN
    if utl_file.is_open(p_file) then
        
        while(v_pos < v_length)   
        LOOP            
            UTL_FILE.put(p_file, dbms_lob.substr(p_clob, p_bufferSize, v_pos));
            UTL_FILE.FFLUSH(p_file);            
            v_pos := v_pos + v_bufferSize;
        END LOOP; 
        UTL_FILE.NEW_LINE(p_file);      
    end if;
    EXCEPTION
      WHEN OTHERS THEN    
        RAISE;                
    END; 
    
    FUNCTION DATE_PARAMETERIZE_FILE_NAME
    (
        p_FileName IN FileNameType,
        p_ASOF_DT IN DATE
    )
    RETURN FileNameType
    AS
        v_ToReturn FileNameType;
        v_Temp varchar(100);
    BEGIN
        if(TRIM(p_FileName) is not NULL) then
            v_ToReturn := Trim(p_FileName);
            if(p_ASOF_DT is NOT NULL) then
                v_Temp := Extract(YEAR from p_ASOF_DT);
                v_ToReturn := REPLACE(v_ToReturn, 'YYYY', v_Temp);
                
                v_Temp := LPAD(Extract(Month from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MM', v_Temp);
                
                v_Temp := LPAD(Extract(Day from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'DD', v_Temp);
                
                v_Temp := LPAD(Extract(HOUR from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'HH', v_Temp);                
                
                v_Temp := LPAD(Extract(Minute from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MI', v_Temp);                
                
                v_Temp := LPAD(Extract(Second from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'SS', v_Temp);
            end if;
        end if;
        return v_ToReturn;
    END;
           
END FILES;


CREATE OR REPLACE PACKAGE HRADMIN.BUG_REPRODUCE AS
    Type PL_TBL Is Table of VARCHAR2(4000);
    
    FUNCTION ERROR_ON_COMPILE(
        p_Rows IN FILES.TFileCursor
    )
    RETURN PL_TBL PIPELINED;
    PROCEDURE ENTRY_POINT;
    
    
END BUG_REPRODUCE;

CREATE OR REPLACE PACKAGE BODY HRADMIN.BUG_REPRODUCE AS
----------------------------------------------------------------------
FUNCTION ERROR_ON_COMPILE(
    p_Rows IN FILES.TFileCursor 
)
RETURN PL_TBL PIPELINED
IS
    v_Str varchar2(4000);
    v_Itr FILES.TRow;
BEGIN
    Loop
        fetch p_Rows into v_Itr;
        exit when p_Rows%notfound;
        v_Str := v_Itr.ROWVAL;
        PIPE ROW(v_Str);   
    END LOOP;    
    return;
END;
----------------------------------------------------------------------
PROCEDURE ENTRY_POINT
IS
    v_Rows_In_File INTEGER;
    v_FileName FILES.FileNameType;
    v_FolderName FILES.FolderNameType;        
BEGIN
    -- in oracle create directory with the following name
    v_FolderName := 'SUBS';
    -- in direction that above directoy is mapped to create file with following name
    -- Note - each line in the file should have a length of less than 4000
    v_FileName := 'small.txt';        
    
    DBMS_OUTPUT.PUT_LINE('this one works');
    select count(*) into v_Rows_In_File
    from TABLE(FILES.LOAD(v_FileName, v_FolderName));
    DBMS_OUTPUT.PUT_LINE('this one works: answer' || to_char(v_Rows_In_File));
    
    DBMS_OUTPUT.PUT_LINE('this one also works');        
    select count(*) into v_Rows_In_File
    from TABLE(ERROR_ON_COMPILE(CURSOR(select * from TABLE(FILES.LOAD(v_FileName, v_FolderName)))));
    DBMS_OUTPUT.PUT_LINE('this one also works : answer ' || to_char(v_Rows_In_File));
    --*/
END;
----------------------------------------------------------------------
END BUG_REPRODUCE;

thirds times a charm

Brian Hanks, February 28, 2019 - 5:17 pm UTC

removed schema names from packages defintion so will compile in any schema

CREATE OR REPLACE PACKAGE FILES AS
/******************************************************************************
   NAME:       FILE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        6/6/2017      bhanks       1. Created this package.
   1.1        7/12/2018     bhanks       2. add delete method
   1.2        2/28/2019     bhanks       3. add ref cursor for TFile type
******************************************************************************/

TYPE TRow is Record(RowNbr INTEGER, RowVal VARCHAR2(32767));
Type TFile is Table of TRow;
SubType FileNameType is varchar2(500);
SubType FolderNameType is varchar2(500);
TYPE TFileCursor is REF CURSOR RETURN TRow;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN TFile pipelined;

FUNCTION Load (
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType,
    p_MaxLineLength IN INTEGER
)
RETURN TFile pipelined;

FUNCTION DATE_PARAMETERIZE_FILE_NAME
(
    p_FileName IN FileNameType,
    p_ASOF_DT IN DATE
)
RETURN FileNameType;

FUNCTION File_Backup(
    p_SrcFileName IN FileNameType,
    p_SrcFolderName IN FolderNameType,
    p_BackupFileName IN FileNameType,
    p_BackupSrcFolderName IN FolderNameType
)
RETURN BOOLEAN;

FUNCTION File_Exists(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
)
RETURN BOOLEAN;

PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
);

PROCEDURE AppendClob
(    
    p_file    UTL_FILE.FILE_TYPE,
    p_clob    CLOB,
    p_bufferSize BINARY_INTEGER DEFAULT 32767
    
);

END FILES;

CREATE OR REPLACE PACKAGE BODY FILES AS  
   
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN TFile pipelined
    IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(4000);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R');
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                v_RowNbr := v_RowNbr + 1;
                UTL_FILE.GET_LINE(v_File,v_Line);
                v_Row.RowNbr := v_RowNbr;
                v_Row.RowVal := v_Line;
                PIPE ROW(v_Row);
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    FUNCTION Load (
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType,
        p_MaxLineLength IN INTEGER
    )
    RETURN TFile pipelined IS
    
    v_File      UTL_FILE.FILE_TYPE;
    v_RowNbr    Integer;
    v_Line      Varchar2(32767);
    v_Row       TRow;

    BEGIN
        v_File := UTL_FILE.FOPEN(p_FolderName, p_FileName,'R',32767);
        v_RowNbr := 0;
        IF UTL_FILE.IS_OPEN(v_File) THEN
            Loop
                UTL_FILE.GET_LINE(v_File,v_Line,32767);
                if(Length(v_Line) <= p_MaxLineLength) then
                    v_RowNbr := v_RowNbr + 1;
                
                    v_Row.RowNbr := v_RowNbr;
                    v_Row.RowVal := v_Line;
                    PIPE ROW(v_Row);
                end if;
            END Loop;               
        END IF;
        return;
    EXCEPTION
        when NO_DATA_FOUND then
            UTL_FILE.FCLOSE (v_File);
        return;
    END;
    PROCEDURE FILE_DELETE(
    p_FileName IN FileNameType,
    p_FolderName IN FolderNameType
    )
    IS
    BEGIN
        IF(File_Exists(p_FileName, p_FolderName)) then
            UTL_FILE.FREMOVE(p_FolderName, p_FileName);            
        end if;
    END;
    
    FUNCTION File_Exists(
        p_FileName IN FileNameType,
        p_FolderName IN FolderNameType
    )
    RETURN BOOLEAN        
    As
        v_FileLocation BFILE := NULL;
        v_FileExists BOOLEAN;
    BEGIN
        v_FileLocation := BFILENAME(p_FolderName, p_FileName);
        v_FileExists := DBMS_LOB.FILEEXISTS(v_FileLocation) = 1;
        return v_FileExists;
    END;

    FUNCTION File_Backup(
        p_SrcFileName IN FileNameType,
        p_SrcFolderName IN FolderNameType,
        p_BackupFileName IN FileNameType,
        p_BackupSrcFolderName IN FolderNameType
    )
    RETURN BOOLEAN
    AS
        v_WasSuccess BOOLEAN := FALSE;
    BEGIN
        if(File_Exists(p_SrcFileName, p_SrcFolderName)) then
            UTL_FILE.FRENAME(p_SrcFolderName, p_SrcFileName, p_BackupSrcFolderName, p_BackupFileName, TRUE);
            v_WasSuccess := TRUE;
        end if;
    
        return v_WasSuccess;
    END;
    PROCEDURE AppendClob
    (    
        p_file    UTL_FILE.FILE_TYPE,
        p_clob    CLOB,
        p_bufferSize BINARY_INTEGER DEFAULT 32767
        
    )
    as
        v_bufferSize BINARY_INTEGER := p_bufferSize;
        v_pos     INTEGER := 1;
        v_length number default nvl(dbms_lob.getlength(p_clob), 0);
    BEGIN
    if utl_file.is_open(p_file) then
        
        while(v_pos < v_length)   
        LOOP            
            UTL_FILE.put(p_file, dbms_lob.substr(p_clob, p_bufferSize, v_pos));
            UTL_FILE.FFLUSH(p_file);            
            v_pos := v_pos + v_bufferSize;
        END LOOP; 
        UTL_FILE.NEW_LINE(p_file);      
    end if;
    EXCEPTION
      WHEN OTHERS THEN    
        RAISE;                
    END; 
    
    FUNCTION DATE_PARAMETERIZE_FILE_NAME
    (
        p_FileName IN FileNameType,
        p_ASOF_DT IN DATE
    )
    RETURN FileNameType
    AS
        v_ToReturn FileNameType;
        v_Temp varchar(100);
    BEGIN
        if(TRIM(p_FileName) is not NULL) then
            v_ToReturn := Trim(p_FileName);
            if(p_ASOF_DT is NOT NULL) then
                v_Temp := Extract(YEAR from p_ASOF_DT);
                v_ToReturn := REPLACE(v_ToReturn, 'YYYY', v_Temp);
                
                v_Temp := LPAD(Extract(Month from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MM', v_Temp);
                
                v_Temp := LPAD(Extract(Day from p_ASOF_DT), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'DD', v_Temp);
                
                v_Temp := LPAD(Extract(HOUR from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'HH', v_Temp);                
                
                v_Temp := LPAD(Extract(Minute from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'MI', v_Temp);                
                
                v_Temp := LPAD(Extract(Second from to_TIMESTAMP(p_ASOF_DT)), 2, '0');
                v_ToReturn := REPLACE(v_ToReturn, 'SS', v_Temp);
            end if;
        end if;
        return v_ToReturn;
    END;
           
END FILES;


CREATE OR REPLACE PACKAGE BUG_REPRODUCE AS
    Type PL_TBL Is Table of VARCHAR2(4000);
    
    FUNCTION ERROR_ON_COMPILE(
        p_Rows IN FILES.TFileCursor
    )
    RETURN PL_TBL PIPELINED;
    PROCEDURE ENTRY_POINT;
    
    
END BUG_REPRODUCE;

CREATE OR REPLACE PACKAGE BODY BUG_REPRODUCE AS
----------------------------------------------------------------------
FUNCTION ERROR_ON_COMPILE(
    p_Rows IN FILES.TFileCursor 
)
RETURN PL_TBL PIPELINED
IS
    v_Str varchar2(4000);
    v_Itr FILES.TRow;
BEGIN
    Loop
        fetch p_Rows into v_Itr;
        exit when p_Rows%notfound;
        v_Str := v_Itr.ROWVAL;
        PIPE ROW(v_Str);   
    END LOOP;    
    return;
END;
----------------------------------------------------------------------
PROCEDURE ENTRY_POINT
IS
    v_Rows_In_File INTEGER;
    v_FileName FILES.FileNameType;
    v_FolderName FILES.FolderNameType;        
BEGIN
    -- in oracle create directory with the following name
    v_FolderName := 'SUBS';
    -- in direction that above directoy is mapped to create file with following name
    -- Note - each line in the file should have a length of less than 4000
    v_FileName := 'small.txt';        
    
    DBMS_OUTPUT.PUT_LINE('this one works');
    select count(*) into v_Rows_In_File
    from TABLE(FILES.LOAD(v_FileName, v_FolderName));
    DBMS_OUTPUT.PUT_LINE('this one works: answer' || to_char(v_Rows_In_File));
    
    DBMS_OUTPUT.PUT_LINE('this one also works');        
    select count(*) into v_Rows_In_File
    from TABLE(ERROR_ON_COMPILE(CURSOR(select * from TABLE(FILES.LOAD(v_FileName, v_FolderName)))));
    DBMS_OUTPUT.PUT_LINE('this one also works : answer ' || to_char(v_Rows_In_File));
    --*/
END;
----------------------------------------------------------------------
END BUG_REPRODUCE;

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

ouroboros

OS dependency?

Follow up to ouroboros comment

correction to typo in prior review

thirds times a charm

More to Explore

PL/SQL demos

PL/SQL docs

getting error ( ORA-06553: PLS-306: wrong number or types of arguments in call to) after migrating code from oracle 11.2.0.3.0 to 12 c

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

ouroboros

OS dependency?

Follow up to ouroboros comment

correction to typo in prior review

thirds times a charm

More to Explore

PL/SQL demos

PL/SQL docs