Server Utilities :: How To Ignore Rows With A Specific Character

Mar 10, 2010

If i have a CSV file that is in the following format

"fd!","sdf","dsfds","dsfd",
"fd!","asdf","dsfds","dsfd",
"fd","sdf","rdsfds","dsfd",
"fdd!","sdf","dsfds","fdsfd",
"fd!","sdf","dsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
"fd!","sdf","dsfds","dsfd",

Is it possible to exclude any row where the first column has an exclamation mark at the end of the string.

i.e. it should only load the following rows

"fd","sdf","rdsfds","dsfd",
"fd","sdf","tdsfds","dsfd",

I am using Oracle 10.2

View 9 Replies


ADVERTISEMENT

Server Utilities :: Sql Loader / How To Ignore Error Due To Quotes

May 5, 2010

I have control file written like

LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

but i have data in csv file like

660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",

Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.

View 10 Replies View Related

Server Utilities :: SQL Loader - How To Ignore Inserting Duplicate Records

Apr 9, 2010

I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.

Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.

any other way to ignore inserting duplicate records into the tables.

View 7 Replies View Related

PL/SQL :: How To Select String Between Nth Specific Character To Nth+1 Character

Aug 9, 2012

I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'

I want to get the string between second underscore and third underscore i.e. 'MEGAMART'

How to achieve this in sql statement?

View 4 Replies View Related

Server Utilities :: Perform Impdp Command For Specific Function?

Jun 26, 2013

Below is my import command for importing specific function from export file but iam getting below errors

impdp system/PASSWORD schemas=TNC6 directory=dumpdir dumpfile=FULL01-02-2011.dmp logfile=IMP.log include=FUNCTION:"IN ('TNC_IS_NUMBER')"

ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression

View 4 Replies View Related

Server Utilities :: How To Load Tabbed Data Lines For Specific Header

Aug 5, 2010

I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.

eg of flat file.

Header
trans_code comp date rec_type
------------------------------------------------------------
8 12800002 0729201005
transcode_line acct date refrence
------------------------------------------------------------
4424604001002738307272010 24427330207710017569675
4424604001002738307272010 24427330207710017569675

View 7 Replies View Related

Server Utilities :: Difference In Character Sets Of Server And Client?

Mar 16, 2011

I've a question regarding difference of character sets, while taking a export(logical backup) of database on directly to server(linux RHEL 2.1 AS) and export on a client (windows xp prof machine, where only a oracle 9i client is installed). On server it seems to fine and okay, but on client node i'm getting following error for almost all tables.

EXP-00091: Exporting questionable statistics.

My question is :

[1] Is it creating any sort of problem, if later on i import the data which was taken from client node.

[2] Why there is a difference(marginal) in dump(.dmp) file size.

[3] Is there any way to overcome it, or it is the natural behave of it. Means not a problem.

[4] If i'm using a long or blob as datatype for some of my table,is they have any problem if i persist like above.

Additional Information about character sets On server node :

Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)

On client node :

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses US7ASCII character set (possible charset conversion)

View 7 Replies View Related

Server Utilities :: Character Set Error While Importing

Jun 9, 2011

I have oracle 10.2.0.4.0 installed on Window server 2008 [Machine A] and oracle 10.2.0.1.0 on windowx xp [Machine b]. Now I have taken the export of database on windows server 2k8 [Machine a] by puting the entry in the tnsname.ora file of Windox XP [Machine b].

Now when I am importing on the same machine I am getting the below mentioned error:

C:Documents and Settingsdsharma>IMP FROMUSER=SYSTEM TOUSER=ESCDBO FILE='D:sharevcc53_0106.dmp' LOG='D:sharevcc53_0106_IMP.LOG' ignore=y

Import: Release 10.2.0.1.0 - Production on Thu Jun 9 20:08:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production..With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
Import terminated successfully without warnings.

View 14 Replies View Related

Server Utilities :: Getting Special Character While Loading?

Sep 19, 2013

I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.How can i prevent it to load as it is not present in the file

data in file

Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8

the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.

View 5 Replies View Related

Server Utilities :: IMP-00037 - Character Set Marker Unknown?

Mar 2, 2011

