Search code examples
oracle-databasedbms-output

Error executing DBMS_OUTPUT.PUT_LINE in Oracle

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:

enter image description here

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:

enter image description here

Solution

  • 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.