SQL & PL/SQL :: Passing Location To External File

Sep 16, 2013

I have a simple external table


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?

View 4 Replies


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.

View 5 Replies View Related

SQL & PL/SQL :: Read CSV File From Shared Location?

Aug 30, 2013

I want to upload csv file from share location(another host) & store data in table

View 2 Replies View Related

Reports & Discoverer :: Location Of BMP (image File) In Report?

Jul 14, 2011

I just started working for a company and am modifying existing reports. This report has a logo in the layout in the form of an image. Is there a way to find out where this image is located? It cannot find the image when I open the layout or run the report so I assume I am not mapped to the drive/directory where the images are located? Is this information noted anywhere in the actual report?

View 7 Replies View Related

Performance Tuning :: Wrong REDO Log File Location

Feb 25, 2011

I try to current redo log file location to multiplex redo log configuration. I did do this on the DB.I import a backup file to DB when I made this change, but its too slow running import process from old configuration. Approximately it is 4 times slow.

when I recovery old configuration for redo log file, import process is normal running...Why this changes hit to DB import process performance? The old redo log file location is below:


The new multiplex redo log file location is below:


I think that new configuration is better for old configuration at security issue.Here is the disk partitions on the server:

-bash-3.00$ df -lh
Filesystem size used avail capacity Mounted on
/dev/dsk/c0t0d0s0 9.6G 2.2G 7.3G 23% /
/devices 0K 0K 0K 0% /devices
ctfs 0K 0K 0K 0% /system/contract

View 4 Replies View Related

RMAN :: Give Output File Location In Crontab Using ( > )?

Aug 2, 2013

I need to take RMAN full backup by every Sunday night and Wednesday night . I have the netbackup script which will take the backup to media. 

Question is:1. We have 15 - backup scripts for 15 DB in that server , so if i configure crontab for backup do i need to give all 15 scripts one by one or (*) will work .    ie) 00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_<sid>.sh    

instead of this if i give  :  00 20 * * 3,7 /app/oracle/rman/scripts/hot_db_*.sh then it will works fine or not ? 2.

Already my backup will create .output file in the same location , do i need to give output file location in crontab using ( > ) ? 3.

Is the above crontab timing is correct ? (3,7) for Wednesday and Sunday 8pm? so Sunday is 0(zero) or 7 ?

View 6 Replies View Related

Backup & Recovery :: Restoring Spfile And Control File To New Location?

Aug 3, 2011

Im trying to restore my spfile and (later controlfile) to a new location:

RMAN> connect catalog rman11cv/pwd@metarep


RMAN> connect target

ansluten till msldatabasen: DATABASE (DBID=510270843)

RMAN> run
2> {
3> allocate channel ch1 type 'sbt_tape'
4> PARMS="BLKSIZE=262144,ENV=(CV_mmsApiVsn=2,CV_channelPar=ch1)"
5> TRACE 0;
6> Restore spfile to pfile 'y:
7> }

tilldelad kanal: ch1
kanal ch1: sid=537 devtype=SBT_TAPE
kanal ch1: CommVault Systems for Oracle: Version 9.0.0(BUILD84)

Starting restore at 2011-08-03

frigjord kanal: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore-command failed at 08/03/2011 11:39:26
ORA-27191: sbtinfo2 returned an error

The spfile and control file exists.

RMAN>list backup of spfile

BS-nyckel Typ N Storlek Enhetstyp Tidsσtgσng Slutf÷randetid
------- ---- -- ---------- ----------- ------------ --------------
736698 Full 256.00K SBT_TAPE 00:00:04 2011-08-02
BP-nyckel: 736705 Status: AVAILABLE Komprimerad: NO Tagg: TAG2011080
Handtag: dkmj0i8p_1_1 Media: V_73870
SPFILE har inkluderats: Σndringstid: 2011-08-02

View 5 Replies View Related

Server Administration :: Location Of Table In Tablespace / Data File

Jul 6, 2010

I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.

View 9 Replies View Related

Impdp Speed Differ From Location To Location

Oct 4, 2013

We have taken export expdp backup from prod database (primary database- Data Guard).

1.) Import impdp is very slow 10GB/Hrs on staging database (Data Guard MAXIMUM AVAILBILITY)Since Server configuration, database version and configuration, operating system everything are same as production. No blocking, locking or waiting sessions

2.)import impdp is fast 90GB/Hrs on Test standalone database and this test database is running in NOARCHIVE LOG mode with oracle standard version after that no more difference.

CPU,Memory,network and disk I/O are look normal while importing on both databases.why that much difference on import.

View 1 Replies View Related


Sep 13, 2010

In a control file, the code is as follows:

load data
infile 'C:\Documents and Settings\xxxxx\Desktop\abc.txt'


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.

View 1 Replies View Related

SQL & PL/SQL :: Oracle External File And UTF-8 Query

Dec 15, 2010

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):


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:


The table is defined as follows:


We tried adding the following attributes but they did not seem to make any difference:


View 6 Replies View Related

PL/SQL :: External Table With Empty File

Oct 9, 2013

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.

View 2 Replies View Related

Forms :: Load Data From External File?

