Home » Developer & Programmer » Forms » How to use GET_FILE_NAME in 9i
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to use GET_FILE_NAME in 9i [message #199751 is a reply to message #198530] |
Thu, 26 October 2006 02:12  |
kbhujendra@rediffmail.com
Messages: 26
Registered: June 2006
Location: Hyderabad,India
|
Junior Member |
 
|
|
Hi,
If you want to use "Get_File_Name" function in Forms10g use CLIENT_GET_FILE_NAME not Get_File_Name.
We will use "Get_File_Name" in Forms6i
We will use "Client_Get_File_Name" in Forms10g with the help of "webutil.pll" ,which we download from oracle.
Below is the sample code.If you want to use it, you must attach webutil.pll
PROCEDURE web_export_to_excel(cur_block_v varchar2)
IS
TYPE t_format_mask IS TABLE OF VARCHAR2 (30)
INDEX BY BINARY_INTEGER;
TYPE t_item_type IS TABLE OF VARCHAR2 (15)
INDEX BY BINARY_INTEGER;
TYPE t_data_type IS TABLE OF VARCHAR2 (15)
INDEX BY BINARY_INTEGER;
TYPE t_multi_line IS TABLE OF VARCHAR2 (5)
INDEX BY BINARY_INTEGER;
TYPE t_display IS TABLE OF BOOLEAN
INDEX BY BINARY_INTEGER;
TYPE t_item_id IS TABLE OF item
INDEX BY BINARY_INTEGER;
v_format_mask t_format_mask;
v_item_type t_item_type;
v_data_type t_data_type;
v_multi_line t_multi_line;
v_display t_display;
v_item_id t_item_id;
v_char VARCHAR2 (4000);
v_file_name VARCHAR2 (2200);
v_list_value VARCHAR2 (100);
v_prompt_text VARCHAR2 (60);
v_hint_text VARCHAR2 (60);
v_label VARCHAR2 (60);
v_name VARCHAR2 (30);
v_visible VARCHAR2 (5);
v_rows PLS_INTEGER;
v_columns PLS_INTEGER;
v_lines PLS_INTEGER;
v_index PLS_INTEGER;
v_list_count PLS_INTEGER;
v_list_index PLS_INTEGER;
v_length PLS_INTEGER;
v_type PLS_INTEGER;
v_null BOOLEAN;
v_number NUMBER;
v_date DATE;
v_connection exec_sql.conntype;
v_cursor exec_sql.curstype;
v_handle text_io.file_type;
BEGIN
v_file_name:= [b]Client_Get_File_Name[/b]('C:\','file_name.xls','XLS Files (*.xls)|*.xls|',NULL,SAVE_FILE,TRUE);
v_handle := text_io.fopen (v_file_name , 'w');
text_io.put_line
(v_handle,
'<html xmlns:o="urn:schemas-microsoft-com:office:office"'
);
text_io.put_line (v_handle,
'xmlns:x="urn:schemas-microsoft-com:office:excel"'
);
text_io.put_line (v_handle, 'xmlns="[url]http://www.w3.org/TR/REC-html40[/url]">');
text_io.put_line (v_handle, '<html>');
text_io.put_line (v_handle, '<body>');
text_io.put_line (v_handle, '<table border=1>');
text_io.put_line (v_handle, '<tr>');
v_connection := exec_sql.default_connection;
v_cursor := exec_sql.open_cursor (v_connection);
exec_sql.parse (v_connection,
v_cursor,
REPLACE (GET_BLOCK_PROPERTY (:SYSTEM.cursor_block,
last_query
),
'ROWID,'
)
);
v_columns := 0;
LOOP
v_columns := v_columns + 1;
BEGIN
exec_sql.describe_column (v_connection,
v_cursor,
v_columns,
v_name,
v_length,
v_type
);
EXCEPTION
WHEN exec_sql.invalid_column_number
THEN
v_columns := v_columns - 1;
EXIT;
END;
v_item_id (v_columns) :=
FIND_ITEM (:SYSTEM.cursor_block || '.' || v_name);
v_item_type (v_columns) :=
GET_ITEM_PROPERTY (v_item_id (v_columns), item_type);
v_data_type (v_columns) :=
GET_ITEM_PROPERTY (v_item_id (v_columns), datatype);
v_visible := GET_ITEM_PROPERTY (v_item_id (v_columns), visible);
v_prompt_text := GET_ITEM_PROPERTY (v_item_id (v_columns), prompt_text);
v_hint_text := GET_ITEM_PROPERTY (v_item_id (v_columns), hint_text);
IF v_item_type (v_columns) = 'TEXT ITEM'
THEN
v_format_mask (v_columns) :=
GET_ITEM_PROPERTY (v_item_id (v_columns), format_mask);
ELSE
v_format_mask (v_columns) := NULL;
END IF;
IF v_hint_text IS NOT NULL
THEN
v_label := v_hint_text;
ELSE
v_label := v_prompt_text;
END IF;
IF v_item_type (v_columns) <> 'DISPLAY ITEM' AND v_visible = 'TRUE'
THEN
v_display (v_columns) := TRUE;
ELSE
v_display (v_columns) := FALSE;
END IF;
IF v_display (v_columns) = TRUE
THEN
text_io.put_line (v_handle,
'<td bgcolor="#FFFF00" x:autofilter="all">'
|| v_label
|| '</td>'
);
END IF;
IF v_data_type (v_columns) = 'CHAR'
THEN
IF v_item_type (v_columns) = 'TEXT ITEM'
THEN
v_multi_line (v_columns) :=
GET_ITEM_PROPERTY (v_item_id (v_columns), multi_line);
ELSE
v_multi_line (v_columns) := 'FALSE';
END IF;
exec_sql.define_column (v_connection,
v_cursor,
v_columns,
v_char,
v_length
);
END IF;
IF v_data_type (v_columns) = 'NUMBER'
THEN
IF v_format_mask (v_columns) IS NOT NULL
THEN
v_format_mask (v_columns) :=
REPLACE (v_format_mask (v_columns), '9', '#');
v_format_mask (v_columns) :=
REPLACE (v_format_mask (v_columns), 'G', ',');
v_format_mask (v_columns) :=
REPLACE (v_format_mask (v_columns), 'D', '.');
END IF;
v_multi_line (v_columns) := 'FALSE';
exec_sql.define_column (v_connection, v_cursor, v_columns, v_number);
END IF;
IF v_data_type (v_columns) IN ('DATE', 'DATETIME')
THEN
v_multi_line (v_columns) := 'FALSE';
exec_sql.define_column (v_connection, v_cursor, v_columns, v_date);
END IF;
END LOOP;
text_io.put_line (v_handle, '</tr>');
v_rows := exec_sql.EXECUTE (v_connection, v_cursor);
v_lines := 0;
WHILE exec_sql.fetch_rows (v_connection, v_cursor) > 0
LOOP
v_lines := v_lines + 1;
text_io.put_line (v_handle, '<tr>');
FOR v_index IN 1 .. v_columns
LOOP
v_null := FALSE;
IF v_data_type (v_index) = 'CHAR'
THEN
exec_sql.column_value (v_connection, v_cursor, v_index, v_char);
IF v_char IS NULL
THEN
v_null := TRUE;
END IF;
END IF;
IF v_data_type (v_index) = 'NUMBER'
THEN
exec_sql.column_value (v_connection, v_cursor, v_index, v_number);
IF v_number IS NULL
THEN
v_null := TRUE;
END IF;
END IF;
IF v_data_type (v_index) IN ('DATE', 'DATETIME')
THEN
exec_sql.column_value (v_connection, v_cursor, v_index, v_date);
IF v_date IS NULL
THEN
v_null := TRUE;
END IF;
END IF;
IF v_display (v_index) = TRUE
THEN
IF v_null = TRUE
THEN
text_io.put_line (v_handle, '<td></td>');
ELSE
IF v_data_type (v_index) = 'CHAR'
THEN
v_char := REPLACE (v_char, ' ', ' ');
IF v_item_type (v_index) = 'CHECKBOX'
THEN
IF v_char IN ('Y', 'YES')
THEN
v_char := 'YES';
ELSE
v_char := 'NO';
END IF;
END IF;
IF v_item_type (v_index) = 'LIST'
THEN
v_list_count :=
GET_LIST_ELEMENT_COUNT (v_item_id (v_index));
FOR v_list_index IN 1 .. v_list_count
LOOP
v_list_value :=
GET_LIST_ELEMENT_VALUE (v_item_id (v_index),
v_list_index
);
IF v_list_value = v_char
THEN
v_char :=
GET_LIST_ELEMENT_LABEL (v_item_id (v_index),
v_list_index
);
EXIT;
END IF;
END LOOP;
END IF;
IF v_multi_line (v_index) = 'TRUE'
THEN
text_io.put_line
(v_handle,
'<td style=''white-space:wrap'' x:str>'
|| v_char
|| '</td>'
);
ELSE
text_io.put_line
(v_handle,
'<td style=''white-space:nowrap'' x:str>'
|| v_char
|| '</td>'
);
END IF;
END IF;
IF v_data_type (v_index) = 'NUMBER'
THEN
IF v_format_mask (v_index) IS NOT NULL
THEN
text_io.put_line (v_handle,
'<td style=''mso-number-format:"'
|| v_format_mask (v_index)
|| '"'' x:num>'
|| TO_CHAR (v_number)
|| '</td>'
);
ELSE
text_io.put_line (v_handle,
'<td x:num>'
|| TO_CHAR (v_number)
|| '</td>'
);
END IF;
END IF;
IF v_data_type (v_index) = 'DATE'
THEN
text_io.put_line
(v_handle,
'<td align=left style=''mso-number-format:"dd-mmm-yyyy"'' x:num>'
|| TO_CHAR (v_date, 'DD-MON-YYYY')
|| '</td>'
);
END IF;
IF v_data_type (v_index) = 'DATETIME'
THEN
text_io.put_line
(v_handle,
'<td align=left style=''mso-number-format:"dd-mmm-yyyy hh:mm:ss"'' x:num>'
|| TO_CHAR (v_date, 'DD-MON-YYYY HH24:MI:SS')
|| '</td>'
);
END IF;
END IF;
END IF;
END LOOP;
text_io.put_line (v_handle, '</tr>');
EXIT WHEN v_lines = 65535;
END LOOP;
exec_sql.close_cursor (v_cursor);
exec_sql.close_connection (v_connection);
text_io.put_line (v_handle, '</table>');
text_io.put_line (v_handle, '</body>');
text_io.put_line (v_handle, '</html>');
text_io.fclose (v_handle);
MESSAGE ('Data exported to excel sheet');
MESSAGE (' ');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -302000
THEN
LOOP
EXIT WHEN TOOL_ERR.NERRORS = 0;
MESSAGE (TO_CHAR(TOOL_ERR.CODE) || ': ' || TOOL_ERR.MESSAGE);
TOOL_ERR.POP;
END LOOP;
END IF;
END;
Thanks,
Bhujendra
Upd-mod: Please post code inside 'code' tags.
[Updated on: Thu, 26 October 2006 02:43] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Wed Apr 24 23:26:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