When I am trying to import a dump file which was exported from Oracle 10.2.0.4(installed on Solaris) in to Oracle 11.1.0.6.0(Installed on Windows 2003), I am getting the following error

IMP-00037: Character set marker unknown
IMP-00000: Import terminated unsuccessfully

So , I have couple of questions on this

1. Is the above import operation is supported?

2. If yes , which is actually causing the problem?

3. How to rectify this problem? - any solution or workarounds?

4. What should I check in both the Oracle instances - any specific parameters or configurations

View 2 Replies View Related

Server Utilities :: How To Use Escape Character For Loading Data Via Sql Loader

Mar 7, 2012

I have to load a fixed width file using sql loader utility. But the records have multiple special characters. writing / modifying the loader utility to load the data.

--Script to create the table
create table t1 (
ip1 varchar2(2),
ip2 number,
ip3 number);

--loader utility
LOAD DATA
INFILE 'c:inputfile.dat'
BADFILE 'c:adfile.bad'
REPLACE
INTO TABLE t1
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '°'
(
ip1POSITION(1:2) CHAR,
ip2POSITION(3:17) INTEGER EXTERNAL ":ip2/100",
ip3POSITION(18:32) INTEGER EXTERNAL ":ip3/100",
)

--data file
9900000000000000000000059762160°
9900009694635473¶00009693856712-
99000024383898654000025664467904

--sql version i am using
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Mar 7 18:32:33 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

In the above mentioned data file, records has multiple special characters like '°','¶' ,'-'.
All these special characters have some meaning.
eg: '°' specifies the above column needs to be multiplied by -1
'¶' specifies the above column needs to be multiplied by -0.1

what changes need to be made in loader utility for the same? Also, will there be any change in the utility if I am using higher version of oracle?

View 13 Replies View Related

Server Utilities :: Escape Character For Loading Data Via Sql Loader

Dec 1, 2006

I have a text file which is comma separated with values enclosed in double quotes.

In my text file which I have to load into database, one of the field have the value like

Your "offspring"

When I run my normal sqlloader ctl file, it gives the error as

Record 304: Rejected - Error on table BUYER, column BUYERS_NAME.
no terminator found after TERMINATED and ENCLOSED field

Is there any way I can use some escape character for loading this type of data.

View 16 Replies View Related

Server Utilities :: Procedures / Triggers And Other Object With Character Changed

Nov 12, 2012

Procedures, triggers and other object with character changed.

Every character was replaced by ã? the content of procedures and other objects after importing a schema for production approval.

Example:

EXCEPTION
WHEN OTHERS THEN
p_msgerro := p_msgerro ||
' Erro na selec?o da Unidade Administrativa. ' ||

The character (?) deveria ser (ã)

View 4 Replies View Related

Server Utilities :: How To Process ASCII Character 29 While Loading Data With SQL Loader

Jul 20, 2010

i have to upload a file in database, the column seperator is ASCII CHARACTER 29, i dont know how to specify that in loader file?

how to write this - fields terminated by ' ' in sql file, which i am calling from a batch file.

i have attached the text file which i am trying to upload, here field seperator is ASCII CHARACTER 29 and record seperator is NEW LINE CHARACTER.

View 3 Replies View Related

PL/SQL :: How To Select Substring In Oracle Up To More Than One Specific Character

Nov 12, 2013

How to select a substring in oracle up to a more than one specific characterfor ex : 121.051^NP: FAMILY PRACTICE  ( trim the values before ^ )            121.051^*NP: FAMILY PRACTICE (trim the value before *). with below function I can only get rid of ^ , I want both the specific characters ^ and ^* to be removed at the same time.   

SUBSTR(p.phys_sub_grp_2_desc,INSTR(p.phys_sub_grp_2_desc, '^') +1)

View 7 Replies View Related

Server Administration :: How To Know Character Set As Single Character Or Multi Character

Sep 26, 2013

how do we know database character set is either single character set or multi character set?

While changing character-set from AL32UTF8 to WE8MSWIN1252 got "ORA-12712: new character set must be a superset of old character set".

Below are steps taken to resolve the issue -

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

i got this error: ORA-12712: new character set must be a superset of old character set

below are the commands executed by me:

