Home » SQL & PL/SQL » SQL & PL/SQL » BLOBS --IMPORTANT
BLOBS --IMPORTANT [message #220218] Mon, 19 February 2007 10:27 Go to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
I am not able to download a blob.
It does open the text file sj_test1.txt but the file is blank.
PLEASE HELP; it is urgent.
Here is the procedure
CREATE OR REPLACE PROCEDURE APPS.xxca_download_cnote_info( p_file IN VARCHAR2)
AS
v_blob BLOB;
BEGIN

SELECT cnote_blob
INTO v_blob
FROM xxca_ach_summary
WHERE entity_no ='123';



OWA_UTIL.mime_header ('application/text', FALSE );
HTP.p ('Content-Length: ' || DBMS_LOB.getlength (v_blob));
HTP.p ('Content-Disposition: attachment; filename=' ||p_file);
OWA_UTIL.http_header_close;
wpg_docload.download_file(v_blob);

EXCEPTION
WHEN OTHERS
THEN
HTP.p ('File could not be found for ' || 'sj_test1.txt');
HTP.br;
HTP.p (SQLCODE || ' - ' || SQLERRM);
END xxca_download_cnote_info;
/

Thanks in advance.
Re: BLOBS --IMPORTANT [message #220219 is a reply to message #220218] Mon, 19 February 2007 10:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are assigning a BLOB to a mime type of application/txt. If you are truly trying to download a BLOB, then you should assign the corresponding mime type.
Re: BLOBS --IMPORTANT [message #220229 is a reply to message #220219] Mon, 19 February 2007 12:55 Go to previous messageGo to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
Thanks. Now after debugging that, I get the impression that the blob is not being stored correctly.
Is there a simpler way of inserting a blob content in a table.
My code is this:
CREATE OR REPLACE PROCEDURE APPS.Xxca_cnote_blob ( retcode OUT NUMBER)
AS
v_blob BLOB;
v_flob BFILE;
v_comm_name varchar2(30);
v_path_name varchar2(80);
v_cnote_fname varchar2(30);
v_test varchar2(5);
begin
dbms_output.put_line('TEsting process');
select entity_no,filename
into v_comm_name,v_cnote_fname
from xxca_cnote_temp_table;

select '/oracle/comn/admin/de_out/' into v_path_name from dual;
dbms_output.put_line('New is'|| v_path_name);
dbms_output.put_line('File is'|| v_cnote_fname);

begin
execute immediate 'create or replace directory ACH_CNOTE_FILES as ''||v_path_name||''';
end;
update xxca_ach_summary set cnote_blob= empty_blob()
where entity_no='123'
RETURN cnote_blob
INTO v_blob;


v_flob := BFILENAME ('ACH_CNOTE_FILES', v_cnote_fname);
DBMS_LOB.fileopen (v_flob, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (
v_blob,
v_flob,
DBMS_LOB.getlength (v_flob)
);
DBMS_LOB.fileclose (v_flob);
commit;
exception
when others then
retcode:=1;
end xxca_cnote_blob;
/

Thanks so much.
Re: BLOBS --IMPORTANT [message #220231 is a reply to message #220229] Mon, 19 February 2007 13:11 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You may want to check Inserting a BLOB Column using PL/SQL.
Re: BLOBS --IMPORTANT [message #220237 is a reply to message #220231] Mon, 19 February 2007 14:13 Go to previous messageGo to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
Thanks. I got it to work.
However one question, can I create or replace the directory by passing a value to a variable?
I do not want to hard code but fetch the value instead from a table .
So my syntax being --

begin
select entity_no,filename
into v_comm_name,v_cnote_fname
from xxca_cnote_temp_table;
select '/oracle/comn/admin/sft_out/'||v_comm_name into v_path_name from dual;

begin
execute immediate 'create or replace directory ACH_CNOTE_FILES as ''v_path_name''';
end;
This doesn't seem to work. If I hard code it ; it does work.
Please advise.

Thanks so much.
Re: BLOBS --IMPORTANT [message #220239 is a reply to message #220237] Mon, 19 February 2007 15:42 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Your statement should be:

execute immediate 'create or replace directory ACH_CNOTE_FILES as '||v_path_name;
Re: BLOBS --IMPORTANT [message #220243 is a reply to message #220239] Mon, 19 February 2007 16:00 Go to previous messageGo to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
I tried it but it does not work. the procedure compiles with no error but the directory is neither replaced nor created incase I choose a new name.

Thanks
Re: BLOBS --IMPORTANT [message #220245 is a reply to message #220243] Mon, 19 February 2007 16:29 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I forgot CREATE DIRECTORY requires single-quotes around the path. Assuming you have the appropriate privileges granted directly to you (if not, you may need to use INVOKER RIGHTS), this should work:

SQL> create or replace procedure test_dir (p_path varchar2) is
  2  begin
  3  execute immediate 'create or replace directory DIR1 as '||''''||p_path||'''';
  4  end;
  5  /

Procedure created.

SQL> exec test_dir ('c:\temp');

PL/SQL procedure successfully completed.

SQL>  select * from dba_directories where directory_name = 'DIR1';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------
SYS                            DIR1                           c:\temp
Re: BLOBS --IMPORTANT [message #220406 is a reply to message #220245] Tue, 20 February 2007 10:12 Go to previous message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
Thanks so much. I appreciate your help. It does work now.
Previous Topic: Basic update question
Next Topic: Merging BLOBs
Goto Forum:
  


Current Time: Mon Dec 05 05:13:11 CST 2016

Total time taken to generate the page: 0.10125 seconds