SQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)
Jan 23, 2013
I have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.
The log file has the following errors:
KUP-04021: field formatting error for field DEPTNO KUP-04023: field start is after end of record KUP-04101: record 1 rejected in file /usr/tmpclie.csv error processing column EMPNO in row 2 for datafile /usr/tmpclie.csv ORA-01722: invalid number
Step 1.Insert all records from the external table into the export table. Truncate the export table first
Step 2.Read in a record from the export map table
Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table
Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line
Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.
first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET
WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1 BRANCH = 2B EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28 [code]....
THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK
CREATE TABLE EXT1 ( COL1 NVARCHAR2(2000), COL2 CLOB ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY FILE_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY '|' MISSING FIELD VALUES ARE NULL ( "COL1" CHAR, [code]....
From here I can count all records and the total number of records matching the file record count, but could not read the clob columns. it's not picking up the values. but if I take this out
( "COL1" CHAR, "COL2" RAW )
then I am missing the record counts.Also if you put all varchar and char, that also missing the count against the file.
I have a flat file on an unix system.The file is required to be loaded through Oracle External Table.
Issue: Not sure how to skip the first record when loading through Oracle External Table.How to suppress data while loading through External Table.
Requiremen syntax where in I can skip the first record. syntax for suppressing values in columns that are not required. How the same needs to be handled in case of Number datatype and Varchar2 datatype. Example - In case of Number can it be replaced with 0 and for datatype can be same be replaced with NULL.
My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.
I have question on the following, that gets defined for the bad file and the log file
BADFILE 'bad_%a_%p.bad' LOGFILE 'log_%a_%p.log'
What does the %a and % p indicate? Also if I wanted to get the value of %p and %a into a variable how would I do it? I want to be able to append %p and %a to the below variable, but unsure how to achieve it..
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I am new in external table so i have tried following cmd.
create directory dir_1 as 'E:ora_dirt' ; grant read, write on directory dir_1 to HR; select * from all_directories; create table emp_ext (emp_id number, emp_name varchar2(30)
[code]...
since I am not able to see DIR_1 in E: drive due to which i havnt created 'emp.dat' file and on executing select on external table i m geting expected error *"ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: EMP_ID"*
Encountering an issue with an Oracle external table. We get the following error when we load a particular file in this table:
ORA-12801: error signaled in parallel query server P000 ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached ORA-06512: at "SYS.ORACLE_LOADER", line 52
We have narrowed this down to a text field in the file that contains the following text (text obfuscated):
XXXXXXXX ł XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
This is nominally 40 characters long, which matches the maximum field size, but it is being rejected because it appears the 'ł' character is causing Oracle to interpret the length as 41 characters instead. If I remove a X then the file loads without issue.
We tried this in a new schema we created where we added the same table and used the same file. There was no problem at all. The oracle database has the following settings:
I'm experiencing some problems when trying to import an 50Mb XML file to an Oracle database. In this XML file I do have several data from customers:
- Name - Address - Contacts
I also have several tables within the DB that would receive this information:
- Customer - CustomerAddress - CustomerContacts
The problem is that, with my XML Transformation and correspondent insertion onto the databse I'm having an huge problem of time expended. I'm having more than 3 hours to insert over 180.000 records on those tables. what can I do to accelerate the process?
I need to do some analysis and research to find if any of these 8000 tables I have in a spreadheet that are going to be spit off into a separate database are used by any of our PeopleSoft processes.
I'm assigned to Student Records identified by NTSR.
So for table AAP_ETHNIC_PMPT, I could do something like
SELECT * FROM PSSQLTEXTDEFN where SQLID like 'NTSR%' AND upper(sqltext) like '%PS_AAP_ETHNIC_PMPT%';
But how can I automate this and search 8000 rows in column A2 of a spreadsheet? What other tables other than PSSQLTEXTDEFN or PSPROJECTITEM can I use to search for values of NTSR?
when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-
CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'
GRANT READ ON DIRECTORY dir_gms TO gopal; GRANT WRITE ON DIRECTORY dir_gms TO gopal;
New point: -- taking export thru expdb expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log then i created one EXTERNAL TABLE TO access it.
function isnumeric( p_string in varchar2) return boolean as l_number number; begin l_number := p_string; return TRUE; exception when others then return FALSE; end;
It is not recognizing function IS NUMERIC. Is there any way, I can do this through External Table.
I'm getting error like
The following error has occurred:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "identifier": expecting one of: "comma, date_format, defaultif, enclosed, (, ltrim, lrtrim, ldrtrim, notrim, nullif, optionally, ), rtrim, terminated" KUP-01008: the bad identifier was: isnumeric KUP-01007: at line 9 column 16 ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1
I have an external table. The table gets created successfully. Once the table is created when I try to access it, I get the following error :
ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error error opening file /cmpnt/dev/test/ADE_TEMP_4992.log
The default directory is valid and does not have any issues. The IP address of my DB server and the server from which I am connecting to the DB are different. Is this is the issue ? However , all SQL queries are working fine except this one.
i had small query on external tables. i had (.csv) file outside the database. In this file, one column will be added monthly. i need to create the external table dynamically by adding the column for every month.
How the procedure can be created for this requirement.
then it gets " select * from ext_schoolof " BUT when i use procedure, it creates external table but when I try to get " select * from ext_schoolof ", then I get errors
SQL> select * from oldemp8; select * from oldemp8 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "(": expecting one of: "comma, defaultif, nullif, )" KUP-01007: at line 7 column 16 ORA-06512: at "SYS.ORACLE_LOADER", line 19
SQL>
what is the syntax error in the above command. I place the notepad file properly.i create external table before many time but cant find any this type of error.
While creating external table how can I specify a particular decode condition for a date field that comes in as '2099-99-99' i want to change it to '2099-01-0001', how i can translate it
I already have this in the access parameters..
Incoming_DATE CHAR(20) DATE_FORMAT DATE MASK "YYYY/MM/DD"
I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002 000011|0365|....|000000.000000| |000000.000000| |0002 000011|0730|....|000000.000000| |000000.000000| |0002 ^ blank spaces may be | causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,.... * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
I am having difficulty importing Spanish text files into my Oracle 11g XE database. Below is my external table declaration code. The external file is attached.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/.../';
All 406 rows seem to import fine, but do not display the Spanish accent characters correctly. Note the strange characters:
SQL> SELECT count(*) FROM ET_SLBLWD0_SPNSH_DESC;
COUNT(*) ---------- 406
SQL> select * from et_slblwd0_spnsh_desc;
LBL_ LBL_TXTSNS LBL_DESCS L L L L ---- ---------- ------------------------------------------------------- - - - - 0001 1 Puede causar somnolencia. El alcohol puede intensificar 0001 2 este efecto. Tenga cuidado cuando conduzca veh¿los 0001 3 automotores u opere maquinaria peligrosa. 0002 1 Importante: Acabe todo este medicamento a menos que 0002 2 quien le escriba la receta le indique lo contrario.
[Code]...
I tried modifying my external table declaration by adding CHARACTERSET WE8ISO8859P1 and CHARACTERSET WE8MSWIN1252, but that just seems to cause the system to throw out ALL the rows containing Spanish characters:
COUNT(*) ---------- 197
SQL> select * from et_slblwd0_spnsh_desc;
LBL_ LBL_TXTSNS LBL_DESCS L L L L ---- ---------- ------------------------------------------------------- - - - - 0001 1 Puede causar somnolencia. El alcohol puede intensificar 0001 3 automotores u opere maquinaria peligrosa. 0002 1 Importante: Acabe todo este medicamento a menos que 0002 2 quien le escriba la receta le indique lo contrario. 0005 1 Debe tomar este medicamento con mucha agua. 0006 1 Puede descolorar la orina o las heces.
[Code]...
The log file specifies the following error on the bad rows:
error processing column LBL_DESCS in row xxx for datafile ...SLBLWD0_CRLF.TXT ORA-12899: value too large for column LBL_DESCS (actual: 55, maximum: 55)
What can I do to import these Spanish characters correctly?
i'm having some issues with the External table. Currently my database encoding is AL32UTF8. If my external table wants to read the txt file with the ZHS16GBK, the value will not show correctly when the external table reads the file. Is there any ways to display the value in the txt file correctly without changing the encoding database and encoding in the txt file.
getting proper value from the file in external table.
How can I get the whole status in STATUS column like completed , Inprogress, incompleted. Right now, if I gave position like (38:9) full status doesn't show. if I give (38:11) then '|1' is adding in status from the flat file.
BATCH_NO FILE_DATEEMP_ID COMPANY_ID TRANSACTIN_ID FILE_NAME STATUS DOC_NO 10000104252012100001***4252012**1:35:57***D100001***04252012***10:35:57***Diverified
ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-00554: error encountered while parsing access parameters KUP-01005: syntax error: found "badfile": expecting one of: "column, enclosed,
I have an oracle code that is using Oracle External table to load a file.The issue: the column within the files have NA within them which needs to be skipped. Mentioned the code below -
