Solved

Upload BLOb File

Posted on 2009-07-14
7
1,209 Views
Last Modified: 2012-05-07
Hi Experts.

I used delphi 6 and oracle 8i, I would like upload file crd+ to web Portal and stored in table DOCUMENTS in oracle database. I created procedure AUTO_UPLOAD_MFB in oracle and used  TOraStoredProc in delphi to connect to procedure AUTO_UPLOAD_MFB.
 But I have a problem:
Access violation at address 005753F9 in module 'mfb.exe' . Read of address 00000000.
Could you please help me.

Thanks so much.
procedure AUTO_UPLOAD_MFB in oracle
(V_PARTNO IN VARCHAR2,V_PATHNAME IN VARCHAR2,V_DESC IN VARCHAR2,V_FILE_TYPE IN VARCHAR2, V_CUST_VIEW IN VARCHAR2, V_BLOB_FILE IN BLOB) 
AS
v_email_content varchar2(5000);
v_err	        varchar2(5000);
v_email_user    varchar2(100);
l_seb  CONSTANT RAW(1000) := UTL_RAW.CAST_TO_RAW('0x');
 
BEGIN
	  INSERT INTO MYSVDBA.DOCUMENTS(MIME_TYPE,NAME,BLOB_CONTENT,LAST_UPDATED,doc_size,CONTENT_TYPE,DAD_CHARSET,CONTENT) 
	  	VALUES('text/plain',V_PATHNAME,V_BLOB_FILE,SYSDATE,DBMS_LOB.GETLENGTH(V_blob_file),'BLOB','ascii',l_seb);
	  commit;
 
	  insert into mysvdba.sv_file_xref(partno, filename,file_type, cust_viewable, description, user_id, date_time, enabled, status, partno_idx)
	  	VALUES(V_PARTNO,V_PATHNAME,V_FILE_TYPE,V_CUST_VIEW,V_DESC,2454,SYSDATE,-1,'RELEASED',SUBSTR(V_PARTNO,1,10));
	  commit;
	  	  
 EXCEPTION
  WHEN OTHERS THEN
   htp.p(sqlerrm || V_PARTNO);
   v_err := sqlerrm;
   if v_err like 'ORA-01401%' then
    	insert into mysvdba.SV_FR_PART_ERROR values(V_PATHNAME,V_PARTNO,sysdate,'File name too long ' || v_err);
   	commit;
  elsif v_err like 'ORA-01691%' then
    	insert into mysvdba.SV_FR_PART_ERROR values(V_PATHNAME,V_PARTNO,sysdate,'Over size file, error in AUTO UPLOAD ' || v_err);
   	commit;
   else
    	insert into mysvdba.SV_FR_PART_ERROR values(V_PATHNAME,V_PARTNO,sysdate,'ERROR IN AUTO UPLOAD '||v_err);
   	commit;
   end if;
  RETURN;
END;
 
 
 
Code in Delphi:
 
unit UploadFilePortal;
interface
uses
   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids, ComCtrls, DBTables, IBCustomDataSet,RzLabel, ExtActns, StrUtils,
  OraClasses,
  ExtCtrls   ;
type
  TFrmUploadPortal = class(TForm)
    RzLabel1: TRzLabel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    edFilePath: TEdit;
    edFileDesc: TEdit;
    cboFileType: TComboBox;
    cboCustomerViewable: TComboBox;
    cboFileStatus: TComboBox;
    btnDialUploadPath: TButton;
    btnUploadSubmit: TButton;
    edPartNumber: TEdit;
    Label6: TLabel;
    OpenDialogGAF: TOpenDialog;
 
 
    procedure btnDialUploadPathClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnUploadSubmitClick(Sender: TObject);
 
   private
    { Private declarations }
  public
    { Public declarations }
  end;
 
var
  FrmUploadPortal: TFrmUploadPortal;
 
implementation
 
uses UnitDataConfig, Ora, gaf;
 
{$R *.dfm}
 
 
procedure TFrmUploadPortal.btnUploadSubmitClick(Sender: TObject);
var fs: TFileStream;
    bs: TBlobStream;
    PartNo: string;
    FileName,FileExt,FileName_ : string;
    MyFormatDateTime: string; //decode the datetime
    EncrypFileName: string;
    FileReleasedOnPortal:string; //use to store all file which have been released.
    i:integer;
     vBlob : TOraLob;
    OraSession : TOraSession;
 
begin
 
      FileName:= ExtractFileName(edFilePath.Text);
      FileName_ := edFilePath.Text;
      FileExt:=  ExtractFileExt(FileName);
      PartNo:= edPartNumber.Text;
      DateTimeToString(MyFormatDateTime, 'ddmmyyhhnnss', now());
      EncrypFileName:= 'F'+ MyFormatDateTime + '/' + FileName;
 
     
  with DataConfig.OraStoredProc do
  begin
       ParamByName('v_partno').AsString := PartNo;
       ParamByName('V_PATHNAME').AsString := FileName_ ; //EncrypFileName;
       ParamByName('V_DESC').AsString := edFileDesc.Text ;
       ParamByName('V_FILE_TYPE').AsString := cboFileType.Items.Strings[cboFileType.ItemIndex] ;
       ParamByName('V_CUST_VIEW').AsString := cboCustomerViewable.Items.Strings[cboCustomerViewable.itemIndex];
       ParamByName('V_BLOB_FILE').ParamType := ptInput;
       vBlob := TOraLob.Create(OraSession.OCISvcCtx);
       vBlob.CreateTemporary(ltBlob);
       vBlob.LoadFromFile(OpenDialogGAF.FileName);
       vBlob.WriteLob;
       ParamByName('V_BLOB_FILE').AsOraBlob := vBlob ;
       Execute;
  end;
       ShowMessage('File has been uploaded');
end;
 
end.

Open in new window

0
Comment
Question by:SVPIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
7 Comments
 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 500 total points
ID: 24861412
i think uploading a blob via a stored proc will not work,
you'll have to use a insert statement

http://www.experts-exchange.com/Programming/Editors_IDEs/Delphi/Q_24046869.html
0
 

Author Comment

by:SVPIT
ID: 24867114
Hi Geert,

No we can upload a blob via a stored proc by using TOraStoredProc in ODAC. I added more line  Options.TemporaryLobUpdate := True;
I can upload and It's Ok now. you can view code as below:

  with OraStoredProc do
  begin
       Options.TemporaryLobUpdate := True;
       StoredProcName := 'AUTO_UPLOAD_MFB';
       Prepare;
       ParamByName('v_partno').AsString := PartNo;
       ParamByName('V_PATHNAME').AsString := EncrypFileName;
       ParamByName('V_DESC').AsString := edFileDesc.Text ;
       ParamByName('V_FILE_TYPE').AsString := cboFileType.Items.Strings[cboFileType.ItemIndex] ;
       ParamByName('V_CUST_VIEW').AsString :=    cboCustomerViewable.Items.Strings[cboCustomerViewable.itemIndex];
       ParamByName('V_STATUS').AsString := cboFileStatus.Items.Strings[cboFileStatus.itemIndex];
       ParamByName('V_BLOB_FILE').ParamType := ptInput;
       ParamByName('V_BLOB_FILE').AsOraClob.LoadFromFile(FileName);
       Execute;
       ShowMessage('File has been uploaded');
  end;

Thanks for your help.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 24875265
via stored proc there is a limit of 4000 bytes
is the file bigger ?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Updating a temp table inside a PL/SQL block 3 63
pl/sql parameter is null sometimes 2 38
oracle query 3 34
Check for any ASM patches and install them. 1 23
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question