10 Replies Latest reply on Apr 29, 2016 9:53 AM by Kalpataru

Unable to attach document in database Via Oracle Forms 11gR2?

Su.gi Apr 29, 2016 5:34 AM

Hi All,

I am developing an application using Oracle Forms and Reports 11gR2.

I have a table like below

CREATE TABLE EMPLOYEE_ATTACH

( EA_SEQ_NO NUMBER(5),

EA_DOC_NAME VARCHAR2(50 BYTE),

EA_DOC BLOB )

I used the above table in forms and have a one button.

In WHEN-BUTTON-PRESSED trigger, I used the below code

DECLARE

VAR_FILE_NAME VARCHAR2 (500);

VAR_FILE_NAME_ONLY VARCHAR2 (100);

GLO_BU VARCHAR2 (500);

LB$RESULT BOOLEAN;

BEGIN

VAR_FILE_NAME := client_get_file_name ('','','jpeg Files/*.jpg','Select an Doc File', open_file,TRUE);

IF SUBSTR (VAR_FILE_NAME, 1, 1) = '\'

THEN

VAR_FILE_NAME := '\' || VAR_FILE_NAME;

END IF;

VAR_FILE_NAME_ONLY :=

SUBSTR (VAR_FILE_NAME,

INSTR (VAR_FILE_NAME, '\', -1) + 1,

LENGTH (VAR_FILE_NAME));

:EA_DOC_NAME := VAR_FILE_NAME_ONLY;

COMMIT;

GLO_BU := 'EA_SEQ_NO = ''' || :EA_SEQ_NO || '''';

LB$RESULT :=

WEBUTIL_FILE_TRANSFER.CLIENT_TO_DB_WITH_PROGRESS (

VAR_FILE_NAME,

'EMPLOYEE_ATTACH',

'EA_DOC',

GLO_BU,

'File Upload',

'FILE UPLOAD IN PROGRESS..',

FALSE,

NULL);

END;

For me, If I choose the file for attach, then the progress bar is loading, but it is not stored in my database.

How to solve this problem??

Thanks in Advance,

Su.gi

1. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 5:06 AM (in response to Su.gi)

IF you are using webutil then why are you using this FBean.Invoke_char.
It can be done using webutil.open_file_dialog and after that you can select the file
and rest of the work will same .

You can check this webutil demo for your reference http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/196249.zip
1 person found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Su.gi Apr 29, 2016 5:36 AM (in response to Kalpataru)

Thanks for your reply.

Sorry for the wrong code......Now i edited my post... This is my original code.

For me It shows like uploading but in database not stored in BLOB column.

Thanks,
Su.gi
Like Show 0 Likes(0)

Actions
3. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 5:40 AM (in response to Su.gi)

Have you checked the reference which i have given above webutil demo form ?
Like Show 0 Likes(0)

Actions
4. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Su.gi Apr 29, 2016 5:46 AM (in response to Kalpataru)

Thanks for your reply.

I already downloaded that webutil demo. I worked as it as reference.

For me, webutil demo too not working....

Is that any error in my code??

Thanks,
Su.gi
Like Show 0 Likes(0)

Actions
5. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 6:30 AM (in response to Su.gi)

webutil demo too not working....
Not working means what any error is there ?
Do you able to attach the jpg file ?
Like Show 0 Likes(0)

Actions

6. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 6:47 AM (in response to Su.gi)

Try this

DECLARE
  VAR_FILE_NAME        VARCHAR2 (500);
  VAR_FILE_NAME_ONLY  VARCHAR2 (100);
  GLO_BU              VARCHAR2 (500);
  LB$RESULT            BOOLEAN;
BEGIN

VAR_FILE_NAME := client_get_file_name( directory_name => null
                                      ,file_name      => null
                                      ,file_filter    => '|JPG Files(*.jpg)|*.jpg|'
                                      ,message        => 'Select an Image File'
                                      ,dialog_type    => open_file
                                      ,select_file    => True
                                      );

  /*
  IF SUBSTR (VAR_FILE_NAME, 1, 1) = '\'
  THEN
      VAR_FILE_NAME := '\' || VAR_FILE_NAME;
  END IF;
  */
  VAR_FILE_NAME_ONLY := SUBSTR (VAR_FILE_NAME, INSTR (VAR_FILE_NAME, '\', -1) + 1, LENGTH (VAR_FILE_NAME));
  :EA_DOC_NAME := VAR_FILE_NAME_ONLY;
  GLO_BU := 'EA_SEQ_NO='||:EA_SEQ_NO;
  LB$RESULT := WEBUTIL_FILE_TRANSFER.CLIENT_TO_DB_WITH_PROGRESS (clientFile      => VAR_FILE_NAME,
                                                                  ,tableName      => 'EMPLOYEE_ATTACH',
                                                                  ,columnName      => 'EA_DOC',
                                                                  ,whereClause    => GLO_BU,
                                                                  ,progressTitle  => 'File Upload',
                                                                  ,progressSubTitle=> 'File Upload In Progress..',
                                                                  ,asynchronous    => false,
                                                                  ,callbackTrigger => Null);
END;

Change c:\temp to your correct path if you are not using c:\temp

Check that in webutil.cfg you have to set the following configuration

transfer.database.enabled=TRUE

transfer.appsrv.enabled=TRUE

transfer.appsrv.workAreaRoot=c:\temp

transfer.appsrv.accessControl=TRUE

transfer.appsrv.read.1=c:\temp

transfer.appsrv.write.1=c:\temp

Check that your EA_SEQ_NO is generating correctly.

7. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Su.gi Apr 29, 2016 7:22 AM (in response to Kalpataru)

Thanks Kalpataru. Thanks for accepting me as friend.

I used the below code after LB$RESULT condition.

if LB$RESULT
the
:System.Message_Level := '15';
commit;
:System.Message_Level := '0';
end if;

After using this, its working..... I just seen the code in webutil demo file.
Is it correct way????

Thanks,
Su.gi
Like Show 0 Likes(0)

Actions
8. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 7:25 AM (in response to Su.gi)

Yes you have to use like this.

if LB$RESULT then

:System.Message_Level := '15';

commit;

:System.Message_Level := '0';

Set_Alert_Property('Alt_Confirm',Alert_Message_Text,'File uploaded successfully into the Database');

v_Alt:=Show_Alert('Alt_Confirm');

else

Set_Alert_Property('Alt_Error',Alert_Message_Text,'File upload to Database failed');

v_Alt:=Show_Alert('Alt_Error');

end if;

1 person found this helpful
Like Show 0 Likes(0)

Actions
9. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Su.gi Apr 29, 2016 9:42 AM (in response to Kalpataru)

I have one doubt.
Why we want to use the below code
if LB$RESULT then
:System.Message_Level := '15';
commit;
:System.Message_Level := '0';
end if;

Without using this, my attachment is not storing in BLOB column.

When I used the above code its working...
Like Show 0 Likes(0)

Actions
10. Re: Unable to attach document in database Via Oracle Forms 11gR2?

Kalpataru Apr 29, 2016 9:53 AM (in response to Su.gi)

Check this thread you will find all your answers SYSTEM.MESSAGE_LEVE clarification
Know How And When To Use System.Message_Level To Control Messages In Oracle Forms | Fox Infotech
Refresh / Updating a form screen in Oracle D2k Forms 6i | Fox Infotech
Like Show 0 Likes(0)

Actions

Go to original post