Table  : pdf_test (pdf_tk number(8), pdf_blob blob)

create or replace procedure write_pdf(tk in number,content in blob)
 as
 b_lob BLOB;
 len number;
 begin
 len:=5000;
 insert into pdf_test values(tk,empty_blob()) return pdf_blob into 
b_lob; 
DBMS_LOB.WRITE(b_lob,len,1,utl_raw.cast_to_varchar2(dbms_lob.substr(b_lob,32000,1)));
 commit;
 end;

i am passing my blob variable from powerbuilder while calling this procedure. and it fails and returns me -1 and saying procedure could  not executed. now how can i check from SQL prompt that my data has been entered in this table. i tried following ways but it fails.

SQL> execute write_pdf(1,'this is test');
BEGIN write_pdf(1,'this is test'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'WRITE_PDF'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> execute write_pdf(1,empty_blob());
BEGIN write_pdf(1,empty_blob()); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 700
ORA-06512: at "SHAHPRA.WRITE_PDF", line 9
ORA-06512: at line 1

it seems to me is my procedure do not understand the supplied parameter blob variable. actually i want to save my pdf document in database. i saw examples of that. but due to security/access reasons i can't use that create directory functions of oracle. 

how can i test from SQL prompt that my blob variable has inserted ?.

any help will be appreciated ? 

let me correct here that i used this way also

create or replace procedure write_pdf(tk in number,content in blob)
 as
 b_lob BLOB;
 len number;
 begin
 len:=5000;
 insert into pdf_test values(tk,empty_blob()) return pdf_blob into
b_lob;
DBMS_LOB.WRITE(b_lob,len,1,utl_raw.cast_to_varchar2(dbms_lob.substr(content,32000,1)));
 commit;
 end;
/

using CONTENT VARIABLE  as my lob loator which i m passing. but it gives me following error.

SQL> execute write_pdf(1,empty_blob());
BEGIN write_pdf(1,empty_blob()); END;

*
ERROR at line 1:
ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 664
ORA-06512: at "SHAHPRA.WRITE_PDF", line 9
ORA-06512: at line 1

thanks
nishant

 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> select dbms_lob.getlength(blob_content ) from DB2_TRACE_FILE      where id=1318627230195021;

DBMS_LOB.GETLENGTH(BLOB_CONTENT)
--------------------------------
                        53549591

SQL> desc DB2_TRACE_FILE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 ID                                        NOT NULL NUMBER
 BLOB_CONTENT                                       BLOB
 MIME_TYPE                                          VARCHAR2(4000)
 DT                                                 DATE

SQL> declare
  2        l_str1      varchar2(4000);
  3        l_str2      varchar2(4000);
  4        l_leftover  varchar2(4000);
  5        l_chunksize number := 3000;
  6        l_offset    number := 1;
  7        l_linebreak varchar2(2) := chr(13)||chr(10);
  8        l_length    number;
  9        p_blob blob;
 10    begin
 11
 12        select blob_content into p_blob from DB2_TRACE_FILE      where id=1318627230195021;
 13
 14        l_length := dbms_lob.getlength(p_blob);
 15        dbms_output.put_line(l_length);
 16
 17        while l_offset < l_length loop
 18
 19           l_str1 := l_leftover ||utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, l_chunksize, l_offset));
 20           l_leftover := null;
 21           l_str2 := l_str1;
 22
 23           while l_str2 is not null loop
 24
 25            if instr(l_str2, l_linebreak) <= 0 then
 26               l_leftover := l_str2;
 27               l_str2 := null;
 28            else
 29
 30               dbms_output.put_line ( substr(l_str2, 1, instr(l_str2, l_linebreak)-1) );
 31               l_str2 := substr(l_str2, instr(l_str2, l_linebreak)+2);
 32            end if;
 33
 34           end loop;
 35
 36           l_offset := l_offset + l_chunksize;
 37
 38        end loop;
 39
 40        if l_leftover is not null then
 41        dbms_output.put_line ( l_leftover );
 42        end if;
 43
 44    end;
 45  /
53549591
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 19


Can you please share what could go wrong? 

I suspect I'm missing something very basic but why do I get different results using raw_to_varchar2 when it operates on pl/sql variable and on a column in a sql statement?

Oracle9i Enterprise Edition Release 9.2.0.6.0

SQL> set serverout on
SQL> create table t1 (col1 number,
  2                   col2 blob);

Table created.
-- Sorry a bit lazy here
SQL>                  
SQL> insert into t1 (select 1,doc_contents from documents where doc_extension in ('htm','TXT','doc')
);

7 rows created.

SQL> 
SQL> select length(utl_raw.cast_to_varchar2(col2)) vc_length,dbms_lob.getlength(col2) lob_length
  2  from   t1;  

 VC_LENGTH LOB_LENGTH
---------- ----------
         6          6
      2000      19456
      2000      19456
      2000       3115
      2000    1365504
      2000      32256
      2000      47616

7 rows selected.

SQL> 
SQL> declare
  2   blob_rec t1%rowtype;
  3   v_var_blob varchar2(32767);
  4  begin
  5   for blob_rec in (select * from t1) loop
  6       v_var_blob :=  utl_raw.cast_to_varchar2(dbms_lob.substr(blob_rec.col2,32000,1));
  7       dbms_output.put_line('vc_length ='||to_char(length(v_var_blob))||'  lob_length ='||to_char
(dbms_lob.getlength(blob_rec.col2)));
  8    end loop;
  9  end;    
 10  /
vc_length =6  lob_length =6
vc_length =19456  lob_length =19456
vc_length =19456  lob_length =19456
vc_length =3115  lob_length =3115
vc_length =32000  lob_length =1365504
vc_length =32000  lob_length =32256
vc_length =32000  lob_length =47616

PL/SQL procedure successfully completed.