Server Utilities :: Execute Package Using Sqlldr And Input CSV?

Apr 23, 2012

I have written a shell script that will execute a procedure. The input parameters are constant.

#!/bin/ksh
sqlplus user@server.com<<EOF
set serveroutput on;
var Return_Code number;
var Return_Message varchar2(4000);
exec test_pkg.Insert_test('IDD', null, 'BATCH',:Return_Code, :Return_Message);

Now I have to call same procedure but the input is a csv file.

Is there a way to call a procedure using csv file, I cannot load the table using SQL Loader because there is a complicated logic.For every row in CSV there should be 3 rows inserted into table and 2 rows updated.

As of JAN 2, three rows have to be inserted with dates JAN2, JAN 3, JAN 4 and 2 rows ( 3ODEC and 29DEC have to be updated). Also these days have to be business days.

So all this code is in procedure that uses a DB2 package for business dates.Instead of using sql ldr , if would like pass the csv file as input param.

View 1 Replies


ADVERTISEMENT

Server Utilities :: How To Execute SQLLDR / Where Data File Reside In Another Server

Mar 24, 2010

How to execute the SQLLDR, where Data File Reside in another Server?

View 1 Replies View Related

Server Utilities :: Intermediate COMMITS When Using Sqlldr Or Imp Utilities

Oct 29, 2013

I want to load lakhs of records into a table. My problem is when after loading the ΒΌ of records my process is abend due to the size of my rollback segment area. I don't have an option to increase it. So, Is there any way to go for intermediate commits when I am using the imp or sqlldr utilities to load the entire data without abend?

View 2 Replies View Related

Server Utilities :: Passing Value Into Sqlldr

Jun 15, 2010

I am executing sqlldr from a UNIX shell script (HP box). The data I am loading is coming from a fixed length flat file. I also want to be able to pass a variable from the shell to the loader job to be loaded with the rest of the data into the oracle table. The value being passed will change with each execution of the shell script which is run on a daily basis.

View 7 Replies View Related

Server Utilities :: Decode Value For Sqlldr?

May 10, 2011

I am trying to upload attached csv data in below described table.

Name Null? Type
------------ -------- --------------------------

MSISDN VARCHAR2(20)
PREFERENCES VARCHAR2(100)
PHONE_DIG NUMBER

There is some character type and null MSISDN records in csv file. due to this I want 0 in my PHONE_DIG column.

Accroding to attached ctl file i am not able to do that.

View 6 Replies View Related

Server Utilities :: Insert Only Date Using Sqlldr?

May 21, 2010

we are using sqlldr to load data into table.we have data in a csv file as below

"TXN_DATE","TXN_HOUR","VID","HID"
"2010-05-18 20:00:00.0","20","184","212"
"2010-05-18 21:00:00.0","21","184","212"
"2010-05-19 17:00:00.0","17","184","212"

I just want to insert only date without timestamp from the first field TXN_DATE. i.e., i just want 2010-05-18 in my table column.

my table desc is
Name Null? Type
----------------------------------------- -------- ---------
SEQID NUMBER(5)
TXN_DATE NOT NULL DATE
TXN_HOUR NOT NULL NUMBER(2)
VID NOT NULL NUMBER(5)
HID NOT NULL NUMBER(5)

i tried many combination but i couldn't achieve. right now i am able to get only the complete date with timestamp using the following control file.

APPEND INTO PERF_STATS
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
TRAILING NULLCOLS

[Code].....

View 8 Replies View Related

Server Utilities :: To Use Sqlldr In Fedora Linux

May 12, 2011

Im trying to use sqlldr in Fedora Linux but it doesnt work

The message:

Message 2100 not found; No message file for product=RDBMS, facility=ULMessage 2100 not found; No message file for product=RDBMS

oracle.sh:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib:/usr/include/oracle/11.2/client:/usr/share/oracle/11.2/client
export ORACLE_SID=zxin
export PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin

View 6 Replies View Related

