Home » SQL & PL/SQL » SQL & PL/SQL » Generate the file having dynamic columns (Oracle Release 9.0.1.4.0)
Generate the file having dynamic columns [message #299403] Mon, 11 February 2008 20:41 Go to next message
sandeep_kushwaha
Messages: 3
Registered: February 2008
Location: HongKong
Junior Member

Hi,

I needs to generate a '#' delimited file. I wants to save the file structure in any table and then to generate the file according to the structure defined in table. I have written below code, but stuck with on problem. I don't want to hardcode the columns name when placeing the values to the file. I want something work around like this
x_text := 'rec_array.' || r2.column_name;

But by this I am not able to do the same. Please suggest, how it can be done.

Thanks in ton.


CREATE OR REPLACE PROCEDURE intf_item_file
IS
   CURSOR c1
   IS
      SELECT b.*
        FROM intf_queues a, intf_item_master b
       WHERE a.queue_token = b.queue_token;

   r1       c1%ROWTYPE;

   CURSOR c2
   IS
      SELECT   *
          FROM intf_file_structure
         WHERE intf_name = 'ITEM' AND direction = 'O'
      ORDER BY seq;

   r2       c2%ROWTYPE;
   v_text   VARCHAR2 (4000);
   t_text   VARCHAR2 (4000);
   x_text   VARCHAR2 (4000);
   v_file   UTL_FILE.file_type;
   stmt     VARCHAR2 (200);
BEGIN
   v_file :=
      UTL_FILE.fopen ('/usr01/app/oracle/admin/sihkbrms/utl_file',
                      'ITEM_INTERFACE_TEST',
                      'w',
                      32767
                     );
   v_text := NULL;

   OPEN c1;

   LOOP
      FETCH c1
       INTO r1;

      EXIT WHEN c1%NOTFOUND;

/***************** Problem Code  *********************/
      FOR r2 IN c2
      LOOP
         x_text := 'rec_array.' || r2.column_name;
         UTL_FILE.putf (v_file, '%s', '#');
         UTL_FILE.putf (v_file, '%s', x_text);
      END LOOP;

/***************** END Problem Code  *********************/
      UTL_FILE.new_line (v_file);
   END LOOP;

   CLOSE c1;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN UTL_FILE.invalid_mode
   THEN
      raise_application_error (-20051, 'Invalid Mode Parameter');
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (-20052, 'Invalid File Location');
   WHEN UTL_FILE.invalid_filehandle
   THEN
      raise_application_error (-20053, 'Invalid Filehandle');
   WHEN UTL_FILE.invalid_operation
   THEN
      raise_application_error (-20054, 'Invalid Operation');
   WHEN UTL_FILE.read_error
   THEN
      raise_application_error (-20055, 'Read Error');
   WHEN UTL_FILE.internal_error
   THEN
      raise_application_error (-20057, 'Internal Error');
   WHEN UTL_FILE.charsetmismatch
   THEN
      raise_application_error
                        (-20058,
                         'Opened With FOPEN_NCHAR But Later I/O Inconsistent'
                        );
   WHEN UTL_FILE.file_open
   THEN
      raise_application_error (-20059, 'File Already Opened');
   WHEN UTL_FILE.invalid_maxlinesize
   THEN
      raise_application_error (-20060, 'Line Size Exceeds 32K');
   WHEN UTL_FILE.invalid_filename
   THEN
      raise_application_error (-20061, 'Invalid File Name');
   WHEN UTL_FILE.access_denied
   THEN
      raise_application_error (-20062, 'File Access Denied By');
   WHEN UTL_FILE.invalid_offset
   THEN
      raise_application_error (-20063, 'FSEEK Param Less Than 0');
   WHEN OTHERS
   THEN
      raise_application_error (-20099,
                               t_text || 'Unknown UTL_FILE Error' || SQLERRM
                              );
END;

Re: Generate the file having dynamic columns [message #299444 is a reply to message #299403] Tue, 12 February 2008 01:19 Go to previous messageGo to next message
sandeep_kushwaha
Messages: 3
Registered: February 2008
Location: HongKong
Junior Member

Hey Guys,

It's done, Thanks.

Sandeep
Re: Generate the file having dynamic columns [message #299465 is a reply to message #299444] Tue, 12 February 2008 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, you showed us a non-working procedure. How about posting a working one, so that other forum members might learn something from it?
Re: Generate the file having dynamic columns [message #299479 is a reply to message #299465] Tue, 12 February 2008 03:38 Go to previous messageGo to next message
sandeep_kushwaha
Messages: 3
Registered: February 2008
Location: HongKong
Junior Member

I have used DBMS_SQL package for this. Below is the procedure..


CREATE OR REPLACE PROCEDURE intf_item_file (
   p_intf_name    IN       VARCHAR2,
   p_direction    IN       VARCHAR2,
   p_event_code   IN       VARCHAR2,
   p_error        OUT      VARCHAR2
)
IS
   v_cur                   NUMBER;
   v_stmt                  VARCHAR2 (4000);
   v_desc_tab              DBMS_SQL.desc_tab;
   v_cnt                   NUMBER;
   v_status                NUMBER;
   v_val                   VARCHAR2 (4000);

   CURSOR c2
   IS
      SELECT   *
          FROM intf_file_structure
         WHERE intf_name = p_intf_name
           AND direction = p_direction
           AND event_code = p_event_code
      ORDER BY seq;

   r2                      c2%ROWTYPE;
   v_text                  VARCHAR2 (4000);
   v_file                  UTL_FILE.file_type;
   v_file_name             VARCHAR2 (70);
   v_mode_of_comm          intf_master.mode_of_comm%TYPE;
   v_file_type             intf_master.file_type%TYPE;
   v_separator             intf_master.separator%TYPE;
   v_lpad_rpad_chr         intf_master.lpad_rpad_chr%TYPE;
   v_file_name_prefix      intf_file_dir.file_name_prefix%TYPE;
   v_file_name_suffix      intf_file_dir.file_name_suffix%TYPE;
   v_file_name_variables   intf_file_dir.file_name_variables%TYPE;
   v_stage_file_dir        intf_file_dir.stage_file_dir%TYPE;
   v_queue_token           intf_queues.queue_token%TYPE;
   v_error_message         intf_queues.error_message%TYPE;
   v_seq                   NUMBER;
   v_status                intf_master.status%TYPE;
BEGIN
/* Updating Information over Interface Master & Detail */
   SELECT status
     INTO v_status
     FROM intf_master
    WHERE intf_name = p_intf_name
      AND direction = p_direction
      AND event_code = p_event_code;

   IF v_status <> 'A'
   THEN
      p_error := 'Interface is Not Active....';
      RETURN;
   END IF;

   SELECT intf_master_seq.NEXTVAL
     INTO v_seq
     FROM DUAL;

   UPDATE intf_master
      SET exec_seq = v_seq
    WHERE intf_name = p_intf_name
      AND direction = p_direction
      AND event_code = p_event_code;

   INSERT INTO intf_detail
               (intf_name, direction, event_code, exec_seq, start_date,
                end_date, total_record, success_record, failed_record, status
               )
        VALUES (p_intf_name, p_direction, p_event_code, v_seq, SYSDATE,
                NULL, 0, 0, 0, 'S'
               );

   COMMIT;

/* End Updating Information over Interface Master & Detail */
   SELECT mode_of_comm, file_type, separator, lpad_rpad_chr
     INTO v_mode_of_comm, v_file_type, v_separator, v_lpad_rpad_chr
     FROM intf_master
    WHERE intf_name = p_intf_name
      AND direction = p_direction
      AND event_code = p_event_code;

   IF v_mode_of_comm = 'D'                         -- If Mode is Database Link
   THEN
      /* This part is pending */
      p_error := 'This Part of the SP is peding';
      RETURN;
   ELSE
      SELECT file_name_prefix, file_name_suffix, file_name_variables,
             stage_file_dir
        INTO v_file_name_prefix, v_file_name_suffix, v_file_name_variables,
             v_stage_file_dir
        FROM intf_file_dir
       WHERE intf_name = p_intf_name
         AND direction = p_direction
         AND event_code = p_event_code;

      v_file_name :=
            v_file_name_prefix
         || TO_CHAR (SYSDATE, v_file_name_variables)
         || v_file_name_suffix;
      v_file := UTL_FILE.fopen (v_stage_file_dir, v_file_name, 'w', 32767);
      v_text := NULL;
      v_cur := DBMS_SQL.open_cursor;
      v_text := ' a.queue_token, a.action_type ';

      FOR r2 IN c2
      LOOP
         IF v_file_type = 'S'                    -- If File Type is Separator
         THEN
            IF v_text IS NULL
            THEN
               v_text := 'b.' || r2.column_name;
            ELSE
               v_text := v_text || ',b.' || r2.column_name;
            END IF;
         ELSIF v_file_type = 'L'                -- If File Type is Lpad length
         THEN
            IF v_text IS NULL
            THEN
               v_text :=
                     'LPAD(b.'
                  || r2.column_name
                  || ','
                  || r2.LENGTH
                  || ','''
                  || v_lpad_rpad_chr
                  || ''') '
                  || r2.column_name;
            ELSE
               v_text :=
                     v_text
                  || ','
                  || 'LPAD(b.'
                  || r2.column_name
                  || ','
                  || r2.LENGTH
                  || ','''
                  || v_lpad_rpad_chr
                  || ''') '
                  || r2.column_name;
            END IF;
         ELSIF v_file_type = 'R'              ---- If File Type is Rpad length
         THEN
            IF v_text IS NULL
            THEN
               v_text :=
                     'RPAD(b.'
                  || r2.column_name
                  || ','
                  || r2.LENGTH
                  || ','''
                  || v_lpad_rpad_chr
                  || ''') '
                  || r2.column_name;
            ELSE
               v_text :=
                     v_text
                  || ','
                  || 'RPAD(b.'
                  || r2.column_name
                  || ','
                  || r2.LENGTH
                  || ','''
                  || v_lpad_rpad_chr
                  || ''') '
                  || r2.column_name;
            END IF;
         END IF;
      END LOOP;

      l_stmt :=
            'select '
         || v_text
         || ' from intf_queues a, intf_item_master b where '
         || ' a.queue_token=b.queue_token and a.intf_name=b.intf_name '
         || ' and a.direction=b.direction and a.status="I" and '
         || ' a.intf_name = "'
         || p_intf_name
         || '" AND a.direction = "'
         || p_direction
         || '" AND a.event_code = "'
         || p_event_code
         || '"';

      UPDATE intf_queues
         SET status = 'I'
       WHERE intf_name = p_intf_name
         AND direction = p_direction
         AND event_code = p_event_code
         AND status = 'Q';

      COMMIT;
      DBMS_SQL.parse (v_cur, REPLACE (l_stmt, '"', ''''), DBMS_SQL.native);
      DBMS_SQL.describe_columns (v_cur, l_cnt, v_desc_tab);

      FOR i IN 1 .. l_cnt
      LOOP
         DBMS_SQL.define_column (v_cur, i, l_val, 4000);
      END LOOP;

      l_status := DBMS_SQL.EXECUTE (v_cur);

      WHILE (DBMS_SQL.fetch_rows (v_cur) > 0)
      LOOP
         BEGIN
            FOR i IN 1 .. l_cnt
            LOOP
               DBMS_SQL.column_value (v_cur, i, l_val);

               IF i = 1
               THEN
                  v_queue_token := l_val;
               END IF;

               IF i > 2
               THEN
                  IF v_file_type = 'S'
                  THEN                                       --Separator file
                     UTL_FILE.putf (v_file, '%s', v_separator);
                  END IF;
               END IF;

               IF i > 1
               THEN
                  UTL_FILE.putf (v_file, '%s', l_val);
               END IF;
            END LOOP;

            UPDATE intf_queues
               SET status = 'C',
                   error_message = 'Complete'
             WHERE queue_token = v_queue_token
               AND intf_name = p_intf_name
               AND direction = p_direction
               AND event_code = p_event_code
               AND status = 'I';

            UPDATE intf_detail
               SET total_record = total_record + 1,
                   success_record = success_record + 1
             WHERE intf_name = p_intf_name
               AND direction = p_direction
               AND event_code = p_event_code
               AND exec_seq = v_seq;
         EXCEPTION
            WHEN OTHERS
            THEN
               v_error_message := SUBSTR (SQLERRM, 1000);

               UPDATE intf_queues
                  SET status = 'E',
                      error_message = v_error_message
                WHERE queue_token = v_queue_token
                  AND intf_name = p_intf_name
                  AND direction = p_direction
                  AND event_code = p_event_code
                  AND status = 'I';

               UPDATE intf_detail
                  SET total_record = total_record + 1,
                      failed_record = failed_record + 1
                WHERE intf_name = p_intf_name
                  AND direction = p_direction
                  AND event_code = p_event_code
                  AND exec_seq = v_seq;

               COMMIT;
         END;

         COMMIT;
         UTL_FILE.new_line (v_file);
      END LOOP;

      DBMS_SQL.close_cursor (v_cur);
      UTL_FILE.fclose (v_file);
   END IF;

   UPDATE intf_detail
      SET end_date = SYSDATE,
          status = 'C'
    WHERE intf_name = p_intf_name
      AND direction = p_direction
      AND event_code = p_event_code
      AND exec_seq = v_seq;

   COMMIT;
EXCEPTION
   WHEN UTL_FILE.invalid_mode
   THEN
      raise_application_error (-20051, 'Invalid Mode Parameter');
   WHEN UTL_FILE.invalid_path
   THEN
      raise_application_error (-20052, 'Invalid File Location');
   WHEN UTL_FILE.invalid_filehandle
   THEN
      raise_application_error (-20053, 'Invalid Filehandle');
   WHEN UTL_FILE.invalid_operation
   THEN
      raise_application_error (-20054, 'Invalid Operation');
   WHEN UTL_FILE.read_error
   THEN
      raise_application_error (-20055, 'Read Error');
   WHEN UTL_FILE.internal_error
   THEN
      raise_application_error (-20057, 'Internal Error');
   WHEN UTL_FILE.charsetmismatch
   THEN
      raise_application_error
                        (-20058,
                         'Opened With FOPEN_NCHAR But Later I/O Inconsistent'
                        );
   WHEN UTL_FILE.file_open
   THEN
      raise_application_error (-20059, 'File Already Opened');
   WHEN UTL_FILE.invalid_maxlinesize
   THEN
      raise_application_error (-20060, 'Line Size Exceeds 32K');
   WHEN UTL_FILE.invalid_filename
   THEN
      raise_application_error (-20061, 'Invalid File Name');
   WHEN UTL_FILE.access_denied
   THEN
      raise_application_error (-20062, 'File Access Denied By');
   WHEN UTL_FILE.invalid_offset
   THEN
      raise_application_error (-20063, 'FSEEK Param Less Than 0');
   WHEN OTHERS
   THEN
      raise_application_error (-20099, 'Unknown UTL_FILE Error' || SQLERRM);
END;

Re: Generate the file having dynamic columns [message #299524 is a reply to message #299479] Tue, 12 February 2008 05:43 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you!
Previous Topic: Julian date Convesion
Next Topic: show in one line
Goto Forum:
  


Current Time: Tue Dec 06 06:13:36 CST 2016

Total time taken to generate the page: 0.36891 seconds