How To Extract The Data From DBF Files
Aug 13, 2010
I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.
Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.
Original database was on Unix, I'm operating on Windows platform.
View 4 Replies
ADVERTISEMENT
May 13, 2004
I would like to extract some data value from oracle to a text file...and i m not sure how to set the delimiter between the columns data value
SET echo off
SET space 0
SET pagesize 0
SPOOL a.txt
SELECT emp_id, name, add
FROM table1
/
SPOOL OFF
Where do i set the delimiter?
Can i do something like in SQL*Loader?
fields terminated by ',' enclosed by '"'
I would like the text file to be display as
"123","ABCD","123 abc road"
"234","XYZ","234 xyz road"
View 5 Replies
View Related
Jun 8, 2012
I have a requirement where the data between [] or ][ has to be extracted from a string.
Here is my situation :
INPUT:
[abc] [def]-[ghi][jlk]
OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl
View 5 Replies
View Related
Mar 21, 2012
Data that enter by user in database having " in the sentences
example:
column1
----------
"My Smurf Village
column2
--------
Farmvile
column3
--------
"Testing
My select statement is
Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become
column1 in excel
----------------
My Smurf Village|Farmvile
column2 in excel
---------------
Testing
How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?
View 5 Replies
View Related
May 25, 2012
How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts
END OF FILE. ROW COUNT:
23
9
View 8 Replies
View Related
Aug 29, 2008
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR(
PK CAR_ID INT NOT NULL,
TYPE VARCHAR NOT NULL)
REPAIR_CENTER(
PK REPAIR_CENTER_ID INT NOT NULL,
NAME VARCHAR NOT NULL)
[code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table...
2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car...
3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
View 3 Replies
View Related
Sep 8, 2007
Is there a way where i can extract the data from Oracle Express 6 (OLAP data) into an excel or any other format, so that the same can be loaded onto a MS Sql or a normal Oracle database.
View 1 Replies
View Related
Dec 20, 2010
I want to retrieve values from a XML data stored on CLOB data type and store it in different table.
Retrieving data from Soap Envelope and Soap Body?
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope>
<soap:Header>
<company>US</company>
</soap:Header>
<soap:Body>
Data
</soap:Body>
<Account>ABCD1234</Account>
</soap:Envelope>
View 8 Replies
View Related
Feb 17, 2011
We are getting following error when we are trying to extract data from ASM.
GGS ERROR 500 Oracle GoldenGate Capture for Oracle, ext_1.prm: Getting attributes for ASM file +DATA/testgg/onlinelog/group_1.257.742844671, SQL <BEGIN dbms_diskgroup.getfileattr('+DATA/testgg/onlinelog/group_1.257.742844671', :filetype, :filesize, :lblksize); END;>: (6550) ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_DISKGROUP.GETFILEATTR' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignoredNot able to establish initial position for begin time 2011-02-16 16:42:05.
Database version is 10.2.0.4.
View 1 Replies
View Related
Jul 22, 2013
I'd like to parse an XML file and get some data extracted as columns. Input file country.xml:
<?xml version="1.0" encoding="UTF-8"?>
<MAS Action="Insert">
<Country ObjectId="100000000000000009" VersionId="8"><Id>1</Id><NlTexts><Name Language="de">Land1</Name><Name Language="en">Country1</Name></NlTexts></Country>
[Code]....
View 2 Replies
View Related
Mar 18, 2013
Below is the XML i have :
<?xml version="1.0" encoding="UTF-8" ?>
- <Transaction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.test.com/Support/Services/test1/2012" xsi:schemaLocation="http://schemas.test.com/Support/Services/test1/2012 Support.test1.v1.xsd">
- <Request>
<Memid>10</Memid>
<Actid>32</Actid>
[code].......
I want to extract the element values :
The below code will be useful when i have no namespace , what must be done in order to work with namespace and extract element value
v_string_xml :=
' //Request[' || TO_CHAR (counter_xml) || ']/Memid/text()';
v_ssp_table (v_ssp_table.COUNT).memid :=
p_xml_in.EXTRACT (v_string_xml).getnumberval ();
v_string_xml :=
[code].......
View 2 Replies
View Related
Feb 9, 2012
How to extract the data from XML using the xsd file. attached files.
Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node.
EmailMessage(exists tag in order xml file)
->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml
->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml.
-> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/>
<xsd:element ref="templateversion"/>
i have to extract the value in between tags.
templatecode -> ORDCONF
templateversion ->1.0
Finally i have to load the data into table.
View 17 Replies
View Related
Oct 23, 2012
I have a table which have two columns date on hourly basis and response time. I want to pull the previous date's data on hourly basis with the corresponding response time. The data will be loaded to the table every midnight.
eg: Today's date 23/10/2012
I want to pull data from 22/10/12 00 to 22/10/12 23
The below query is pulling the date as required but I am not able to pull the response time.
with a as
(select min(trunc(lhour)) as mindate, max(trunc(lhour)) as maxdate from AVG_HR)
SELECT to_char(maxdate + (level/25), 'dd/mm/yyyy hh24') as dates FROM a CONNECT BY LEVEL <= (1)*24 ;
View 4 Replies
View Related
Aug 20, 2013
I have a requirement. I will get .csv files . I need to extract the data from .csv file and push into the various tables.
The illegal data will be rejected and sent back to the sender.I need to schedule this logic every weekend. I am on 10gR2 on Solaris.
View 7 Replies
View Related
Sep 3, 2010
I have a requirement to extract the data from a table using the UTL file utilities.
My problem is, Say i have a table t1 with column C1,C2, C3, C4, C5. This table t1 gets loaded everyday. i need to pickup the data only that which has changed/inserted in the last load. How can i achieve this ? There is no timestamp in this table.
View 3 Replies
View Related
Sep 5, 2013
the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.
CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));
INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');
[Code] .....
With the sample data there are 4 groups based in c1 column:
1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7
I'm trying to get and output like :
A1,A2,A3 : 1,3
A1,A2,A4 : 2,4
I was trying something like :
select t1.c1 as gr1, t2.c1 as gr2, t1.c2
from test_data t1,test_data t2
where t1.c1<>t2.c1 and t1.c2=t2.c2
and
(select count(*) from test_data t3 where t3.c1=t1.c1)=
(select count(*) from test_data t4 where t4.c1=t2.c1)
order by 1 asc, 2 asc
but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.
View 4 Replies
View Related
Dec 14, 2012
Is it possible for Access to extract data from an Oracle database and upload it directly?
Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.
View 3 Replies
View Related
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
Oct 14, 2010
This is my first time running a stored procedure. The procedure is already written.
We have various related table. I need to use this stored procedure and extract information from an excel sheet into the multiple tables.
View 8 Replies
View Related
Jun 27, 2013
i want to extract data from my local database table to text file using plsql
View 4 Replies
View Related
Nov 4, 2010
I have a question regarding data extraction to Excel.Is there any patch or update that makes it possible to extract to Excel 2007 and not only 2003?
View 6 Replies
View Related
Apr 19, 2013
In our application, we are allowing user to upload data using excel sheet in UI. We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.
The temp_table has a primary key.
Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ? so that we can easily extract the data by using batch id . we are using Oracle 11g.
View 11 Replies
View Related
Mar 29, 2011
I am working in a bank as an system consultant, i have a SAN Storage Area and oracle as below.
SAN 1
This interface includes the DATA FILES of the oracle tablespace
SAN 2
SAN1 Mirrors the DATA FILES of the oracle tablespace to SAN 2
1. Can i rely on real time data recovery from SAN2 ?
2. if SAN1 (Data Files are currupted) will the SAN2 Data Files will be currupted as well.
3. If the SAN2 is currupted then what Oracle Features can be used to have uncurrupted data.
View 5 Replies
View Related
Jun 15, 2012
I have set up a cross platform (Microsoft Windows IA (32-bit) -> Linux x86 64-bit) data guard and it worked fine.Then I did a switch over (which again worked) and found out the data is not getting replicated at all.. checked the data files available from the new primary database and found out they are in the windows format as below..
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:ORACLEAPPADMINISTRATORORADATAMFSSYSTEM01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSSYSAUX01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUNDOTBS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSUSERS01.DBF
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF
and physically they were created at '/home/app/oracle/product/11.2.0/db_1/dbs/' and as
D:ORACLEAPPADMINISTRATORORADATAMFSREDO02.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSREDO03.LOG
D:ORACLEAPPADMINISTRATORORADATAMFSRMANRMAN_TS01.DBF
View 5 Replies
View Related
Oct 6, 2011
I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
View 1 Replies
View Related
Aug 25, 2010
we're planning a data migration from an application (oracle-based) to another (also with oracle db).
the origin is a ca. 80 GB database. so lots of millions of records are to be migrated. (before loading records into the destination tables, they have to be transformed).
the current concept is to receive all origin data in xml files, load them in a staging area (an own migration scheme in oracle), transform and load them into the destination tables.
we have three days for the whole migration (including extract from origin database, transform, load, backup after completion...).
my question is, that a migration with xml-files is a good concept. i think xml processing is much slower than doing the same with csv files. my proposal to migrate an oracle dump (so we got the original data in our staging area) was declined.
is migration mass-data with xml files good or are there performance or other issues?
View 2 Replies
View Related
Aug 6, 2012
DB version : 11.2.0.2
Platform : RHEL 5.4
We use Oracle Managed files for storing datafiles in ASM diskgroups. To add datafiles to a tablespace we usually issue
SQL > ALTER TABLESPACE CADL_WM_TBS ADD DATAFILE '+DATA' SIZE 10g AUTOEXTEND Off;
Tablespace altered.And the new datafile will be created at the below location
+DATA/orcl/datafile/cadl_wm_tbs.893.767888027But my db_create_file_dest is set only as DATA
SQL > show parameter db_create_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
Although the above datafile got created in the desired location (ie inside +DATA/<dbname>/datafile/ directory), how did this happen without us setting the db_create_file_dest parameter to +DATA/orcl/datafile ?
View 4 Replies
View Related
Jul 3, 2013
I was trying to load data from XML files to an Oracle database table.I followed these below steps to load that file data into a table. Created XML_DIR1 as oracle directory where i have kept all XML files.
Create table import_rpt_xml of xmltypexmltype store as binary xml; insert into import_rpt_xmlvalues (xmltype (bfilename('XML_DIR1','I-Yamanouchi-20040525-501.xml'),nls_charset_id('AL32UTF8')));
This insert shows below error: Error starting at line 80 in command:insert into import_rpt_xmlvalues(xmltype(bfilename('XML_DIR1', 'I-Yamanouchi-20040525-501.SGM'), nls_charset_id('AL32UTF8')))
Error report:SQL Error: ORA-31061: XDB error: XML event errorORA-19202: Error occurred in XML processingIn line 69 of orastream:LPX-00217: invalid character 142 (U+008E) I tried to look into my XML and got that it has some Japanese characters in it.
this to deal with japanese characters in XML. I don't want to miss those characters. My databse NLS_CHARACTERSET is 'AL32UTF8'.
My sample XML file looks like this.
<ichicsr lang="ja">
<ichicsrmessageheader>
<messagetype>ichicsr</messagetype>
<messageformatversion>2.1</messageformatversion>
<messageformatrelease>2.0</messageformatrelease>
<messagenumb>US-Yamanouchi-W2004050033-4</messagenumb>
<messagesenderidentifier>Yamanouchi</messagesenderidentifier>
<messagereceiveridentifier>PMDA</messagereceiveridentifier>
[Code]...
and so on.
View 4 Replies
View Related
Jul 24, 2012
: I noticed three data files on my standby show's they are corrupted. What will be the easy and fast way to sync them up from my production database?
View 1 Replies
View Related
Jun 12, 2013
I am trying to insert data in three tables from three csv files simultaneously. This is what I have so far:
---insert all data from three csv files
DECLARE
--zenobject
F UTL_FILE.FILE_TYPE;
[Code]....
View 5 Replies
View Related