Server Utilities :: SQLLDR Sequence Number

Jun 13, 2013

I have a CSV file with 100 records and one of the column as FILE_ID. I want to load one unique number for all 100 recs not for every records.

suppose my sequence returns 3 as next val i want to load 3 for all 100 records. How to implement this in control file or sh file . I am using shell script to call sqlldr.

View 2 Replies View Related

Server Utilities :: SQLLDR Connection Error?

Aug 25, 2012

The SQLLDR fails with connection error:

SQLLDR userid=userid/passwd@vpl01 control=OtherType.ctl log=OtherType.log bad=OtherType.bad
SQL*Loader: Release 11.2.0.1.0 - Production on Sat Aug 25 13:32:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-704: Internal error: ulconnect: OCIEnvCreate [-1]

If I provide full connection string, it gives syntax error:

sqlldr userid/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasrv)(PORT=1526))(CONNECT_DATA=(SID=vpl01))) control=OtherType.ctl log=OtherType.log bad=OtherType.bad
LRM-00116: Message 116 not found; No message file for product=ORACORE, facility=LRM

[code]...

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

but it doesn't work if supplied following command:sqlplus userid/passwd@vpl01

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 25 13:32:14 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

Even tnsping vpl01 gives error:
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 25-AUG-2012 09:14:40
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:

[code]...

View 4 Replies View Related

Server Utilities :: Invalid Geometries After Import Sqlldr?

May 17, 2010

I'm using sqlldr to import geometries into a table. The import runs succesfully, but when I validate to imported geometries using "SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT", it gives an error on empty geometries (or supossedly empty).

When I select to columns using sqlplus, the geometry column isn't entirely empty. After I set the geometry = null, the geometry is really empty. (see screenshot)

Question: How do I import empty geometries values properly, so that it's really empty My guess is that I have to alter to ctrl-file, like NULLIF

data:
3806501|
3806504|2001|90112||121231|485668||;:

ctrl-file:
LOAD DATA
INFILE 'aap.dat'
APPEND INTO TABLE aap
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '$$'

[code]...

View 4 Replies View Related

Server Utilities :: Concatenating Dashes In A Column Using Sqlldr

May 20, 2011

I'd like to load ss_numbers, but concatenate dashes in between.

The ssn currently looks like this in the file: 123456789

I'd like to put dashes in there to make it load like this. 123-45-6789

The data is positional, so I have the column in the ctl file defined as:

ssn position(1:9) char nullif ssn=blanks ,

I know how to concat using the other method of loading this way, but this is not how the data file looks:

"substr(lpad(:ssn,9,'0'),1,3)||'-'||substr(lpad(:ssn,9,'0'),4,2) ||'-'||substr(lpad(:ssn,9,'0'),6,4)",

how to get the dashes in there while loading with the positional method?

View 4 Replies View Related

Server Utilities :: Number Sqlldr Control File?

Apr 24, 2012

I have a csv file extracted from mainframe which has to be loaded into oracle using sqlldr utility.The numbers are in the format +0000003333, -0000003232.44 etc

I have to convert it to 3333 and -3232.44 and insert into the table.

I have used syntax like

Load file....append into table (t_num expression "to_number(':tnum,'99999.999')")

This gives me an invalid number error.

View 3 Replies View Related

Server Utilities :: SQLLDR / Dates And Null Columns?

Nov 10, 2010

I am experiencing somewhat same issue...but have been unable to resolve it(new to Oracle) I am getting the infile from flat file(data dump from SQL) using sqlldr to upload data to the Oracle table...since the data is already in the flat file...I cannot do anything in the SQL to pre-format the data...

Sample of ERROR I am getting - Column CREATE_DATE which has date and time - happens to other date time columns also if remove the CREATE_DATE from Control file(happens to every single line of record):
==========================================
Record 2: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

[code]...

Flat file: (3 lines of data)