SQL> SHUTDOWN IMMEDIATE;
SQL> CONNECT SYS/password AS SYSDBA;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT;

And its working...

I have not done it in proper order. Neither have done ccsscan. Still, no user reported any issues. Do my changes truncated the data?

View 11 Replies View Related

Server Utilities :: How To Import Dump Into Specific Tablespace Instead Of Default Tablespace Users

Jan 18, 2012

How to import dump into specific tablespace instead of default tablespace users.

I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .

View 2 Replies View Related

Server Utilities :: No Change In Rows

Jan 8, 2013

when i was loading a large file sqlldr is committing after every record, when i used

OPTIONS(bindsize=20000000, readsize=20000000 , rows=200)
LOAD DATA
LENGTH SEMANTICS CHARACTER
APPEND
INTO TABLE TABLE_NAME
TRAILING NULLCOLS
(
)
still the same.. no change at all..

View 9 Replies View Related

Server Utilities :: ROWS Command Line Parameter

Jan 11, 2013

I am new to SQL*loader and I would like to know what is the maximum number of ROWS that can be loaded in a Conventional bind array while specifying the command line parameter.

View 2 Replies View Related

Server Utilities :: Import Only Certain Number Of Rows In Oracle

Aug 15, 2013

I want to import only limited number of rows(say 100 rows from each table) from all tables but I want all proc/functions/packages etc to be imported.

View 2 Replies View Related

Server Utilities :: Insert Rows In Two Tables Using Sql Loader?

Aug 7, 2012

I am trying to insert rows in two tables using sql loader.

I have two tables in database as

SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE

I created controlfiles as

[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'

[code]....

when i run sql loader as

[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 2.

no rows is inserted..the log file contain entries as

[oracle@DBTEST sqldri]$ cat datafile.log
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad

[code]....

View 7 Replies View Related

Server Utilities :: Export / Import From One Oracle DB To Other - Rows Missing?

May 31, 2011

I am new to Oracle DBA. m facing one problem. i imported and exported my data from one oracle DB to another by using exp/imp.....in both exp/imp log files, its shows the fallowing messages at the end.

"Import terminated successfully with warnings."
"Export terminated successfully with warnings."

but when i count there are some rows missing in some tables...

what could be the cause? is there any other way to cross check whether export/import was successful...

View 2 Replies View Related

Server Utilities :: Why Duplicated Rows When A PK Is Present Running Sqlldr

Dec 10, 2010

Why duplicated rows when a PK is present running Sqlldr?

View 1 Replies View Related

Security :: Audit Specific Statement On Specific Table By Specific User?

May 29, 2012

I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :

AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;

I get an error, where is my syntax error ?

i want to know if it's possible to do it without trigger ?

View 7 Replies View Related

SQL & PL/SQL :: Count Of Rows For Specific Period?

Aug 12, 2010

I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:

Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645

View 12 Replies View Related

SQL & PL/SQL :: Select Specific Number Of Rows By Each Value

Oct 17, 2012

This is what I've been trying to do.

I have the following table:

FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc

I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.

View 4 Replies View Related

Server Utilities :: Control File To Update Multiple Rows In Database Table?

Dec 22, 2010

The following control file updates multiple rows in database table.

LOAD DATA
INFILE *
replace
INTO TABLE temp_tab
FIELDS TERMINATED BY ","
(Data LOBFILE(CONSTANT cadd_pass.xml) terminated by eof

There are 21 lines in the xml. So 21 rows are updated in table.update only one row?

View 9 Replies View Related

Server Utilities :: Control File - Insert Rows Into Table By Defaulting Date?

Jan 15, 2013

My Table structure

column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)

By Data file

asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds

I still want to insert row 2 and row 4 into table by defaulting the date. how can I handle this in control file?

View 14 Replies View Related

Server Utilities :: Possible To Configure Auditing To Get Number Of Rows Returned By Select Statements

Aug 22, 2011

I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.

We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.

View 8 Replies View Related

PL/SQL :: Return The Rows Of The Table Where Column Contains Specific Value First

Feb 5, 2013

I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.

For Example:

Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....

Now i want USA and CANADA on top in that order and then other in alphabetized order.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved