Error executing DBMS_OUTPUT.PUT_LINE in Oracle

Question

I am running DBMS_OUTPUT.PUT_LINE to send a message from a procedure and I am trying to display the debug information of what a type variable contains BFILE

create or replace PROCEDURE P_FILEUPLOAD_XML IS

v_dir       gzvcatg.gzvcatg_desc11%TYPE;
l_dir       VARCHAR2(35);
l_fil       VARCHAR2(30) := 'ES0000251446027471.xml'; 
l_loc       BFILE;

BEGIN

        l_loc := BFILENAME(v_dir,l_fil);

        DBMS_OUTPUT.PUT_LINE(l_loc); 

END;

At the moment of executing my procedure and waiting for a response from the log:

Anyone know why the error is due and how to correct it.

UPDATE:

Following the recommendation in the MT0 response making use of DBMS_LOB.READ, try the following:

create or replace PROCEDURE P_FILEUPLOAD_XML IS

    v_dir       gzvcatg.gzvcatg_desc11%TYPE;
    l_dir       VARCHAR2(35);
    l_fil       VARCHAR2(30) := 'ES0000251446027471.xml'; 
    l_loc       BFILE;

    BEGIN

            l_loc := BFILENAME(v_dir,l_fil);

            DBMS_LOB.READ(l_loc IN BFILE); 

    END;

But executing it generates the following error:

You can't "print" a BFILE. what are you trying to do? Unclear. You want to read the file and print it out? Read [this](https://docs.oracle.com/en/database/oracle/oracle-database/18/adlob/LOB-APIs-for-BFILE-operations.html#GUID-43A8979C-97AC-4F97-9B5C-F44E72FE64F4) — OldProgrammer, Mar 02 '20 at 20:57
YOu need to use the [LOB API function](https://docs.oracle.com/database/121/ARPLS/d_lob.htm#ARPLS66624) — OldProgrammer, Mar 02 '20 at 21:01
The variable `l_loc` is of data type `bfile`. That data type can't be converted to a string, to be printed with `put_line`. `bfile` is an object data type, made up of a file name and an internal alias for that file's location. So - what were you trying to print? — , Mar 02 '20 at 21:02
@mathguy How would it be the correct way to show what the `l_loc` variable contains in the log — John Doe, Mar 02 '20 at 21:05
@OldProgrammer Sorry, I mean showing what the `l_loc` variable in console contains — John Doe, Mar 02 '20 at 21:15

MT0 · Accepted Answer · 2020-03-02T21:10:46.957

1

Anyone know why the error is due

l_loc is a BFILE.

DBMS_OUTPUT.PUT_LINE( item IN VARCHAR2 ) takes a VARCHAR2 data type as its argument.

You cannot implicitly cast a BFILE to a VARCHAR2 so the procedure call raise an exception as it is the wrong type of argument to call the function with.

and how to correct it.

Read the file using DBMS_LOB.READ and use UTL_RAW.CAST_TO_VARCHAR2 to convert the RAW value you get from the LOB to a string so you can print it.

edited Mar 02 '20 at 21:10

answered Mar 02 '20 at 21:04

MT0

143,790
11
59
117

Some example of how to print in the log what the `BFILE` variable contains – John Doe Mar 02 '20 at 21:09
You can be more detailed with the function `DBMS_LOB.READ`, I don't know her and I don't understand the documentation. – John Doe Mar 02 '20 at 21:39
Check my question again, I am using `DBMS_LOB.READ` but it generates an error – John Doe Mar 02 '20 at 21:56

Error executing DBMS_OUTPUT.PUT_LINE in Oracle

1 Answers1