Home » Developer & Programmer » Forms » Not able to download Excel data from Oracle 10G with Window Server (Oracle 10G, Forms, Window Server 2012 R2)
Not able to download Excel data from Oracle 10G with Window Server [message #628181] |
Thu, 20 November 2014 20:24 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/3c034367dd59224051ae06ba8d43d98d?s=64&d=mm&r=g) |
praveenjin@gmail.com
Messages: 10 Registered: November 2014 Location: India
|
Junior Member |
|
|
Hello Team/friends,
I'm using 'ole2' utility to download an excel file from Oracle form 10G client.
I am sharing the code Which I'm using to download excel file. Currently this code is working on my local computer (OS: Window 7) to download excel but after I migrated to MS SERVER 2012 R2 it is not working.
Can someone help me understand why it is not working?
Here is the code for reference and any help is greatly appreciate it..
PROCEDURE fpr_forms_to_excel (
p_block_name IN VARCHAR2 DEFAULT NAME_IN ('system.current_block'),
p_path IN VARCHAR2 DEFAULT 'C:\',
p_file_name IN VARCHAR2 DEFAULT 'Temp'
)
IS
-- Declare the OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheets ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;
RANGE ole2.obj_type;
range_col ole2.obj_type;
-- Declare handles to OLE argument lists
args ole2.list_type;
arglist ole2.list_type;
-- Declare form and block items
form_name VARCHAR2 (100);
f_block VARCHAR2 (100);
l_block VARCHAR2 (100);
f_item VARCHAR2 (100);
l_item VARCHAR2 (100);
cur_block VARCHAR2 (100) := NAME_IN ('system.current_block');
cur_item VARCHAR2 (100);
cur_record VARCHAR2 (100);
item_name VARCHAR2 (100);
baslik VARCHAR2 (100);
row_n NUMBER;
col_n NUMBER;
filename VARCHAR2 (1000) := p_path || p_file_name;
excelfontid ole2.list_type;
v_file_name varchar2(1000);
BEGIN
-- Start Excel
application := ole2.create_obj ('Excel.Application');
ole2.set_property (application, 'Visible', 'False');
-- Return object handle to the Workbooks collection
workbooks := ole2.get_obj_property (application, 'Workbooks');
-- Add a new Workbook object to the Workbooks collection
workbook := ole2.get_obj_property (workbooks, 'Add');
-- Return object handle to the Worksheets collection for the Workbook
worksheets := ole2.get_obj_property (workbook, 'Worksheets');
-- Get the first Worksheet in the Worksheets collection
-- worksheet:=OLE2.GET_OBJ_PROPERTY(worksheets,'Add');
args := ole2.create_arglist;
ole2.add_arg (args, 1);
worksheet := ole2.get_obj_property (worksheets, 'Item', args);
ole2.destroy_arglist (args);
-- Return object handle to cell A1 on the new Worksheet
GO_BLOCK (p_block_name);
baslik := GET_BLOCK_PROPERTY (p_block_name, first_item);
--commented to consider the second item as the first item
f_item :=
p_block_name || '.' || GET_BLOCK_PROPERTY (p_block_name, first_item);
--in order to skip the old filename in the excel file
l_item :=
p_block_name || '.' || GET_BLOCK_PROPERTY (p_block_name, last_item);
FIRST_RECORD;
LOOP
item_name := f_item;
row_n := NAME_IN ('SYSTEM.CURSOR_RECORD');
col_n := 1;
LOOP
IF GET_ITEM_PROPERTY (item_name, item_type) <> 'BUTTON'
AND GET_ITEM_PROPERTY (item_name, visible) = 'TRUE'
-- AND GET_ITEM_PROPERTY (item_name,item_name) <> 'TOTAL_AMOUNT'
THEN
-- Set first row with the item names
IF row_n = 1
THEN
args := ole2.create_arglist;
ole2.add_arg (args, 1);
ole2.add_arg (args, col_n);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.destroy_arglist (args);
--cell_value := OLE2.get_char_property(cell, 'Value');
excelfontid := ole2.get_obj_property (cell, 'Font');
ole2.set_property (excelfontid, 'Bold', 'True');
--------------------------------------------
baslik :=
NVL (GET_ITEM_PROPERTY (item_name, prompt_text), baslik);
args := ole2.create_arglist;
ole2.add_arg (args, row_n);
ole2.add_arg (args, col_n);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.destroy_arglist (args);
ole2.set_property (cell, 'Value', baslik);
ole2.RELEASE_OBJ (cell);
END IF;
-- Set other rows with the item values
args := ole2.create_arglist;
ole2.add_arg (args, row_n + 1);
ole2.add_arg (args, col_n);
cell := ole2.get_obj_property (worksheet, 'Cells', args);
ole2.destroy_arglist (args);
IF GET_ITEM_PROPERTY (item_name, datatype) <> 'NUMBER'
THEN
ole2.set_property (cell, 'NumberFormat', '@');
END IF;
ole2.set_property (cell, 'Value', NAME_IN (item_name));
ole2.RELEASE_OBJ (cell);
END IF;
IF item_name ='XXEBS_MONTH_END_DETAILS_V.REPORTED_HOURS_HANDLER' -- l_item
THEN
EXIT;
END IF;
baslik := GET_ITEM_PROPERTY (item_name, nextitem);
item_name :=
p_block_name || '.' || GET_ITEM_PROPERTY (item_name, nextitem);
col_n := col_n + 1;
END LOOP;
EXIT
WHEN NAME_IN ('system.last_record') = 'TRUE';
NEXT_RECORD;
END LOOP;
-- Autofit columns
RANGE := ole2.get_obj_property (worksheet, 'UsedRange');
range_col := ole2.get_obj_property (RANGE, 'Columns');
ole2.invoke (range_col, 'AutoFit');
ole2.RELEASE_OBJ (RANGE);
ole2.RELEASE_OBJ (range_col);
-- Save as worksheet with a Specified file path & name.
IF NVL (filename, '0') <> '0'
THEN
args := ole2.create_arglist;
ole2.add_arg (args, filename);
ole2.invoke (worksheet, 'SaveAs', args);
ole2.destroy_arglist (args);
END IF;
Message ('File has been generated at C:\UL_DATA\'||:global.file_name);
Message ('File has been generated at C:\UL_DATA\'||:global.file_name);
-- Release the OLE objects
ole2.RELEASE_OBJ (worksheet);
ole2.RELEASE_OBJ (worksheets);
ole2.RELEASE_OBJ (workbook);
ole2.RELEASE_OBJ (workbooks);
ole2.invoke (application, 'Quit');
ole2.RELEASE_OBJ (application);
-- Focus to the original location
EXCEPTION
WHEN OTHERS
THEN
NULL;
RAISE form_trigger_failure;
END;
[EDITED by LF: applied [code] tags]
[Updated on: Mon, 24 November 2014 15:15] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Not able to download Excel data from Oracle 10G with Window Server [message #629147 is a reply to message #628501] |
Wed, 03 December 2014 15:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/6f539f43889b4b3e3fcb6c591073de8f?s=64&d=mm&r=g) |
CraigB
Messages: 386 Registered: August 2014 Location: Utah, USA
|
Senior Member |
|
|
Quote:1. Create a DB package as per the script, but for This I didn't create any new user. I've created this database object by using my existing development schema. (is It effective some thing? )
It could if you have not created the public synonyms to the WebUtil objects. It is always best to create the user, but it is not necessary. If you are able to successfully compile the webutil.pll then you should be OK.
Quote:2. Not able to understand step# 5.
Are you refering to Step 5 in the Runtime Setup Checklist? If yes, then this step is typically already completed as part of the default installation, but you need to check and confirm that the Virtual Directory is valid for your configuration. Just because you don't understand the step doesn't mean you shouldn't perform the step! Virtual directories are configured within the Application Server. You'll need to figure out how to check this...and yes, this could be the cause of your error.
Quote:IF NOT webutil_file_transfer.AS_to_Client('C:\temp\pp.xlsx', 'C:\temp\pp.xlsx') THEN
This assumes the client computer has the same directory as the application server. A better option would be to prompt the user to select the directory to download the file too. This can be done using WebUtil_File.Directory_Selection_Dialog() method.
Also, when you are working with Java or WebUtil (which uses Java) it is a good idea to check the Java Console for any Java errors. Java errors will not propogate up to Forms in most cases. To display the Java Console while your Form is running just right-click on the Java Icon in the System Tray and select "Show Console" from the context menu.
Quote:Please check the same and guide me what I'm missing while configuration of webuitl.
Start with understanding and completing Step 5. Run the Java Console so you can the Java Exceptions (if any). Finally, did you modify the webutil.cfg file to enable File transfers? This feature is disable by default because it is a security risk. ![Smile](images/smiley_icons/icon_smile.gif)
Craig...
[Updated on: Wed, 03 December 2014 16:00] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:08:18 CDT 2024
|