Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    4

    Unanswered: extract blob type

    I have a database table with blob datatype. I want to extract this blob datatype without using UTL file package. Is there any way to achieve this ?

    The reason I can's use UTL_FILE is as follows-

    I ran a sample code on my UNIX box to check that database I am accessing is on same server. The error suggests that Database is not on Unix box we are accessing. We have no access to the box where database is hosted so cant't change the permissions of directories there.


    Code:
    create directory tpffilesdir1 as '/data/download/amlr' ;
    Sample Code
    Code:
    declare
      f utl_file.file_type;
    begin
    
      f := utl_file.fopen('TPFFILESDIR1', 'Test.txt', 'w',32767);
      utl_file.put(f,'hello');
      utl_file.fclose(f);
    end;
    /
    Error encountered

    Code:
    SQL> @test1.sql
    declare
    *
    ERROR at line 1:
    ORA-29289: directory access denied
    ORA-06512: at "SYS.UTL_FILE", line 33
    ORA-06512: at "SYS.UTL_FILE", line 436
    ORA-06512: at line 4

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALWAYS
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    or post Oracle version to 4 decimal places


    One possible way would be to use exp or expdp to export the whole table to your system

    In my opinion, your test is inconclusive.

    select host_name from v$instance;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Check that directory object was successfully created:
    Code:
    select * from all_directories
    Check the permissions on the directory object created:
    Code:
    select * from all_tab_privs where table_name = 'tpffilesdir1'
    The user who created this directory should have read/write permissions because by default, you get the READ WRITE privileges on the directory object created.

    Also from the above list, you could try a directory where it shows you have READ WRITE permissions.

    If you want to assign a READ WRITE privilege to another user you can GRANT the necessary privileges using 'Grant', for example:
    Code:
    GRANT  READ ON DIRECTORY tpffilesdir1 TO PUBLIC;
    Make sure that the OS user that the Oracle binaries(i.e. utl_file) run as has read-write access to this directory.

    hth

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •