SQL & PL/SQL :: Exporting DAT File?

Nov 14, 2011

Oracle 9i,
Window 7

My query is: I am exporting the file in .dat file. So i am using below query to export the record of one table. Below is the query and details:

Desc Test_zeros

Test_ID Number(10)
Test_number Number(8,
SELECT
LPAD(NVL(TO_CHAR(Test_ID),' '),11,' ')||
LPAD(NVL(TO_CHAR(Test_number),''),10,'0')
FROM
Test_zeros;

actual Records in database table

Test_ID Test_Number
151 -126.05

When i execute the query, this is what i am getting in the .dat file:
151000-126.05

So in the above i am getting the Test_number(000-126.05) but i have to get the value like(-000126.05).How is format the records like (-000126.05).

View 7 Replies


ADVERTISEMENT

Exporting Data Objects Into SQL File Using Exp (export)?

Oct 31, 2012

I need to take only backup of schema objects with out data using exp (export) into .sql file and need to run that .sql file in the target.because I dont have exp/imp privs on target database.

NOTE: using only export (exp) not data pump.

View 1 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

FUNCTION

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

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: How To Escape Comma While Exporting Data From Table Into CSV File

Apr 9, 2012

How we can escape comma while exporting data from table into csv file.

CREATE TABLE emp
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
address varchar2(100),
[code].......

i have to export data from emp table which has address column and address column contain comma, when i am running below script, the comma part in address field comes in next tab in csv file, is there any way we can avoid shifting to next tab and can have complete address in one tab.

set echo off
set verify off
set termout on
set heading off
set pages 50000
[code]....

View 9 Replies View Related

Reports & Discoverer :: Bitmap Is Not Exporting To Excel File

Jan 24, 2012

I have added a bitmap image in my workbook but when i am exporting it into excel or HTML ,only text part of the title is exporting into excel file . The bitmap is only visible in discoverer workbook ,after exporting to excel or HTML, it disappears,

i am using oracle 9i discoverer version 9.0.2.0.

View 1 Replies View Related

PL/SQL :: NLS Parameters - Exporting Couple Of Views Into Flat File

Jan 11, 2013

In one of my projects I am exporting a couple of views into a flat file. The export utility is generic and uses dynamic sql to generate a flat file. We have a test environment and a production environment. On both the code is the same. We noticed that the output is different between the environments although it is supposed to be the same. If I export a view in the production I will get a record like this:

0020110107O0000000001|OTHER|07.01.11 08:06:00,296000|07.01.11 08:04:41,008000||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PS

In the test environment it will be like this:

0020110107O0000000001|OTHER|07-JAN-11 08.06.00.296000 AM|07-JAN-11 08.04.41.008000 AM||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PS

The code I am running is not changing any settings explicitly. It looks like this and it will be run as EXECUTE IMMEDIATE:

DECLARE
v_sql         VARCHAR2 (32000);
v_sql_count   NUMBER             := 0;
v_error       VARCHAR2 (4000);
v_new_file    UTL_FILE.file_type;
BEGIN
[code]........
  
I also tried to do the following on production in order to get it equal to the test environment:

BEGIN
EXECUTE IMMEDIATE    'ALTER SESSION SET NLS_LANGUAGE = AMERICAN '
|| 'NLS_NUMERIC_CHARACTERS = ''.,'''
|| 'NLS_TIMESTAMP_FORMAT = ''DD-MON-RR HH.MI.SSXFF AM''';
END;

This would change the formatting for the timestamp columns for almost all files. Almost. Two of those files remain unchanged and still show the decimal separator from the old setting:

0020110107O0000000001|OTHER|07-JAN-11 08.06.00,296000 AM|07-JAN-11 08.04.41,008000 AM||0|0|EUR||NOT_FROZEN|MVOIP||IS_NORMAL_VERSION|MODIFIED|6863475590797607166|8648564326455689103|8011808169304472215|||CCP||||10000580||||DEKA|PSAny

[URL]......

[URL]......

[URL].....

[URL]......

View 2 Replies View Related

Client Tools :: Exporting Result To Text File Through Query?

Dec 12, 2012

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

View 16 Replies View Related

Client Tools :: Exporting Result Into Excel Or Text File?

Jul 2, 2012

i'm working on sql developer my table contains 40 columns and contains around 4 to 5 lakhs records........

when i'm trying to export the results into excel or text file my sql developer is getting hanged... if the result is less than 2lakh record its copying....

View 6 Replies View Related

SQL & PL/SQL :: Exporting Oracle Data Into Excel File With Auto Column Size

Nov 7, 2007

I want to export the oracle data into an excel sheet. I have written the code by using UTL_FILE package. but i am getting the output as shown in the screen shot(without formatting the column size as the width of the data it has). But I want the output column width to be set according to the size of the data automatically.

View 5 Replies View Related

Error While Exporting A Job

Aug 6, 2010

error while exporting a job

ORA-39171- job is experiencing a resumable wait
ORA-01652-UNABLE TO EXTEND TEMP SEGMENT BY 128 IN TABLESPACE TEMP.

View 2 Replies View Related

11GR2 Exporting SQL Tuning Set?

Jul 21, 2011

Trying to export a TUNINGSET.Here are my steps below.

select * from dba_sqlset order by name;
ID NAME OWNER DESCRIPTION CREATED LAST MOD STATEMENT_COUNT
8LOAD_TEST_1SYS11g upgrade load test 1.15-JUL-1115-JUL-11180
7LOAD_TEST_2SYS11g upgrade load test 2.15-JUL-1115-JUL-11210

[code]...

ERROR at line 1:
ORA-19374: invalid staging table
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4328
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6829
ORA-06512: at line 2

View 2 Replies View Related

SQL & PL/SQL :: Reg Data Exporting To Excel From Oracle?

Dec 8, 2010

Now we are having 100+ sql queries and we making all those queries as procedures.after that we want to schedule those procedures and get data to export into excel file.

so we are planning to use utl_file to get data export excel. we may have rows of 30000 above.is it utl_file will be able upload all these rows into excel.any performance issue will come.

View 4 Replies View Related

Server Utilities :: Exporting Database?

May 31, 2010

I am Trying to Export my database and whenever I try to login it is giving Ora-Error 1017 Invalid Username/Password.

If I Login as System and Manager it is accepting but I am not able to Export all my Database.

View 9 Replies View Related

Server Utilities :: Exporting Table Structure From A Particular DB?

Nov 9, 2010

know the process of exporting only the table structure of a Database without the actual content of it.

Note:: I don't know how many tables are present in the DB.

View 1 Replies View Related

Server Utilities :: Exporting Metadata Backup

Jul 19, 2011

i am using a schema which i need to take a backup of meta data only i am using exp utility

exp shan/shan@shan file=/backup_dump/shan.dmp log=/backup_dump/shan.log owner=shan rows=n

but it will return me below error, i have only access to user shan our client cant allow me to use system or sysdba schema or any other required grants or privileges so is there any way to take metadata backup of user shan from user shan.

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

View 3 Replies View Related

Server Utilities :: Exporting A Table That Is 3 GB In Size

Mar 22, 2011

I am exporting a table that is 3 GB in size and also Partitioned with option NOCOMPRESS specified.

Now when i export it with COMPRESS=N option of exp utility then it should take 3 Gb in target server but will exporting it with COMPRESS=Y will save some storage during import or once NOCOMPRESS option specified on partition has no impact on exp utility COMPRESS=Y option and it will take 3 GB space in both cases

Is this true that whether u specify COMPRESS=N|Y during export it does not matter the size will be 3 GB always after import?

View 6 Replies View Related

Server Utilities :: Exporting From Physical Standby

Aug 3, 2012

I would like to export few tables from the physical standby which is in read only mode.

I have tried both the exp and expdp methods and could successfully export and import the tables from physical standby using exp unfortunately the expdp does not allow this process from a read only database.

Does this mean that we still have to use the exp feature instead of expdp ?

Note : I would expect a proper response from experts and no unwanted comments like "Contact Oracle support" or "Paste the entire command here" or "Read the Manuals" or "Why i am exporting from Standby and not from Primary" etc.

View 1 Replies View Related

Server Utilities :: Exporting Huge Amount Of Data?

Jul 25, 2011

extract a huge amount of data from a couple of views... the problem is that they want it in TXT files with fixed record length. There will be like 6 files, for a total amount of about 10GB.

export those tables in the fastest possible way? If I'm not mistaken exp and expdp can't create txt files, so do I really need to use utl_file or spool?

View 1 Replies View Related

Server Utilities :: Exporting Schema Using Consistent Parameter

Aug 5, 2012

I taking export using consistent parameter. Theoretically i can understand . practically i couldn't understand how it works.

for ex

I am updating tab1 table under sams user. table having one lakh records.
while updating the query using consistent=y and consistent=n. i mean

exp sams/sams file=cons.dmp owner=sams consistent=y
exp sams/sams file=cons2.dmp owner=sams consistent=n

then both files imported to separate user(sam ,san).
Updated info not visible in san and sam user.

I want to know practically how it works. I need perfect example. while using consistent=y and consistent=n

View 2 Replies View Related

Server Utilities :: Exporting / Importing Partitioned Table

Sep 2, 2013

I am trying to export a partition of a table and import it to another database. I get the below error when I try to import.

ORA-14400: inserted partition key does not map to any partition

If I export the table(for that particular partition) and import the table(after dropping the table) in destination, the partitions and sub partitions are created without any problem.

The table is Range Partitioned and Sub partitioned in List. So I had to perform the below operation if I want to retain other data in the Destination table.

1. Drop the existing partition
2. Create the partition and sub partition, same as source
3. Execute imp

In fact I had to perform step#2, as if I split the partition also, the sub partition gets replicated in the new partition, which again throws the same error. Is there better way of managing the partitions and subpartition in destination with exp/imp utility, so that I need not perform step#1 and step#2 manually.

View 11 Replies View Related

Server Utilities :: Exporting Schema Using Filesize Parameter?

Aug 6, 2012

Export /Import
==============

While exporting schema's

i couldn't export dump file to exact location i mean see following query : -

QUERY
=====

exp file=ackupfile1.dmp,ackupfile2.dmp,ackupfile3.dmp
owner=(order,purchase) filesize=5m as os level ,

I fould those dump files files home directory.

-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp
[oracle@localhost ~]$ pwd
/home/oracle

when i listing

rw-r--r-- 1 oracle oinstall 72 Jun 20 21:17 afiedt.buf
drwxr-xr-x 3 oracle oinstall 4096 Jun 17 10:07 Desktop
-rw-r--r-- 1 oracle oinstall 71 Jun 19 20:42 ed.hup
drwxr-xr-x 2 oracle oinstall 4096 Aug 6 19:38 backup
-rw-r--r-- 1 oracle oinstall 2826240 Aug 6 19:39 expdat.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile1.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile2.dmp
-rw-r--r-- 1 oracle oinstall 5242880 Aug 6 19:38 expfile3.dmp

Dump file goes to home path even if i mentioned appropriate location.

View 7 Replies View Related

Server Utilities :: Syntax For Exporting Only Procedures Of Particular User

Mar 7, 2011

What is the syntax for exporting only the procedures of a particular user.

View 1 Replies View Related

Export/Import/SQL Loader :: Exporting Query Using Datapump

May 29, 2013

how to WRITE a script for datapump EXPORT for the below list of tables in the ADAM schema in RMUAT2 database using include

1) PSOPR,PSDEFN,PSMTR,PSQFPR,PSMNT,PSOPR
2) PMNT,PMTS
3) RMOT,RMST

