3 Replies Latest reply: Oct 14, 2012 11:26 AM by yoonas RSS

    Directory Creation problem

    841691
      Hello


      i have installed Oracle Forms 6i client version:

      i have followed these steps for loading Image into Oracle Database

      Step 1 :
      SQL>create directory photo_dir as 'd:\photo_dir' ;
      Directory created.

      //here directory has to be created in client Machine, i don't have Server //

      Step 2 :
      SQL> create table temp_photo
      (
      ID NUMBER(3) NOT NULL,
      PHOTO_NAME VARCHAR2(50),
      PHOTO BLOB
      );
      Table created.

      Step 3 :
      SQL> create or replace PROCEDURE load_file (
      p_id number,
      p_photo_name in varchar2) IS
      src_file BFILE;
      dst_file BLOB;
      lgh_file BINARY_INTEGER;
      BEGIN
      src_file := bfilename('PHOTO_DIR', p_photo_name);
      -- insert a NULL record to lock
      INSERT INTO temp_photo
      (id, photo_name, photo)
      VALUES
      (p_id , p_photo_name ,EMPTY_BLOB())
      RETURNING photo INTO dst_file;
      -- lock record
      SELECT photo
      INTO dst_file
      FROM temp_photo
      WHERE id = p_id
      AND photo_name = p_photo_name
      FOR UPDATE;
      -- open the file
      dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
      -- determine length
      lgh_file := dbms_lob.getlength(src_file);
      -- read the file
      dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
      -- update the blob field
      UPDATE temp_photo
      SET photo = dst_file
      WHERE id = p_id
      AND photo_name = p_photo_name;
      -- close file
      dbms_lob.fileclose(src_file);
      END load_file;
      /


      Step 4 :
      SQL> execute load_file(1,'rdht.jpg') ;
      ERROR at line 1:
      ORA-22285: non-existent directory or file for FILEOPEN operation
      ORA-06512: at "SYS.DBMS_LOB", line 523
      ORA-06512: at "SCOTT.LOAD_FILE", line 23
      ORA-06512: at line 1

      this is the error how to rectify it

      directory has to be created in which privileges either Admin Privileges or scott user
      Admin user=system
      normal user=scott

      Kindly Help me

      regards
      venugopal reddy