Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to Convert the content in BLOB field into a PDF file...

jampala - oracleDec 5 2010 — edited Dec 6 2010
Hi,
I am having PDF files stored in BLOB column of a table in Oracle Database (11G R2).
I want to retrieve the files back and store them in hard disk. I am successful in storing the content as a file with '.doc' but if I store the file as '.pdf', adobe fails to open the file with error
Adobe Reader could not open file 'xxx.pdf' because it is either not a supported file type or because the file has been damaged (for example it was sent as an email attachment and wasn't correctly decoded)

I am using following example code to achieve my goal...

Declare
b blob;
c clob;
buffer VARCHAR2(32767);
buffer_size CONSTANT BINARY_INTEGER := 32767;
amount BINARY_INTEGER;
offset NUMBER(38);
file_handle UTL_FILE.FILE_TYPE;
begin
select blob_data into b from blobdata where id=1;
c := blob2clob(b);
file_handle := UTL_FILE.FOPEN('BLOB2FILE','my_file.pdf','w',buffer_size);
amount := buffer_size;
offset := 1;
WHILE amount >= buffer_size
LOOP
DBMS_LOB.READ(c,amount,offset,buffer);
-- buffer:=replace(buffer,chr(13),'');
offset := offset + amount;
UTL_FILE.PUT(file_handle,buffer);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
end;

create or replace FUNCTION BLOB2CLOB ( p_blob IN BLOB ) RETURN CLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
/*****************************************************************************************
|| Purpose : To Convert a BLOB File to CLOB File
|| INPUT : BLOB File
|| OUTPUT : CLOB File
|| History: MB V5.0 24.09.2007 RCMS00318572 Initial version
*****************************************************************************************/
ln_file_check NUMBER;
ln_file_size NUMBER;
v_text_file CLOB;
v_binary_file BLOB;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_warning INTEGER;
lv_data CLOB;
ln_length NUMBER;
csid VARCHAR2(100) := DBMS_LOB.DEFAULT_CSID;
V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
BEGIN
DBMS_LOB.createtemporary (v_text_file, TRUE);
SELECT dbms_lob.getlength(p_blob) INTO ln_file_size FROM DUAL;
DBMS_LOB.converttoclob (v_text_file, p_blob, ln_file_size, v_dest_offset, v_src_offset, 0, v_lang_context, v_warning);
SELECT dbms_lob.getlength(v_text_file) INTO ln_length FROM DUAL;
RETURN v_text_file;
END;

Comments

Brian Jeffries-Oracle
Hi Laurent,

To create the unit test repository, the repository user needs:
CONNECT, RESOURCE, CREATE VIEW

The "SYS" user can be any user that can connect as SYSDBA
this is used to execute the following:

grant select on dba_roles to repository_user;
grant select on dba_role_privs to repository_user;

create role UT_REPO_ADMINISTRATOR;
create role UT_REPO_USER;
grant create public synonym,drop public synonym to UT_REPO_ADMINISTRATOR;
grant select on dba_role_privs to UT_REPO_USER;
grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_USER;
grant select on dba_tab_privs to UT_REPO_USER; --3.1
grant select on dba_tab_privs to UT_REPO_ADMINISTATOR; --3.1
grant execute on dbms_lock to UT_REPO_USER; --3.1
grant execute on dbms_lock to UT_REPO_ADMINISTATOR; --3.1
grant ut_repo_user to UT_REPO_ADMINISTRATOR with admin option;
grant UT_REPO_ADMINISTRATOR to repository_user with admin option;

Alternatively, your dba could do the previous block (create role ... grant UT_REPO_ADMIN...) for repository_user and then you could create one without being prompted for the "SYS" connection.

Brian
SQL Developer Team.

Edited by: bjeffrie on Mar 1, 2012 11:55 AM to update with new privilege requirements
1 person found this helpful
Laurent Schneider
Hi Brian,
I will ask my dba to create the user above.

I will update this thread later if needed

Thank you for your prompt answer,
Laurent
Laurent Schneider
but create public synonym is quite a powerful privilege to give... Can you tell me why is this needed and which synonyms will be created?
Brian Jeffries-Oracle
General answer is that create public synonym is only used in the case of setting a repository as shared. Doing so creates public synonyms for the unit test tables, views, and copy procedure.

Going through final code to see (& test) what the minimum requirements are for using individual/private repositories and will get back to you.

Brian Jeffries
SQL Developer Team
Brian Jeffries-Oracle
Answer
Hi Laurent,

Here is the minimum required to get past the internal checks and be able to use unit testing.

Once your DBA does this:

-- RUN ONCE TO SET UP REQUIRED UT ROLES WITH MINIMUM PRIVILEGES:

-- DROP ROLE UT_REPO_USER ;
-- DROP ROLE UT_REPO_ADMINISTRATOR ;
-- DROP USER MIN_PRIV_UT_REPO WITH CASCADE;

CREATE ROLE UT_REPO_USER ;
GRANT SELECT ON DBA_TAB_PRIVS TO UT_REPO_USER ; -- 3.1
GRANT EXECUTE ON DBMS_LOCK TO UT_REPO_USER ; -- 3.1
CREATE ROLE UT_REPO_ADMINISTRATOR ;
GRANT UT_REPO_USER TO UT_REPO_ADMINISTRATOR ; --3.1

-- EDIT/RUN FOR EACH USER/UNIT TEST REPOSITORY DESIRED:
-- Note: Replace MIN_PRIV_UT_REPO, USERS, TEMP as desired for your situation

CREATE USER MIN_PRIV_UT_REPO IDENTIFIED BY MIN_PRIV_UT_REPO
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT RESOURCE TO MIN_PRIV_UT_REPO ;
GRANT CONNECT TO MIN_PRIV_UT_REPO ;
GRANT CREATE VIEW TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLES TO MIN_PRIV_UT_REPO ;
GRANT SELECT ON DBA_ROLE_PRIVS TO MIN_PRIV_UT_REPO ;
GRANT UT_REPO_ADMINISTRATOR TO MIN_PRIV_UT_REPO ;


Then in to SQLDeveloper, select Tools->Unit Test->Select Current Repository, create/select a connection for MIN_PRIV_UT_REPO, and press "Yes" when asked if you want to create one.

Note that with the UT roles set up 'empty' like this, the menu options for managing a shared repository will be active and appear to work, but won't really do anything

Brian Jeffries
SQL Developer Team

Edited by: bjeffrie on Dec 22, 2009 3:08 PM

Edited by: bjeffrie on Dec 22, 2009 4:01 PM

Edited by: bjeffrie on Mar 1, 2012 11:43 AM to update with new privilege requirements for 3.1
Marked as Answer by Laurent Schneider · Sep 27 2020
Laurent Schneider
2 weeks later, I go the permission! I suppose you could optimize this process by removing the need of a role for dedicated repository. Or maybe create the role as a default role in Oracle 12...
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 3 2011
Added on Dec 5 2010
5 comments
22,670 views