Solved

Output File not correct

Posted on 2014-03-20
8
397 Views
Last Modified: 2014-04-09
I have a procedure that creates an output file using UTL in 10G
It goes out the way is is supposed to.

When I run the same procedure in 11G it does not.

It isn't formatting 9 fields correctly

I have attached the code

the fields not formating corrected are DIAG1 to DIAG9

In 10 g they go out as 6 chars.
In 11g they go out as 10 chars

the field length in the Oracle table is VARCHAR2(10 bytes)

   PROCEDURE sp_create_gen_file (
      p_out_error_flag      OUT VARCHAR2,
      p_out_error_msg       OUT VARCHAR2,
      p_claim_type       IN     VARCHAR2,
      p_passed_query     IN     VARCHAR2 DEFAULT NULL,
      p_filename         IN     VARCHAR2 DEFAULT NULL,
      p_stamp            IN     NUMBER DEFAULT NULL,
      p_delim            IN     VARCHAR2 DEFAULT NULL,
      p_column_names     IN     NUMBER DEFAULT 0,
      p_limit_from       IN     NUMBER,
      p_limit_to         IN     NUMBER,
      p_claim_source     IN     VARCHAR2)
   IS
      v_output_ext     VARCHAR2 (4) := '.txt';
      v_open_type      VARCHAR2 (1) := 'W';
      v_column_names   PLS_INTEGER;
      v_delim          VARCHAR2 (1) := '|';
      v_file_name      VARCHAR2 (80) := 'file';
      v_file_path      VARCHAR2 (80);
      v_file           UTL_FILE.file_type;
      v_data_flag      PLS_INTEGER := 0;
      v_select         VARCHAR2 (8) := 'select ';
      v_hint           VARCHAR2 (1000) := '';
      v_distinct       VARCHAR2 (9) := '';
      v_from           VARCHAR2 (8000) := '';
      v_cursor         PLS_INTEGER DEFAULT DBMS_SQL.open_cursor;
      v_desc           DBMS_SQL.desc_tab;
      v_desc2          DBMS_SQL.desc_tab;
      v_column_value   VARCHAR2 (32767);
      v_status         PLS_INTEGER;
      v_col_cnt        PLS_INTEGER DEFAULT 0;
      v_col_cnt2       PLS_INTEGER DEFAULT 0;
      v_separator      VARCHAR2 (10) DEFAULT '';
      v_query          VARCHAR2 (32767) := 'select * from ';
   BEGIN
      debug_log (p_filename);

      -- Full Query or Table Query and determine output filename
      IF TRIM (p_passed_query) IS NOT NULL
      THEN
         IF INSTR (TRIM (p_passed_query), ' ') > 0
         THEN
            v_query := p_passed_query;

            IF TRIM (p_filename) IS NOT NULL
            THEN
               IF p_stamp IS NOT NULL
               THEN
                  v_file_name :=
                        p_filename
                     || TO_CHAR (SYSDATE, '_MMDDYYYY_HH24MISS')
                     || v_output_ext;
               ELSE
                  IF INSTR (p_filename, '.') = 0
                  THEN
                     v_file_name := p_filename || v_output_ext;
                  ELSE
                     v_file_name := p_filename;
                  END IF;
               END IF;
            ELSE
               IF p_stamp IS NOT NULL
               THEN
                  v_file_name :=
                        v_file_name
                     || TO_CHAR (SYSDATE, '_MMDDYYYY_HH24MISS')
                     || v_output_ext;
               ELSE
                  v_file_name := v_file_name || v_output_ext;
               END IF;
            END IF;
         ELSE
            v_query := v_query || p_passed_query;

            IF TRIM (p_filename) IS NOT NULL
            THEN
               IF p_stamp IS NOT NULL
               THEN
                  v_file_name :=
                        p_filename
                     || TO_CHAR (SYSDATE, '_MMDDYYYY_HH24MISS')
                     || v_output_ext;
               ELSE
                  IF INSTR (p_filename, '.') = 0
                  THEN
                     v_file_name := p_filename || v_output_ext;
                  ELSE
                     v_file_name := p_filename;
                  END IF;
               END IF;
            ELSE
               IF p_stamp IS NOT NULL
               THEN
                  v_file_name :=
                        v_file_name
                     || '_'
                     || p_passed_query
                     || TO_CHAR (SYSDATE, '_MMDDYYYY_HH24MISS')
                     || v_output_ext;
               ELSE
                  v_file_name :=
                     v_file_name || '_' || p_passed_query || v_output_ext;
               END IF;
            END IF;
         END IF;
      END IF;

      -- New delimeter
      IF LENGTH (TRIM (p_delim)) = 1
      THEN
         v_delim := p_delim;
      END IF;

      -- New column names in the first row defaults to true
      v_column_names := p_column_names;
      -- Get hints
      v_hint :=
         SUBSTR (v_query,
                 INSTR (v_query, '/*'),
                 INSTR (v_query, '*/', -1) - INSTR (v_query, '/*'));

      IF LENGTH (v_hint) > 0
      THEN
         v_hint := v_hint || '*/';
      END IF;

      -- Get distinct
      IF (INSTR (UPPER (v_query), 'DISTINCT') > 0)
      THEN
         v_distinct := 'distinct ';
      END IF;

      -- Get from/where clauses
      -- v_from := SUBSTR (v_query,INSTR (v_query, 'FROM'),INSTR(v_query,' ',INSTR (v_query, 'WHERE')+1)-INSTR (v_query, 'FROM')-1);
      v_from := SUBSTR (v_query, INSTR (v_query, ' FROM '));
      --debug_log (v_from);
      -- Parse the query
      DBMS_SQL.parse (v_cursor, v_query, DBMS_SQL.native);
      -- Get the column count, and column info
      DBMS_SQL.describe_columns (v_cursor, v_col_cnt, v_desc);
      -- Reformat the query
      v_query := v_select || v_hint || v_distinct;
      v_separator := '';

      FOR i IN 1 .. v_col_cnt
      LOOP
         v_query :=
               v_query
            || v_separator
            || 'RPAD('
            || v_desc (i).col_name
            || ' , '
            || v_desc (i).col_max_len
            || ','' '')';
         v_separator := '||''' || v_delim || '''||';
      END LOOP;

      --debug_log (v_query);
      v_query := v_query || ' as output_line ' || v_from;
      --dbms_output.put_line('v_from :' || v_from);
      -- Parse the query
      DBMS_SQL.parse (v_cursor, v_query, DBMS_SQL.native);
      -- Get the column count, and column info
      DBMS_SQL.describe_columns (v_cursor, v_col_cnt2, v_desc2);

      -- Define size for each column (as character)
      FOR i IN 1 .. v_col_cnt2
      LOOP
         DBMS_SQL.define_column (v_cursor,
                                 i,
                                 v_column_value,
                                 32767);
      END LOOP;

      -- Execute the query
      v_status := DBMS_SQL.EXECUTE (v_cursor);
      --10052009/Selvi - Commented the below lines --10G Migration change
      -- Open the file
      --      SELECT directory_path
      --        INTO v_file_path
      --        FROM all_directories
      --       WHERE directory_name = 'DATA_DIR';
      v_file :=
         UTL_FILE.fopen (v_output_dir,
                         v_file_name,
                         v_open_type,
                         32767);

      -- Write column names in the first row
      IF v_column_names <> 0
      THEN
         v_separator := '';
         v_query := '';

         FOR i IN 1 .. v_col_cnt
         LOOP
            v_query :=
                  v_query
               || v_separator
               || 'RPAD('
               || v_desc (i).col_name
               || ' , '
               || v_desc (i).col_max_len
               || ','' '')';                            --v_desc (i).col_name;
            v_separator := v_delim;
         END LOOP;

         UTL_FILE.put_line (v_file, v_query);
      END IF;

      -- Write the table data to the file
      IF v_file_name LIKE 'HALL%'
      THEN
         -- write header
         UTL_FILE.put_line (v_file, fn_get_claim_header (p_claim_type));
      END IF;

      LOOP
         EXIT WHEN (DBMS_SQL.fetch_rows (v_cursor) <= 0);
         v_data_flag := 1;

         FOR i IN 1 .. v_col_cnt2
         LOOP
            DBMS_SQL.COLUMN_VALUE (v_cursor, i, v_column_value);
            UTL_FILE.put_line (v_file, REPLACE (v_column_value, '|', ''));
         END LOOP;
      END LOOP;

      DBMS_SQL.close_cursor (v_cursor);

      IF v_file_name LIKE 'HALL%'
      THEN
         -- write trailer
         UTL_FILE.put_line (v_file,
                            fn_get_claim_trailer_new (p_claim_type, -- hs 10/13/2011--
                                                      p_limit_from,
                                                      p_limit_to,
                                                      p_claim_source));
      END IF;

      UTL_FILE.fclose (v_file);
   EXCEPTION

the v_from = 
      v_from :=
            ' SELECT '
         || ' RECORD_TYPE, CLAIM_TYPE, PATIENT_ACCOUNT_NUMBER, '
         || '    PROVIDER_MAID_NO, FILLER1, BILLING_PROVIDER_TAX_ID, '
         || '   MEMBER_LAST_NAME, MEMBER_FIRST_NAME, MEMBER_MIDDLE_INITIAL,'
         || '   CAP_FFS_INDICATOR, STATEMENT_FROM_DATE, STATEMENT_TO_DATE,'
         || '   PATIENT_STATUS, MEMBER_MAID, ATTENDING_NPI, '
         /*061808/SELVI/ filler2 modified as--attending_npi-- Claims file Layout Change*/
         || '   TYPE_OF_BILL, PAYMENT_METHOD, COB_PAID_AMOUNT,'
         || '   ADJUSTMENT_CODES, DRG, REVENUE_CODE, '
         || '   PROCEDURE_CODE, PROCEDURE_MODIFIER, PROCEDURE_MODIFIER2,'
         || '   PROCEDURE_MODIFIER3, QUANTITY, PLACE_OF_SERVICE, '
         || '   BILL_AMOUNT, BILLING_PROVIDER_NPI, BILLING_PROVIDER_ZIP, '
         || '   BILLING_PROVIDER_TAXONOMY, RENDERING_PROVIDER_NPI, Rendering_Provider_Taxonomy, '
         --FILLER3,'
         || '   NDC_CODE, substr(DIAG1,1,6), substr(DIAG2,1,6),'
         || '   substr(DIAG3,1,6), substr(DIAG4,1,6), substr(DIAG5,1,6), '
         || '   substr(DIAG6,1,6), substr(DIAG7,1,6), substr(DIAG8,1,6), '
         || '   substr(DIAG9,1,6), EPSDT_INDICATOR, PREGNANCY_INDICATOR, '
         || '   EMERGENCY_INDICATOR, POA_DIAGNOSIS, FILLER5, DELIVERY_SYSTEM, '
         --09282009/Selvi -- POA diagonosis -- File layout change
         || '   CLAIM_SOURCE, '
         --FILLER6,
         || '   BH_FLAG, '
         || '   RENDERING_PHYSICIAN_MAID_NO, '
         || '   EXPECTED_DUE_DATE, ADMISSION_TYPE, ADMISSION_DATE, '
         || '   DISCHARGE_DATE, REFERRING_PHYSICIAN_MAID_NO, MEMBER_DOB,'
         || '   MEMBER_GENDER, INPATIENT_DAYS, TOTAL_INPATIENT_DAYS, '
         || '   FILLER7, LINE_NUMBER_EXPANDED, LINE_ITERATION_EXPANDED,'
         || '   ALLOWED_AMOUNT, COB_BENEFIT_AMOUNT, APPROVED_UNITS, '
         || '   REFERRING_PROVIDER_NPI, CLAIM_STATUS, CLEAN_CLAIM_INDICATOR,'
         || '   LAST_MENSTRUAL_PERIOD , FILLER8, PRIMARY_INSURANCE, FILLER9, '
         --FILLER8
         || '   BILLING_PROVIDER_LOCATION, FILLER10, PMP_PROVIDER, '
         || '   PRINCIPLE_SURG_PROCEDURE_CODE, EDS_FLAG, CLAIM_RECEIVED_DATE,'
         || '   PAID_DATE, ORIGINAL_CLAIM_ID, PROCEDURE_DATE, '
         || '   SPECIALTY_CODE, IN_NETWORK_PROVIDER, DELIVERY_CLAIM_INDICATOR,'
         || '   CLAIMS_PAYER, ADMITTING_DIAGNOSIS, AUTHORIZATION_NUMBER,'
         || '   EDS_ICN, ICD9_SURGICAL_PROCEDURE_CODE2, ICD9_SURGICAL_PROCEDURE_CODE3,'
         || '   ICD9_SURGICAL_PROCEDURE_CODE4, ICD9_SURGICAL_PROCEDURE_CODE5,'
         -- Begin 07/08/10 LT  added all HIP fields
         || 'icd9_surgical_procedure_code6,icd9_surgical_procedure_code7,icd9_surgical_procedure_code8,'
         || 'icd9_surgical_procedure_code9,icd9_surgical_procedure_code10, '
         || '   ICD9_SURGICAL_PROC_CODE1_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE2_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE3_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE4_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE5_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE6_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE7_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE8_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE9_DATE,        '
         || '   ICD9_SURGICAL_PROC_CODE10_DATE,       '
         || '   PROVIDER_NAME,                        '
         || '   INTEREST_AMOUNT,                      '
         || '   PAYER_AUTHORIZATION_NUMBER,           '
         || '   CHECK_NUMBER,                         '
         || '   HIP_ER_COPAY_AMOUNT,                  '
         || '   HIP_POWER_ACCOUNT_AMOUNT,             '
         || '   HIP_POWER_ACCOUNT_ADJ_FLAG,           '
         || '   OCCURRENCE_CODE,                      '
         || '   OCCURRENCE_CODE_FROM_DATE,            '
         || '   OCCURRENCE_CODE_TO_DATE,              '
         || '   NDC_QTY,                              '
         || '   NDC_UNITS,                            '
         || '   PATIENT_NO,                           '
         || '   FILLER11                              '; 

Open in new window

0
Comment
Question by:timjshields
  • 3
  • 3
  • 2
8 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
looks like the substr(DIAG1,1,6) expressions retain the field's size (10), so you will presumably need to cast explicitly:
cast(substr(DIAG1,1,6) as varchar(6))

hope this helps
0
 

Author Comment

by:timjshields
Comment Utility
Is that something with 11g substr where it retains the field size?
It is not retaining the field size in 10g
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
comparing the docs:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions162.htm
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2101.htm#OLADM679
there is no "difference" documented in regards to the behavior you describe ...
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
Comment Utility
First confirm both columns in both databases are varchar2(10).

My guess is it has to do with col_max_len.  Any reason you are using RPAD to the maximum length?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:timjshields
Comment Utility
Both are Varchar1(10)
Whoever created the proc did it to format the output file to specs.
He may of done it in case of field size changes in the future.
What makes you think it might be the col_max_len?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
Comment Utility
>>What makes you think it might be the col_max_len?

col_max_length is likely the maximum allowed in the column.  This is probably 10.  I see an RPAD to that length.  I also see the substr calls but I haven't walked through the code line by line to see exactly what it is doing.

I would probably set up some debug statements using dbms_output.put_line at various places in the code to see where the 'bad' data is coming from.
0
 

Author Comment

by:timjshields
Comment Utility
Did that.    put out a whole lot of output lines. That is how I found out the RPAD was stringing it out to 10. Somehow IN 10g the fields are getting truncated but not in 11g.

I found another field that didn't format correctly

If the field was null make it a string using the NVL.
weel guess what that didn't work.
so the field was truncated all together thus throwing the format off.
If it found a non null value it working according.

Talk about weird.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
Comment Utility
>>the Oracle table is VARCHAR2(10 bytes)

I missed the bytes when I first read the question.  Are the character sets of both databases the same?

I wouldn't expect 6 characters in 10g but can easily see 5 it the 10g was a 2-byte character set and 11g is a 1-btye character set.

>>the RPAD was stringing it out to 10.

That is actually what I would have expected even with 10g.

I suppose that there might have been a 'feature/bug' in 10g?  Since 10g is no longer supported I doubt Oracle Support would be able to answer that for you.

>>weel guess what that didn't work.

NVL will convert a null to whatever you tell it to convert it to.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now