4 Replies Latest reply: Mar 2, 2012 9:05 AM by user275751 RSS

    Error: UTL_FILE.PUT_RAW

    user275751
      Hello

      I am trying to extract data from Oracle (DB: 10g) which has non-english charectors (Ex: âçïù), so when I use below piece of code I am getting below error. Can anybody let me know how to extract the non-english charectors using UTL_FILE?

      Error:ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error


      Code
      v_chr_line_record LONG;
      file := UTL_FILE.FOPEN (v_output_directory, v_output_file, 'w');
      UTL_FILE.PUT_RAW (file, v_chr_line_record);
      UTL_FILE.NEW_LINE (file);

      Output of below SQL: UTF8
      select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

      Thanks!!
        • 1. Re: Error: UTL_FILE.PUT_RAW
          JustinCave
          UTL_FILE.PUT_RAW accepts a RAW. v_chr_line_record is defined to be a LONG So you're not passing in the proper data type.

          Are you populating V_CHR_LINE_RECORD from some place in your code? If so, are you trying to populate it with a number? A string? Or a RAW?

          Justin

          Edited by: Justin Cave on Mar 1, 2012 5:51 PM
          • 2. Re: Error: UTL_FILE.PUT_RAW
            rp0428
            >
            I am trying to extract data from Oracle
            >
            What you posted doesn't show anything being extracted from Oracle.
            Please post the actual code and the actual error you are getting which will show what statement the error is on.

            Also you are using PUT_RAW to put a LONG.
            • 3. Re: Error: UTL_FILE.PUT_RAW
              user275751
              Hello

              when i put the data type as RAW instead of LONG it is giving compilation error. My issue is suppose i have cutomer PO Number which can have in Japaneese language and when my program completes then the Japaneese comming in .csv file as junk.

              I am using sql code and writing the data from Oracle Applications (11.5.10) base tables to a custom stagging table. From custom table I am writing the data in to my local variable v_chr_line_record. Please find code and i have edited some piece of code as system allowing me to post max 30000 charectors

              declare
              l_chr_delimitor VARCHAR2 (1) := '!';
              v_start_date DATE;
              v_chr_print_hdr_rec VARCHAR2 (1) := NULL;
              v_chr_hdr_line VARCHAR2 (2000) := NULL;
              v_chr_line_record LONG; blob;LONG;--CLOB;--LONG;
              v_num_sequence NUMBER := 0;
              v_output_file VARCHAR2 (1000);
              v_output_directory VARCHAR2 (1000) := i_chr_file_path;
              file UTL_FILE.FILE_TYPE;


              CURSOR cur_ser_inv
              IS
              SELECT ROWID ROW_ID, kti.*
              FROM KEI_TEK_SVC_INV_INF KTI
              WHERE process_flag IN ('E', 'N');

              l_num_records := 0;

              --Inserting the data in to the custom table
              SELECT hou.name field_office_name,
              ooh.order_number order_number,
              ooh.creation_date order_creation_date,
              t.name order_type,
              ooh.attribute4 ship_to_Contact_Name,
              ooh.attribute5 Bill_to_Contact_Name,
              (SELECT --replace(substrb(B.short_text,1,240),'!',' ')
              REPLACE (
              REPLACE (
              REPLACE (SUBSTRB (b.short_text, 1, 240),
              CHR (10),
              ' '),
              CHR (13),
              NULL
              ),
              '!',
              ' '
              )
              FROM fnd_attached_docs_form_vl A,
              fnd_documents_short_text B
              WHERE A.pk1_value = ooh.header_id
              AND A.function_name = 'OEXOEORD'
              AND A.category_description =
              'External Order Note'
              AND A.datatype_name = 'Short Text'
              AND A.media_id = B.media_id)
              header_notes,
              ool.line_number line_number,
              TO_CHAR (SYSDATE, 'mm/dd/yyyy') invoice_date,
              ool.attribute7 repair_number,
              DECODE (SIGN (ool.invoiced_quantity),
              '1',
              'Invoice',
              '-1',
              'Credit')
              inv_transaction_type,
              ooh.transactional_curr_code currency,
              ooh.cust_po_number customer_po_number,
              inv_ps.party_site_number bill_to_site_number,
              shp_ps.party_site_number ship_to_site_number,
              rt.name payment_term,
              NULL inv_method_code,
              (SELECT --replace(substrb(B.short_text,1,240),'!',' ')
              REPLACE (
              REPLACE (
              REPLACE (SUBSTRB (b.short_text, 1, 240),
              CHR (10),
              ' '),
              CHR (13),
              NULL
              ),
              '!',
              ' '
              )
              FROM FND_ATTACHED_DOCS_FORM_VL A,
              FND_DOCUMENTS_SHORT_TEXT B
              WHERE A.pk1_value = ooh.header_id
              AND A.function_name = 'OEXOEORD'
              AND A.CATEGORY_DESCRIPTION =
              'SPECIAL_HANDLING_INSTRUCTIONS'
              AND A.DATATYPE_NAME = 'Short Text'
              AND A.MEDIA_ID = B.MEDIA_ID)
              spl_handling_ins,
              msi.segment1 model,
              ool.attribute8 model_serial_number,
              ool.attribute9 repair_item_code,
              (SELECT b.description
              FROM fnd_flex_value_sets a, FND_FLEX_VALUES_VL b
              WHERE a.flex_value_set_name = 'KIOM_REPAIR_CODES'
              AND a.flex_value_set_id = b.flex_value_set_id
              AND b.flex_value = ool.attribute9)
              repair_item_description,
              ool.invoiced_quantity ordered_quantity,
              --
              ool.unit_selling_price,
              (SELECT REPLACE (
              REPLACE (
              REPLACE (SUBSTRB (b.short_text, 1, 150),
              CHR (10),
              ' '),
              CHR (13),
              NULL
              ),
              '!',
              ' '
              )
              FROM FND_ATTACHED_DOCS_FORM_VL A,
              FND_DOCUMENTS_SHORT_TEXT B
              WHERE A.pk1_value = ool.line_id
              AND A.function_name = 'OEXOEORD'
              AND A.CATEGORY_DESCRIPTION = 'LINE_COMMENT_1'
              AND A.DATATYPE_NAME = 'Short Text'
              AND A.MEDIA_ID = B.MEDIA_ID)
              LINE_COMMENT_1,
              NULL line_comment_2, -- (this has a very specific usage at Tek)
              (SELECT REPLACE (
              REPLACE (
              REPLACE (SUBSTRB (b.short_text, 1, 150),
              CHR (10),
              ' '),
              CHR (13),
              NULL
              ),
              '!',
              ' '
              )
              FROM FND_ATTACHED_DOCS_FORM_VL A,
              FND_DOCUMENTS_SHORT_TEXT B
              WHERE A.pk1_value = ool.line_id
              AND A.function_name = 'OEXOEORD'
              AND A.CATEGORY_DESCRIPTION = 'LINE_COMMENT_3'
              AND A.DATATYPE_NAME = 'Short Text'
              AND A.MEDIA_ID = B.MEDIA_ID)
              LINE_COMMENT_3,
              DECODE (
              hou.name,
              'KI-US',
              NULL,
              (DECODE (ool.tax_code, 'EXEMPT', ool.tax_code, NULL))
              )
              tax_code,
              ool.line_id line_id,
              ooh.header_id header_id,
              ool.inventory_item_id inventory_item_id,
              ooh.org_id org_id,
              NVL (ool.attribute6, 'N') repair_model_flag,
              'N' process_flag,
              NULL error_description,
              l_num_user_id created_by,
              SYSDATE creation_date,
              l_num_user_id last_updated_by,
              SYSDATE last_update_date,
              l_num_login_id last_update_login,
              l_num_request_id request_id,
              NULL omar_inv_number,
              NULL omar_inv_trx_date,
              'N' kei_order_updated,
              i_chr_resend,
              (SELECT incident_id
              FROM CS_INCIDENTS_ALL_B
              WHERE incident_number = ool.attribute7)
              incident_id,
              (SELECT s.set_name
              FROM oe_sets s,
              oe_line_sets ls,
              oe_order_headers_all oh
              WHERE s.set_id = ls.set_id
              AND s.set_type = 'FULFILLMENT_SET'
              AND s.header_id = oh.header_id
              AND oh.header_id = ooh.header_id
              AND ls.line_id = ool.line_id)
              fulfillment_set_no
              FROM hz_party_sites shp_ps,
              hz_party_sites inv_ps,
              hz_cust_acct_sites_all shp_sa,
              hz_cust_acct_sites_all inv_sa,
              hz_cust_accounts_all shp_ac,
              -- hz_cust_accounts_all inv_ac,
              hz_cust_site_uses_all shp_su,
              hz_cust_site_uses_all inv_su,
              hz_locations shp_loc,
              hz_locations inv_loc,
              hz_parties hz,
              oe_order_headers_all ooh,
              oe_order_lines_all ool,
              mtl_system_items_b msi,
              ra_terms rt,
              OE_TRANSACTION_TYPES_TL T,
              OE_TRANSACTION_TYPES_ALL OTT,
              hr_operating_units hou
              WHERE inv_su.site_use_id = ooh.invoice_to_org_id
              AND shp_su.site_use_id = ooh.ship_to_org_id
              AND inv_sa.cust_acct_site_id = inv_su.cust_acct_site_id
              AND shp_sa.cust_acct_site_id = shp_su.cust_acct_site_id
              AND inv_ps.party_site_id = inv_sa.party_site_id
              AND shp_ps.party_site_id = shp_sa.party_site_id
              AND inv_ps.location_id = inv_loc.location_id
              AND shp_ps.location_id = shp_loc.location_id
              AND shp_ps.party_id = hz.party_id
              AND shp_sa.cust_account_id = shp_ac.CUST_ACCOUNT_ID
              --
              AND ool.header_id = ooh.header_id
              AND ( (ool.line_category_code IN ('ORDER')
              AND t.name NOT LIKE 'Credit%')
              OR (ool.line_category_code NOT IN ('ORDER')
              AND t.name LIKE 'Credit%'))
              AND ool.inventory_item_id = msi.inventory_item_id
              AND msi.organization_id = 84
              AND ool.payment_term_id = rt.term_id
              AND ooh.order_type_id = ott.transaction_type_id
              AND OTT.TRANSACTION_TYPE_ID = T.TRANSACTION_TYPE_ID
              AND T.LANGUAGE = USERENV ('LANG')
              AND ( t.name LIKE 'Invoice Only%'
              OR t.name LIKE 'Repair%'
              OR t.name LIKE 'Credit%')
              AND ooh.org_id = hou.organization_id
              AND ool.flow_status_code IN ('CLOSED')
              -- AND ooh.order_number IN (1232360)
              AND ooh.order_number =
              NVL (i_num_order_number, ooh.order_number)
              AND ( (t.name NOT LIKE 'Credit%'
              AND NVL (
              (ool.invoiced_quantity
              * ool.unit_selling_price),
              0
              ) > 0)
              OR (t.name LIKE 'Credit%'
              AND NVL (
              (ool.invoiced_quantity
              * ool.unit_selling_price),
              0
              ) < 0))
              AND TRUNC (ool.last_update_date) =
              NVL (i_chr_current_date, TRUNC (SYSDATE - 1))
              AND ( (i_chr_resend = 'Y'
              AND EXISTS
              (SELECT '1'
              FROM KEI_TEK_SVC_INV_INF kts
              WHERE ooh.order_number = kts.order_number
              AND ool.line_id = kts.line_id
              AND ROWNUM = 1))
              OR (i_chr_resend = 'N'
              AND NOT EXISTS
              (SELECT '1'
              FROM KEI_TEK_SVC_INV_INF kts
              WHERE ooh.order_number =
              kts.order_number
              AND ool.line_id = kts.line_id
              AND ROWNUM = 1)))
              ORDER BY ooh.order_number, ool.line_id;

              EXCEPTION
              WHEN OTHERS
              THEN NULL END;






              --Extract File Header
              BEGIN
              v_chr_hdr_line :=
              'FIELD_OFFICE_NAME'
              || l_chr_delimitor
              || 'INVOICE_REF'
              || l_chr_delimitor
              || 'LINE_NUMBER'
              || l_chr_delimitor
              || 'INVOICE_DATE'
              || l_chr_delimitor
              || 'REPAIR_NUMBER'
              || l_chr_delimitor
              || 'TRANSACTION_TYPE'
              || l_chr_delimitor
              || 'CURRENCY'
              || l_chr_delimitor
              || 'CUSTOMER_PO_NUMBER'
              || l_chr_delimitor
              || 'CUSTOMER_ADDRESS_BILL_TO'
              || l_chr_delimitor
              || 'BILL_TO_CONTACT_NAME'
              || l_chr_delimitor
              || 'CUSTOMER_ADDRESS_SHIP_TO'
              || l_chr_delimitor
              || 'SHIP_TO_CONTACT_NAME'
              || l_chr_delimitor
              || 'PAYMENT_TERM'
              || l_chr_delimitor
              || 'HEADER_NOTES'
              || l_chr_delimitor
              || 'INVOICE_METHOD_CODE'
              || l_chr_delimitor
              || 'SPECIAL_HANDLING_INSTRUCTIONS'
              || l_chr_delimitor
              || --Line Level Details
              'MODEL'
              || l_chr_delimitor
              || 'SERIAL_NUMBER'
              || l_chr_delimitor
              || 'REPAIR_ITEM_CODE'
              || l_chr_delimitor
              || 'REPAIR_ITEM_DESCRIPTION'
              || l_chr_delimitor
              || 'QUANTITY'
              || l_chr_delimitor
              || 'UNIT_SELLING_PRICE'
              || l_chr_delimitor
              || 'LINE_COMMENT_1'
              || l_chr_delimitor
              || -- LINE_COMMENT_2' ||l_chr_delimitor||
              'LINE_COMMENT_3'
              || l_chr_delimitor
              || 'TAX_CODE';
              EXCEPTION
              WHEN OTHERS
              THEN
              END;



              fnd_file.put_line (fnd_file.LOG, 'Opening Cursor to process records... ');

              v_chr_print_hdr_rec := 'Y';
              v_num_count := 0;

              BEGIN
              IF l_chr_err_flag <> 'Y'
              THEN
              FOR cur_ser_inv_rec IN cur_ser_inv
              LOOP

              BEGIN
              v_output_file :=
              'KII_'
              || TO_CHAR (SYSDATE, 'YYYYMMDDHHMI')
              || '_'
              || v_num_sequence
              || '.csv';
              EXCEPTION
              WHEN OTHERS
              THEN
              fnd_file.put_line (
              fnd_file.LOG,
              'Error while formatting the output file name- '
              || SQLERRM
              );
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              END;

              BEGIN
              file :=
              UTL_FILE.FOPEN (v_output_directory, v_output_file, 'w');
              --UTL_FILE.FOPEN_NCHAR (v_output_directory, v_output_file, 'w');
              EXCEPTION
              WHEN UTL_FILE.INVALID_PATH
              THEN
              RAISE_APPLICATION_ERROR (-20100, 'Invalid Path');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.INVALID_MODE
              THEN
              RAISE_APPLICATION_ERROR (-20101, 'Invalid Mode');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.INVALID_FILEHANDLE
              THEN
              RAISE_APPLICATION_ERROR (-20102, 'Invalid Filehandle');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.INVALID_OPERATION
              THEN
              RAISE_APPLICATION_ERROR (
              -20103,
              'Invalid Operation -- May signal a file locked by the OS'
              );
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.READ_ERROR
              THEN
              RAISE_APPLICATION_ERROR (-20104, 'Read Error');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.WRITE_ERROR
              THEN
              RAISE_APPLICATION_ERROR (-20105, 'Write Error');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN UTL_FILE.INTERNAL_ERROR
              THEN
              RAISE_APPLICATION_ERROR (-20106, 'Internal Error');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN NO_DATA_FOUND
              THEN
              RAISE_APPLICATION_ERROR (-20107, 'No Data Found');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN VALUE_ERROR
              THEN
              RAISE_APPLICATION_ERROR (-20108, 'Value Error');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              WHEN OTHERS
              THEN
              RAISE_APPLICATION_ERROR (-20109,
              'Unknown UTL_FILE Error');
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              END;

              fnd_file.put_line (fnd_file.output, v_chr_hdr_line);
              UTL_FILE.PUTF (file, '%s', v_chr_hdr_line);
              --UTL_FILE.PUT_NCHAR (file, v_chr_hdr_line);
              UTL_FILE.NEW_LINE (file);

              v_chr_print_hdr_rec := 'N';
              END IF;


              IF l_chr_err_flag <> 'Y'
              THEN
              BEGIN
              IF cur_ser_inv_rec.repair_model_flag = 'N' --and cur_ser_inv_rec.order_type like 'Repair%'
              THEN
              cur_ser_inv_rec.model := cur_ser_inv_rec.non_model;
              cur_ser_inv_rec.model_serial_number :=
              cur_ser_inv_rec.non_model_serial_number;
              END IF;


              v_chr_line_record :=
              cur_ser_inv_rec.field_office_name
              || l_chr_delimitor
              || cur_ser_inv_rec.order_number
              || '-'
              || cur_ser_inv_rec.request_id
              || l_chr_delimitor
              || cur_ser_inv_rec.line_number
              || l_chr_delimitor
              || cur_ser_inv_rec.invoice_date
              || l_chr_delimitor
              || --cur_ser_inv_rec.repair_number ||l_chr_delimitor||
              cur_ser_inv_rec.order_number
              || l_chr_delimitor
              || cur_ser_inv_rec.inv_transaction_type
              || l_chr_delimitor
              || cur_ser_inv_rec.currency
              || l_chr_delimitor
              || cur_ser_inv_rec.customer_po_number
              || l_chr_delimitor
              || cur_ser_inv_rec.bill_to_site_number
              || l_chr_delimitor
              || cur_ser_inv_rec.bill_to_contact_name
              || l_chr_delimitor
              || cur_ser_inv_rec.ship_to_site_number
              || l_chr_delimitor
              || cur_ser_inv_rec.ship_to_contact_name
              || l_chr_delimitor
              || cur_ser_inv_rec.payment_term
              || l_chr_delimitor
              || --l_chr_short_text                     ||l_chr_delimitor||
              cur_ser_inv_rec.header_notes
              || l_chr_delimitor
              || cur_ser_inv_rec.inv_method_code
              || l_chr_delimitor
              || cur_ser_inv_rec.spl_handling_ins
              || l_chr_delimitor
              || --
              cur_ser_inv_rec.model
              || l_chr_delimitor
              || cur_ser_inv_rec.model_serial_number
              || l_chr_delimitor
              || cur_ser_inv_rec.repair_item_code
              || l_chr_delimitor
              || cur_ser_inv_rec.repair_item_description
              || l_chr_delimitor
              || cur_ser_inv_rec.ordered_quantity
              || l_chr_delimitor
              || cur_ser_inv_rec.unit_selling_price
              || l_chr_delimitor
              || cur_ser_inv_rec.line_comment_1
              || l_chr_delimitor
              || -- cur_ser_inv_rec.line_comment_2||l_chr_delimitor||
              cur_ser_inv_rec.line_comment_3
              || l_chr_delimitor
              || cur_ser_inv_rec.tax_code;

              fnd_file.put_line (fnd_file.output, v_chr_line_record);

              --UTL_FILE.PUTF (file, '%s', v_chr_line_record);
              --UTL_FILE.PUTF (file, '%s', CONVERT(v_chr_line_record,'UTF8'));              
              --UTL_FILE.PUT_RAW (file, HEXTORAW(v_chr_line_record));
              --UTL_FILE.PUT_RAW (file, utl_raw.cast_to_raw(v_chr_line_record));             
              --UTL_FILE.PUT_NCHAR (file, v_chr_line_record);


              UTL_FILE.PUT_RAW (file, v_chr_line_record);




              UTL_FILE.NEW_LINE (file);
              v_num_count := v_num_count + 1;
              EXCEPTION
              WHEN OTHERS
              THEN
              fnd_file.put_line (
              fnd_file.LOG,
              'Error while writing order_line_id information into output file: '
              || cur_ser_inv_rec.line_id
              || ' Error:'
              || SQLERRM
              );
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              END;

              END IF;
              END LOOP;
              ELSE
              FND_FILE.PUT_LINE (
              fnd_file.LOG,
              '**** Found error, so no records processed *****'
              );
              END IF;

              COMMIT;
              EXCEPTION
              WHEN OTHERS
              THEN
              fnd_file.put_line (
              fnd_file.LOG,
              'Error while writing in to the output file- ' || SQLERRM
              );
              l_chr_err_flag := 'Y';
              RAISE custom_exception;
              END;



              IF v_num_count > 0
              THEN
              UTL_FILE.FCLOSE (file);

              FND_FILE.PUT_LINE (
              fnd_file.LOG,
              'Total: '
              || v_num_count
              || ' records written on to the output file....'
              );

              ELSE
              FND_FILE.PUT_LINE (fnd_file.LOG,
              '**** No records are fetched and processed *****');
              END IF;







              EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
              NULL;
              WHEN OTHERS
              THEN
              fnd_file.put_line (
              fnd_file.LOG,
              'Error while Updating OMAR TRX Number on Service Request base table '
              || '. Error: '
              || SQLERRM
              );
              l_chr_err_flag := 'Y';
              -- RAISE custom_exception;
              END;

              COMMIT;
              EXCEPTION
              WHEN custom_exception
              THEN
              fnd_file.put_line (fnd_file.LOG,
              '****** Custom Exception: ' || SQLERRM);
              ROLLBACK;
              o_ret_code := 1;
              WHEN OTHERS
              THEN
              fnd_file.put_line (fnd_file.LOG, '****** Unknown Error: ' || SQLERRM);
              ROLLBACK;
              o_ret_code := 1;
              END KEI_TEK_SERVICE_INV_PRC;
              /

              SHO ERR;

              Edited by: 856311 on Mar 1, 2012 3:11 PM
              • 4. Re: Error: UTL_FILE.PUT_RAW
                user275751
                Hello

                I got the solution, I have to use below command to convert the information in to RAW data...

                UTL_FILE.PUT_RAW (file,
                UTL_RAW.cast_to_raw (v_chr_line_record));

                Thanks!!