title: ORACLE BLOB to FILE
body: <p>I am writing some pl/sql to generate pdf reports that are stored as blobs in an oracle table. I need to loop through this table which has a column for filename and blob and write the blob to the OS as a file with the corresponding filename in the table. I pretty much have completed this code but am running into a snag:</p>
  
  <pre><code>ORA-06550: line 13, column 59:
  PL/SQL: ORA-00904: "SIMS_PROD"."PUBLISH_RPT_NEW"."RPT_FILE_NAME": invalid identifier
  ORA-06550: line 13, column 12:
  PL/SQL: SQL Statement ignored
  06550. 00000 -  "line %s, column %s:\n%s"
  Cause:    Usually a PL/SQL compilation error.
  Action:
  </code></pre>
  
  <p>I did read the post on the site: <a href="https://stackoverflow.com/questions/6332032/how-can-i-extract-files-from-an-oracle-blob-field">How can I extract files from an Oracle BLOB field?</a> - however - this is only for one file - my table contains hundreds of rows each that has a blob and associated filename - its the looping through this table thats giving me grief.</p>
  
  <p>I need to prefix the schema name, table and column explicitly since I am logged in as a DBA user and not as the owner of the schema itself. Here is my code - what am I missing here or doing wrong. Thanks in advance for any help from the community - its much appreciated.</p>
  
  <pre><code>DECLARE 
  t_blob BLOB; 
  t_len NUMBER; 
  t_file_name VARCHAR2(100); 
  t_output utl_file.file_type; 
  t_totalsize NUMBER; 
  t_position NUMBER := 1; 
  t_chucklen NUMBER := 4096; 
  t_chuck RAW(4096); 
  t_remain NUMBER; 
  
  BEGIN
    FOR X IN (SELECT SIMS_PROD.publish_rpt_new.RPT_FILE_NAME,     SIMS_PROD.publish_rpt_new.RPT_CONTENTS FROM SIMS_PROD.PUBLISH_RPT)
  
    LOOP
      -- Get length of blob 
      SELECT dbms_lob.Getlength (SIMS_PROD.publish_rpt_new.RPT_CONTENTS),     SIMS_PROD.publish_rpt_new.RPT_FILE_NAME INTO t_totalsize, t_file_name FROM     SIMS_PROD.publish_rpt_new;
      t_remain := t_totalsize; 
  
      -- The directory TEMPDIR should exist before executing 
      t_output := utl_file.Fopen ('PDF_REP', t_file_name, 'wb', 32760); 
  
      -- Get BLOB 
      SELECT SIMS_PROD.publish_rpt_new.RPT_CONTENTS INTO t_blob FROM       SIMS_PROD.publish_rpt_new;
  
      -- Retrieving BLOB  
      WHILE t_position &lt; t_totalsize 
        LOOP 
          dbms_lob.READ (t_blob, t_chucklen, t_position, t_chuck); 
          utl_file.Put_raw (t_output, t_chuck); 
          utl_file.Fflush (t_output); 
          t_position := t_position + t_chucklen; 
          t_remain := t_remain - t_chucklen; 
  
          IF t_remain &lt; 4096 THEN t_chucklen := t_remain; 
          END IF; 
        END LOOP; 
  
      END LOOP;
  END;
  </code></pre>