etc.... i WANT TO EXPORT ONLY FEW TABLES IN THE SCHEMA

View 7 Replies View Related

Server Utilities :: Exporting Database Schema / Tables Without Data

Sep 22, 2010

I'm trying to export a relatively large database but it's a bit more complicated than that.For one schema I need a full export / import (data included).

For another 10 schemas I need them empty, with the exception of a table in some of them which needs to be exported / imported with all data inside.Is it possible to do this with datapump utility (impdp, expdp)?

Afterwards I will be running some scripts to populate the DB instance with critical data / metadata.

View 1 Replies View Related

Server Utilities :: Primary Keys Are Not Exporting When Export Using EXP Command

Dec 27, 2011

I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.

how will i take backup including with primary keys?

View 7 Replies View Related

Server Utilities :: Export Hangs At Exporting Cluster Definitions?

Dec 22, 2010

Sunddenly my exports hangs at 'exporting cluster definitions'. I had been using this database since last 4 years and it never cause a problem or hangs at this level. here i'm pasting my screen details. it is my production db.

[oracle1@wbh_as1 smbshare]$ exp wb/wb

Export: Release 9.2.0.1.0 - Production on Thu Dec 23 00:02:44 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > wb

(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WB
About to export WB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions

View 11 Replies View Related

Application Express :: Exporting Inside Database In Export Repository - Owner And Table Name?

Jun 12, 2013

We are exporting our apex application inside the database in the Export Repository.  what owner and table name are those export located exactly?  

View 1 Replies View Related

Export/Import/SQL Loader :: Expdp / Query Option For Exporting From Multiple Tables With Same Condition?

Sep 3, 2012

export a subset of the data only from one database to another. Both on AIX.

Source/testdatabase 11.2.0.3 (non-partitioned tables)
Target productiion database 11.2.0.3 (partioned tables)

Tables same column names but diffrenet index structures and traget one to be partitioned hence only want to import the content Each table on source datbaase hascolumn seq number and only want to extract the last few months of data.

TABLES:table1,table2...
DUMPFILE=dump_dir
CONTENT=data_only
QUERY= table1:"WHERE seq_num >100 "want to use expdp but not sure about how to ensure all tables have the WHERE seq_num >100 condition, if leave table1: out and just have
QUERY= "WHERE seq_num >100 " will this condition be applied to all tables which is what we want.

I'm assuming also can use impdp CONTENT=data_only?

View 3 Replies View Related

Server Utilities :: Data Pump For Exporting And Importing Extremely Large Data Files

Sep 24, 2010

I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?

View 4 Replies View Related

Forms :: Export Text File With Same File As Imported Text File?

May 30, 2011

Is it possible to get the same name of file name from imported text file?

What i've is, I import the text file then I exported it.

can I get the same name of the imported text file?

View 2 Replies View Related







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