5|Annie|1|AR|84601D0A-6D9D-4D0F-86EB-2FDD9D7E680B|0|0|1|1|1|0|1|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Annie|VAXP60|2008-10-03 16:54:59.583|2008-10-03 16:54:59.583
11|Beverly|1|BA|9A2D6304-E997-4B40-96E5-2221E521B077|1|0|0|1|0|0|0|0|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|BEVERLY|VAXP60|2008-10-03 09:39:33.973|2008-10-03 09:39:33.973
29|KGarbin|1|KG|B229FCF9-BED0-4E50-9804-83324B677C67|0|0|1|1|1|1|0|1|kgarbin|XPLTMCE01|2005-04-07 13:54:42.087|Gfoote|VAXP60|2008-09-08 10:05:01.690|2008-09-08 10:05:01.690

View 1 Replies View Related

Server Utilities :: Sqlldr Loading Into A Clob Column

Oct 26, 2011

I am doing a simple test and need to populate a small table with some data.

My table looks like this:

SQL> desc clob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(20)
C1 CLOB

I have a pipe delimited data file ID range 1-50000 and random characters with a length of 100-4000 bytes for the clob field.

My control file looks like this but I am guessing it is wrong:

LOAD DATA
INFILE 'clob_test.dat'
INTO TABLE CLOB_TEST
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ID INTEGER EXTERNAL NULLIF (ID=BLANKS)
, C1
)

Let me know what I need to do to my .ctl file in order to load this data?

View 4 Replies View Related

Server Utilities :: Loading Vertical Data Using Sqlldr?

Aug 23, 2013

Here's my table:

SQL> desc stg_query_overflow
Name Null? Type
----------------------------------------- -------- ----------------------------
HOSTNAME VARCHAR2(50)
NPSID NUMBER
NPSINSTANCEID NUMBER
OPID NUMBER

[code]....

Here's my controlfile:

load data
infile '/u01/tony/server_name/query_overflow.dat'
badfile '/opt/oracle/tony/sql_dir/bad/server_name_query_overflow.bad'
discardfile '/opt/oracle/tony/sql_dir/discard/server_name_query_overflow.dsc'
append
into table stg_query_overflow

[code]....

Here's a sample of data that I can't load into the table via sqlldr:

echo
echo ***** Creating view: "pul_promotion_response"
CREATE or replace VIEW "pul_promotion_response"
(
"promo_rsp_id",
"promo_hist_dtl_id",
"indiv_id",

[code]....

Here's the error(s) I receive in my log file:

Record 272: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID.
ORA-01722: invalid number
Record 273: Rejected - Error on table STG_QUERY_OVERFLOW, column NPSID.
ORA-01722: invalid number

As you can see, sqlldr is interpreting this vertical sql code as the npsid column, when in fact it is the querytext column. How can I insert each record when some of my data is in this vertical format?

View 1 Replies View Related

Server Utilities :: How To Load Date Field Along With Timestamp Using Sqlldr

Apr 17, 2013

I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format

100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM

how to load the date filed along with the time stamp.

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

Server Utilities :: Estimate Tablespace Growth While Loading Data Using Sqlldr?

Jun 1, 2011

We load large amount of data into multiple tables using sqlldr. Amount of data that we need to load varies according to the situation. We want to estimate the tablespace usage growth due to this data load, so we can verify/extend the tablespaces before the data load. Though, setting to autoextend will work in this case, We want to avoid extending the tablespace during sqlldr executing due to performance.

Our initial attempt was to note the tablespace size before and after executing the sqlldr and use the delta. But this delta was not consistent in different environments for the same amount of data. Different environments mean different oracle servers, different existing sizes of tablespaces, One data file Vs multiple data files etc.

How do we reliably estimate how much tablespace we need for the given amount of data?

View 3 Replies View Related

Server Utilities :: Sqlldr - Change The Load Type From Bulk To Normal

Jan 25, 2012

sqlldr works fine. but, i have primary keys defined in the table. So the SQL loader throws error that

SQL*Loader-951: Error calling once/load initialization
ORA-26002: Table WD has index defined upon it.

How should i change this load type to "normal" in sqlldr ? I think sqlldr made for bulk load purpose only.

View 3 Replies View Related

Server Utilities :: SQLLDR - Delimited File - Bypass Unwanted Data Fields

Aug 8, 2012

using SQLLDR: Looking for a control file solution to move past or bypass extra data fields which are not on destination table. Basically if you have 8 tab delimited fields(terminated by ' ') on a data record; but only need to load 5 of the values from the delimited record; is there a way to ignore/bypass the not needed data. Obviously, the answer would be to massage the data at the OS and removed the 3 unnecessary fields.

However my hands are tied by volume,time, and compliancy. I am familiar with using 'FILLER' for the reverse scenario; but not where you have more data available on the record then exists on the table.

View 1 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

View 10 Replies View Related

Server Utilities :: SQL Loader - How To Dynamically Create Multiple Records Out Of One CSV Input Line

Jul 28, 2010

I need to load data from a CSV file where one of the CSV values determines how many records should be inserted.

Example of the input data:

KEYWORD;2;REC1_COL1_X,REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y,REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z,REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z,REC4_COL1_Z;REC4_COL2_Z
If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.

As a result I hope to achieve this:

SELECT Column1, Column2 FROM testTable

REC1_COL1_X,REC1_COL2_X
REC2_COL1_X;REC2_COL2_X
REC1_COL1_Y;REC1_COL2_Y
REC2_COL1_Y;REC2_COL2_Y
REC3_COL1_Y;REC3_COL2_Y
REC1_COL1_Z;REC1_COL2_Z
REC2_COL1_Z;REC2_COL2_Z
REC3_COL1_Z;REC3_COL2_Z
REC4_COL1_Z;REC4_COL2_Z

I learned how to import data using Oracle SQL loader for cases where one input line more or less matches a (new) row in a DB table.

View 9 Replies View Related

Server Utilities :: ORA-04063 - Package Body Has Errors

Jan 30, 2013

I am getting the below error when i am trying to import the dmp file into my oracle 11g.

impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user

ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

View 7 Replies View Related

Server Utilities :: Restore A Package Only With Original Import Utility?

Jun 28, 2013

I am trying to restore a package only from a dump file that was exported by original exp command. I know you can do it with data pump, but unfortunately this is dump file is not exported by expdp. Can I do that. I really do not want to import the whole database.

View 5 Replies View Related

Server Utilities :: Impdp - Package Body Import Taking Huge Time?

Sep 13, 2012

I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).

I have tried both parallel and non-parallel.how to improve speed of the package body import.

Details about the Schema & Import No. of objects in Schema

SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);

OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1

ORA-00933: SQL command not properly ended

View 3 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

SQL & PL/SQL :: Execute Privilege On A Package Which Is In User

Dec 5, 2011

how will i know if i have execute privilege on a package which is in user?

View 6 Replies View Related

SQL & PL/SQL :: Providing Execute Privileges To Function In A Package?

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;

I got the blow error.

ORA-00905: missing keyword

View 2 Replies View Related

Forms :: Ora-00600 Cannot Execute Package Of Other Schema

Oct 21, 2010

I am using Oracle forms 6i, database 10g. Created a package containing 1 function in schema1.

Granted execute on this package to Schema2. Created a public synonym on this package.

Now when im using this package in a form and compiling in schema2, it is giving Error 0 at Line 0, column 0. Ora-00600: internal error code, arguments: [17069],[71101744],[],[],[],[]

If i compile in schema1, where package was originally created, it is compiling without any error. But i am not compile this in schema2.

View 3 Replies View Related

PL/SQL :: Providing Execute Privileges To Function In A Package

Jun 6, 2013

I want to provide execute privilege to a function in a package to other schema.

Owner wedb
Package MASTER
function in a a package is convert_function

GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.

ORA-00905: missing keyword

View 5 Replies View Related







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