Sep 27, 2010

We 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.

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?

View 3 Replies View Related

SQL & PL/SQL :: Attach External File Using UTL_SMTP Package?

Feb 7, 2012

I want to attache an external file to a email using UTL_SMTP package.

A file which attach will be present in directory.

View 3 Replies View Related

Forms :: Passing Parameters From Menu File

Apr 17, 2012

Is it possible to pass parameters from menu file to form when calling a form from menu item?

View 4 Replies View Related

Comparing External File Data With Sql Query Result

Mar 16, 2009

I 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?

View 3 Replies View Related

Exporting Data From Tables To External Text File?

Apr 29, 2008

Actually 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


create or replace
FUNCTION dump_data ( p_query in varchar2,
p_separator in varchar2 ,


View 3 Replies View Related

SQL & PL/SQL :: External Table For CSV Delimited File Skipping Columns?

Jan 24, 2013

I 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));


The table data should look like

1 3 5

need to skip the columns in CSV file.

View 5 Replies View Related

Client Tools :: OWB File Loading Through External Tables

Mar 15, 2011

Is 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.

View 3 Replies View Related

SQL & PL/SQL :: Hard Code Bad File Names For External Table

Apr 7, 2011

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..

l_badfile := file_nm || '.bad' ;

View 3 Replies View Related

SQL & PL/SQL :: How To Store External File (XLS / Doc / PDF) Format In Oracle Database

Feb 1, 2011

I 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 Related

PL/SQL :: Create Multiple External Tables From Same Flat File?

Nov 28, 2012

using oracle 10g currently create many external tables like so..


"Q1_2012" VARCHAR2(25 BYTE),
"Q2_2012" VARCHAR2(25 BYTE),
"Q3_2012" VARCHAR2(25 BYTE),
"Q4_2012" VARCHAR2(25 BYTE)

is there any way I can use 1 flat file (csv) to populate many external tables ?

View 4 Replies View Related

Windows :: Passing Values / Arguments To Batch File

Aug 26, 2012

I have a batch file Menu.bat which should pass accept the input parameters from the end users and pass the entered values to a different batch file.

After entering option 2 the user should be prompted with an option to enter the path. E.g. /shared/folder.

Now '/shared/folder' should be passed to a different batch file named R.bat

Below is the Menu.bat file.

@echo off
color 00
title Security Audit Adaptor

echo //MAIN MENU\
echo 1. Got to Google
echo 2. Enter path

View -1 Replies View Related

SQL & PL/SQL :: Load CSV File Into External Table / ORA-01722 / Invalid Number

Apr 26, 2011

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

This is the script for the table:

create table emp_ext (


And this is csv:



View 37 Replies View Related

Application Express :: Access For External File In Apex Page?

May 21, 2013

How 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.

View 5 Replies View Related

Server Utilities :: External Table Definition And Content Of CSV File

May 26, 2010

Below 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

View 8 Replies View Related

Forms :: Read External File Data At A Time Like Bulk Collect Concept?

Jul 23, 2012

I 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.

View 3 Replies View Related

Export/Import/SQL Loader :: Setting For External Table - Create File In Directory DIR-1?

Nov 20, 2012

Oracle Database 10g Express Edition Release - Product
PL/SQL Release - Production
CORE Production
TNS for 32-bit Windows: Version - Production
NLSRTL Version - 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)


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"*

how to create that file in directory "DIR_1" .

View 2 Replies View Related

Backup & Recovery :: Changing Location Of Data And Temp Files From Auto-backup Of Control-file?

Jan 26, 2012

I have an oracle autoback of controlfile and spfile. I am trying to restore the production database using this backup, into a new server with a different directory structure.

I have completed the following steps in the restore process.

1. Creating a pfile from spfile

2. Changing the the location of cdump, udump, bdump and the control files ( there are three )

3. Then creating a spfile from this pfile

I am stuck on the next step where to rename all the datafiles and tempfile and restore the database. Following is what I did.

After mounting the database in RMAN, tried to run the following.

RMAN> run
2> {
3> allocate channel c1 device type disk
4> ;
5> @/home/oracle/rman_scripts/newloc.rman
6> SET NEWNAME FOR DATAFILE 1 TO '/u02/oradata/dorian/system01.dbf';
7> SET NEWNAME FOR DATAFILE 2 TO '/u02/oradata/dorian/undotbs01.dbf';
8> SET NEWNAME FOR DATAFILE 3 TO '/u02/oradata/dorian/sysaux01.dbf';
9> SET NEWNAME FOR DATAFILE 4 TO '/u02/oradata/dorian/users01.dbf';


I am getting the following error message.

Starting restore at 25-JAN-2012 21:26:48
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/25/2012 21:26:49
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 30
RMAN-06100: no channel to restore a backup or copy of datafile 29
RMAN-06100: no channel to restore a backup or copy of datafile 28


View 14 Replies View Related

Difference Between (ocrconfig -export Location) And (ocrconfig -manualbackup Location)?

Aug 13, 2013

what is difference between "ocrconfig -export location" and "ocrconfig -manualbackup location"

View 1 Replies View Related

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