Comparing External File Data With Sql Query Result
Mar 16, 2009I need to compare data of .csv file with the SELECT query result or with any table data.
Is there any possibilities in ORACLE to fulfill this requirement?
ADVERTISEMENT
SQL & PL/SQL :: External Table Query (compare Number Records In File With External Table)
Jan 23, 2013I 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.
SQL & PL/SQL :: Oracle External File And UTF-8 Query
Dec 15, 2010Encountering 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:
NLS_CHARACTERSETUTF8
NLS_NCHAR_CHARACTERSETUTF8
NLS_LENGTH_SEMANTICSBYTE
NLS_LANGUAGEAMERICAN
The table is defined as follows:
CREATE TABLE XXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXX_INTERFACE
(
XXXXXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXXXXXX VARCHAR2(40 CHAR),
XXXXXXXXXXXXX VARCHAR2(40 CHAR),
[code]........
We tried adding the following attributes but they did not seem to make any difference:
CHARACTERSET UTF8
STRING SIZES ARE IN CHARACTERS
SQL & PL/SQL :: Query Result Into XLS File With Column Names?
Oct 6, 2012I want to load query result into .xls file with column names by using plsql.
Like this i need to generate 4 files and zip it then send mail with attachement of that zip file.
Can we automate in PLSQL? If it is possible pls share the script.
Ex: select ename,eno,dept,deptno,sal,hiredate from employee;
ENAME ENO DEPT DEPTNO SAL HIREDATE like this we need to store the query result into .xls file.
Forms :: Load Data From External File?
Sep 27, 2010We are using hand scanning machine for attendance, machine saved data in a TEXT file now I want to load data into my oracle base payroll system.
data saved in this format.
31201009240928000100000002690001
31201009240933000100000000060001
as per my understanding
20100924 is date
0928 is time
269 is employee code
but I am unable to understand, is this IN or OUT time?
Client Tools :: SQL For Saving Query Result In A Text File
May 17, 2012I want to execute a query which automatically saves the result of a set of queries in a text file and have to send that as an attachment thru a mail (using UTL_MAIL or UTL_SMTP) thru a stored procedure...saving the result in a text file in Toad.
View 2 Replies View RelatedClient Tools :: Exporting Result To Text File Through Query?
Dec 12, 2012I'm running this query on sql developer trying to export large file but its not executing.
set head off
spool c:myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on
Error:- line 1: SQLPLUS Command Skipped: set head on
Exporting Data From Tables To External Text File?
Apr 29, 2008Actually what i am trying to do is to extract data form tables and place them in an external text file....i wrote the following code
FUNCTION
create or replace
FUNCTION dump_data ( p_query in varchar2,
p_separator in varchar2 ,
[Code].....
Forms :: Read External File Data At A Time Like Bulk Collect Concept?
Jul 23, 2012I have external file like (.csv or .txt) which contain million of record...i wnat to upload it in backend by using form 6i/10g.
by using package text_io.fopen i read it and by using for..loop conventional method insert record into table...but it will take time..
Is there any way like we use bulk collect and FORALL in backend for inserting data into table..
Is there any way to read external file at a time and insert it ...so minimize inserting time....process will become fast.
SQL & PL/SQL :: Comparing Data In Two Tables Row By Row
Sep 23, 2011I have two databases.
one is "ora" it is a 8i version
2nd is "orcl" it is a 11g version
"Oracle" is the my local database. i wrote following program for comparing the row by row data in both the tables.
Q)Is it BEST practice? If not let me know the best practice to compare data in tables?
Q) If am not using the order by clause its giving me wrong output even though both the data tables has same data. WHY?
DECLARE
v_emptest1 EMP_TEST1%ROWTYPE;
v_emptest2 EMP_TEST1@ora%ROWTYPE;
v_suc_flg NUMBER := 0;
v_cnt1 NUMBER:=0;
[code].......
SQL & PL/SQL :: Comparing Two Tables Data
Mar 16, 2010I want to do a comparision for the missing rows between two diffrent tables
TBL1 and TBL2 both with the same structure but with diffrent data some data is identical. though my data is huge i wanted to make sure the technique i am using
Storing Select Query Result Into Array And Using It In Another Query?
Aug 7, 2009I am looking to simplify the below query,
DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)
Since both the inner queries are same,I want to extract out to a local variable and then use it.
Say,
Array var = SELECT ID FROM B WHERE BID=0;
And then ,
DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)
How to do this using SQLPLUS?
SQL & PL/SQL :: Comparing Schemas - Content (data) Identical
Jan 20, 2011I have two schemas with 149 tables in each schema, what I need to do is to prove that the content(data) between the two schemas is identical. I know that all the table names between the two schemas are the same, just need to prove that there is no difference in data.
So the query needs to prove that Schema A content = Schema B content
I know I cant do a simple select from Schema A.tab1 minus select Schema B.tab1 but since there are 159 tables, I am not sure if this is an efficient way of doing it.
DECIMAL EXTERNAL In Control File?
Sep 13, 2010In a control file, the code is as follows:
load data
infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt'
APPEND
PRESERVE BLANKS
INTO TABLE table1
[code]...
When I run the above control file in sqlldr, I'm getting the error as
Record 1: Rejected - Error on table table1, column column3.
ORA-01481: invalid number format model
In the table the column3 data type is NUMBER(6,2).: The column size in table is 6 and position of column3 in control file is only 4. Also if possible let me know how the same data (send me 2 dummy records) which exactly works for the above control file especially for column3 where decimal number comes in the flat file.
For generating the flat file, for column3, i'm using LPAD(:value,4,0) in the select query column list.
SQL & PL/SQL :: Passing Location To External File
Sep 16, 2013I have a simple external table
CREATE TABLE xyz
(
UNIQUE_ID VARCHAR2(255 BYTE),
FULL_BUSINESS_NAME VARCHAR2(255 BYTE),
ADDRESS_1 VARCHAR2(255 BYTE),
ADDRESS_2 VARCHAR2(255 BYTE),
CITY VARCHAR2(255 BYTE),
[code].......
I need to make the location parametrized so we don't have to hard code the file name file1. Can I do this in Oracle or do I need to do this in a shell script on the UNIX server?
PL/SQL :: External Table With Empty File
Oct 9, 2013My 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.
View 2 Replies View RelatedSQL & PL/SQL :: Attach External File Using UTL_SMTP Package?
Feb 7, 2012I want to attache an external file to a email using UTL_SMTP package.
A file which attach will be present in directory.
SQL & PL/SQL :: External Table For CSV Delimited File Skipping Columns?
Jan 24, 2013I need to load csv file using an external table.
Structure of External Table:
---------------------------
create table A (col1 varchar2(30), col3 varchar2(30), col5 varchar2(30));
CSV FILE:
-----------
col1,col2,col3,col4,col5
A,B,C,D,E
1,2,3,4,5
The table data should look like
COL1 COL3 COL5
A C E
1 3 5
need to skip the columns in CSV file.
Client Tools :: OWB File Loading Through External Tables
Mar 15, 2011Is it possible to trim the file name while loading into OWB through external tables?
Like suppose I am trying to read a file which has a timestamp value appended in its name. In that case loading into external file would give an error.
SQL & PL/SQL :: Hard Code Bad File Names For External Table
Apr 7, 2011I 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..
l_badfile := file_nm || '.bad' ;
SQL & PL/SQL :: How To Store External File (XLS / Doc / PDF) Format In Oracle Database
Feb 1, 2011I want to stored the excel or ms word document in oracle database. Is it possible to view that file from database. If i export full database it is included in that dmp.
View 5 Replies View RelatedPL/SQL :: Create Multiple External Tables From Same Flat File?
Nov 28, 2012using oracle 10g currently create many external tables like so..
CREATE TABLE "XT_UNITS"
(
"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)
[code]....
is there any way I can use 1 flat file (csv) to populate many external tables ?
SQL & PL/SQL :: Load CSV File Into External Table / ORA-01722 / Invalid Number
Apr 26, 2011I'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
This is the script for the table:
create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
[code]....
And this is csv:
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
[code]....
Application Express :: Access For External File In Apex Page?
May 21, 2013How can I access this "file:///C:/Users/RI/m_1.html" external file within the apex page?
I created a page and button for link but struggling for above.
Server Utilities :: External Table Definition And Content Of CSV File
May 26, 2010Below is the external table definition and the content of csv file, why the first record fails ?
0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
[code]....
PL/SQL :: Query To Get Result Set
Feb 12, 2013I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
" ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
LKP_Item.Group, LKP_Item.Modality Source
FROM ((LKP_Product
[code]....
T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp
Just want to know if there is any possibility of running the query with all the Groups in one go
Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.
Concatenating Result Of Query
Aug 30, 2007My scenario is something like this:
MyTable
Rownum Colval
1 23
2 34
3 45
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
SQL & PL/SQL :: Query Result Into Excel Or CSV
Jun 15, 2011I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.
View 4 Replies View RelatedSQL & PL/SQL :: Query Result In One Line
Aug 29, 2011I have a select that return the query like this:
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br
so,I need the query result in just one line...
EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br
I saw some exemples of "pivot query" but I still looking for a solution.
SQL & PL/SQL :: Different Result On Every Execution Of Query?
Jan 13, 2011when I am executing the below query getting diffrent count every time and not able to guess what is happening.
SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day