Today's Messages (off)  | Unanswered Messages (on)

Forum: Server Utilities
 Topic: External Table with Tab Delimiter Loads Data Wrong
External Table with Tab Delimiter Loads Data Wrong [message #678931] Wed, 22 January 2020 10:45
Messages: 457
Registered: December 2002
Senior Member
First off, I'm unable to provide the data so the data can be tested. No way for me to provide the data to an open forum like this.

The data is Tab Delimited with double quotations around any string data. Numeric data does not have any double quotations.

The problem: I believe one or more of the columns in the data are being shifted to the left which causes an "invalid number" error. I read a post where someone had a problem with an External Table where they thought that Oracle interprets two subsequent tabs incorrectly as one tab. That would make sense since the data is shifted to the left by one or more columns. Data from other columns are ending up in a different column. I think it might have something to do with there is no data for that column and there are subsequent tabs so Oracle gets confused.

As a test I made all the columns VARCHAR2 and I'm able to load the data using an External Table. The problem is after the "primary person last name" column, the data is incorrect for the rest of the columns. Data is ending up in columns that should not contain that data. Throwing the data into a table and exporting the data with double quotations around all the columns with a tab delimiter also loads the data correctly in the External Table. It's only when the data has double quotations around strings and numeric data does not does the data not load correctly.

Is there any work around for when Oracle interprets two subsequent tabs incorrectly as one tab?

  affiliation                    number,
  account_number                 number,
  letter_salutation              varchar2(50 byte),
  sequence_name                  varchar2(16 byte),
  envelope_salutation            varchar2(50 byte),
  company_name                   varchar2(50 byte),
  address_line_1                 varchar2(50 byte),
  address_line_2                 varchar2(50 byte),
  city                           varchar2(50 byte),
  state_or_province              varchar2(30 byte),
  zip_code                       varchar2(20 byte),
  zip_4                          number,
  telephone_number               number,
  alternate_id                   varchar2(11 byte),
  source_code                    varchar2(8 byte),
  pledge_amount                  number,
  pledge_date                    date,
  mode_of_payment                number,
  benefit_code                   varchar2(6 byte),
  premium_code                   varchar2(8 byte),
  premium_size_code              varchar2(4 byte),
  premium_description            varchar2(30 byte),
  pledge_amount_paid_to_date     number,
  pledge_type                    varchar2(1 byte),
  fund_code                      varchar2(5 byte),
  number_of_pledge_years         number,
  total_market_value             number,
  payment_date                   date,
  payment_amount                 number,
  cash_receipt_number            number,
  gl_account_number              number,
  payment_reference              varchar2(30 byte),
  payment_market_value           number,
  email_address                  varchar2(60 byte),
  major_donor_indicator          varchar2(3 byte),
  pledge_id                      number,
  payment_id                     number,
  primary_person_first_name      varchar2(30 byte),
  primary_person_middle_name     varchar2(30 byte),
  primary_person_last_name       varchar2(50 byte),
  hard_credit_affiliation        number,
  hard_credit_account_number     number,
  hard_credit_alternate_id       varchar2(11 byte),
  hard_credit_envelope_sal       varchar2(50 byte),
  hard_credit_company_name       varchar2(50 byte),
  hard_credit_address_line_1     varchar2(50 byte),
  hard_credit_address_line_2     varchar2(50 byte),
  hard_credit_city               varchar2(50 byte),
  hard_credit_state_or_province  varchar2(30 byte),
  hard_credit_zipcode            varchar2(20 byte),
  hard_credit_zip4               number,
  hard_credit_telephone          number,
  refund_payment_id              number,
  refund_crr_number              number,
  country                        varchar2(3 byte),
  country_name                   varchar2(50 byte),
  country_code_2                 varchar2(2 byte),
  hard_credit_country            varchar2(3 byte),
  hard_credit_country_name       varchar2(50 byte),
  hard_credit_country_code_2     varchar2(2 byte),
  shipping_amount                number,
  shipping_paid_to_date          number
       ( records delimited by newline
         badfile test_file_directory: 'test_upload_bad_file.bad'
         logfile test_file_directory: 'test_upload_log_file.log'
         skip 1 
         fields terminated by 0X'09' optionally enclosed by '"' lrtrim 
         missing field values are null
         date_format date mask "yyyymmdd"
     LOCATION (TEST_FILE_DIRECTORY:'3786620_1 12.18.19.txt')

Forum: Forms
 Topic: pdf file opening
pdf file opening [message #678879] Sat, 18 January 2020 15:20
Messages: 32
Registered: December 2009

Some pdf files located in server path '/u01/app' and file name '/u01/app/65845.pdf'

I want to open the pdf file from client by using web.show_document


Any one help me on this.

Thank in Advance.

Forum: Streams & AQ
 Topic: Remote JMS queue read
Remote JMS queue read [message #678886] Mon, 20 January 2020 04:15
Messages: 35
Registered: August 2012
Location: United Kingdom
I need to read from a JMS queue on a remote Oracle 11.2 database (think their server has weblogic but ours does not). I've been provided with a Host IP,Port,Connection Factory JNDI,Queue JNDI, and Queue Name but have no idea how to connect. I have checked that the network can see the IP address of the host ok. Do I need something like a database link to do this ? thanks.
Forum: Reports & Discoverer
 Topic: Report output to network location
Report output to network location [message #678814] Wed, 15 January 2020 09:36
Messages: 1
Registered: January 2020
Junior Member

I would like to generate report to network share. When I specify DESNAME='\\SERVER\DIR' it is throwing me an error:

"The report terminated with error:
REP-69: An internal error occurred org.omg.CORBA.UNKNOWN: ----------BEGIN server-side stack trace---------- org.omg.CORBA.UNKNOWN: vmcid: SUN minor code: 202 completed: Maybe at at ..."

I've added this location to rwserver.conf <write> tag.
I also granted full access to the share to the Reports server (by specifying Report's SERVERNAME$ in sharing options). This should give accsess to share to Local System user on Reports server on which Oracle Weblogic service is running.
I am running it by run_report_object() from Oracle Forms 12c.

What is missing or what is wrong in this solution?

Current Time: Wed Jan 22 18:01:35 CST 2020