CODEWITHSUNDEEP
oracle-databaseplsql
uditjain
uditjain

Reputation: 15

Implement File Upload with PL/SQL

I am using the below code to create a ORDS service which will upload a file to a DBA directory. Could anyone assist me to make the following work. I am getting an error trying to compile this.

    DECLARE
      l_file      UTL_FILE.FILE_TYPE;
      l_buffer    RAW(32767);
      l_amount    BINARY_INTEGER := 32767;
      l_pos       INTEGER := 1;
      l_blob      BLOB := :body;
--      l_file_name VARCHAR2 (100) := :fileName;
      l_blob_len  INTEGER;
    
      l_reqid     NUMBER;
    
    BEGIN
    
      l_blob_len := DBMS_LOB.getlength(l_blob);
    
      -- Open the destination file.
      l_file := UTL_FILE.fopen('XX_FILE','XX_FILE_NAME.csv','wb', 32767);
    
      -- Read chunks of the BLOB and write them to the file
      -- until complete.
      WHILE l_pos <= l_blob_len LOOP
        DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
        UTL_FILE.put_raw(l_file, l_buffer, TRUE);
        l_pos := l_pos + l_amount;
      END LOOP;
    
      -- Close the file.
      UTL_FILE.fclose(l_file);
    
      HTP.P('File Upload Success');  
    
    EXCEPTION
      WHEN OTHERS THEN
        -- Close the file if something goes wrong.
        IF UTL_FILE.is_open(l_file) THEN
          UTL_FILE.fclose(l_file);
        END IF;
        HTP.P('Error: ' || SQLERRM);
    END;

Error report - ORA-06550: line 6, column 24: PLS-00382: expression is of wrong type ORA-06550: line 0, column 1: PL/SQL: Compilation unit analysis terminated 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Upvotes: 0

Views: 217

Answers (2)

uditjain
uditjain

Reputation: 15

Thought of posting how I resolved the above. The issue here is that whenever you try to click run statement or run script in SQL Developer, it will throw an error as :body is a standard implicit parameter in ORDS used to accept any object like a file/image etc and pass it to the DB via the service. Best way to handle this is to compile the code and not execute it. More about implicit params here:

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/implicit-parameters.html#GUID-B0BB1694-715C-4948-84A5-307EA3868063

Upvotes: 0

d r
d r

Reputation: 7786

The plsql block provided has the code to write an oracle BLOB into a file. The issue is in line 6 where you are declaring the variable named l_blob of type BLOB and assigning it to :body (whatever that :body is).

l_blob      BLOB := :body;

If your l_blob variable was a propper BLOB this code would create a file named "XX_FILE_NAME.csv" in the directory defined by Directory object in your db named "XX_FILE" if such a Directory object exist.

l_file := UTL_FILE.fopen('XX_FILE','XX_FILE_NAME.csv','wb', 32767);

To make it work, if you are trying to create file from BLOB, make sure that a propper BLOB is assigned to your l_blob variable and that you have a Directory object named "XX_FILE".
enter image description here

For ORDS file upload and download read the docs here.

Upvotes: 0

Related Questions