Home » Developer & Programmer » Forms » How to use GET_FILE_NAME in 9i
icon9.gif  How to use GET_FILE_NAME in 9i [message #198530] Tue, 17 October 2006 09:10 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member




How to use GET_FILE_NAME in 9i.I am getting this in 6i but in 9i it is not working .I used only one argument filefilter in this function it s working in 6i but not in 9i.If any one have the code of GET_FILE_NAME thats work in 9i please help me



Thanks in advance .Have a nice day Smile
Re: How to use GET_FILE_NAME in 9i [message #198605 is a reply to message #198530] Tue, 17 October 2006 23:22 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
In what way is it not working? Do you get an error message? Were you running your 6i in client/server mode?

David
icon4.gif  Re: How to use GET_FILE_NAME in 9i [message #198770 is a reply to message #198605] Wed, 18 October 2006 08:26 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


In 6i it is working in client/server mode and in 9i also in client server mode and not getting any error message but not getting the ouput please help me to solve this problen
Re: How to use GET_FILE_NAME in 9i [message #198860 is a reply to message #198770] Wed, 18 October 2006 20:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please describe your configuration to explain how you are running Forms 9i in client/server mode.

David
Re: How to use GET_FILE_NAME in 9i [message #199214 is a reply to message #198860] Sat, 21 October 2006 07:39 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member



Yes Forms 9i in is running in client/server mode.please help me
Re: How to use GET_FILE_NAME in 9i [message #199245 is a reply to message #199214] Sun, 22 October 2006 19:40 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Forms 9i does NOT run in client/server mode. You must have either an Application Server or, during the development phase, a Developer Server.

What version are you running?

Please post the 6i code and the 9i code that you are using. Also, what is the error message?

David
Re: How to use GET_FILE_NAME in 9i [message #199751 is a reply to message #198530] Thu, 26 October 2006 02:12 Go to previous message
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

Previous Topic: To generate random number
Next Topic: Oracle 9i startup
Goto Forum:
  


Current Time: Sun Dec 04 18:40:59 CST 2016

Total time taken to generate the page: 0.08882 seconds