SQL & PL/SQL :: Load Xml File Into Table Having Xmltype Datatype?

Jul 4, 2013

I'm trying to load xml file into table having xmltype datatype, but it is throwing below given error.I even tried to load data by changing '&' into '&' but still getting same error.

Error at line 6
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

Version : ORACLE 11g, Windows 7

CREATE TABLE xml_test
(
id NUMBER(5),
NAME VARCHAR2(50),
xmldata xmltype
);
INSERT INTO xml_test(id, name, xmldata) VALUES(1,'file1', XMLTYPE(bfilename('SCOTTDIR', 'TEST_XML.XML'), nls_charset_id('AL32UTF8')));

TEST_XML.XML file content:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- excelFolder:"c:CSD'TEST.XSL" -->
<File1>
<Version>1.6.1</Version>
<FileSource>Java&Conversion 1.2</FileSource>
<FileDateTime>2013-06-13T15:55:54</FileDateTime>
<Filecreatedby>Lo&ki</Filecreatedby>
<FileLocation>Bangalore</FileLocation>
</File1>

insert xmlfile having '&' data into xmltype column.

View 4 Replies


ADVERTISEMENT

PL/SQL :: How To Load XML File Into Table

Jun 13, 2013

I've been working on Oracle for many years but fot the first time I was asked to load a XML file into a table.As an example, I've found this on the web, but it doesn't work. the file acct.xml is this:

 <?xml version="1.0"?><ACCOUNT_HEADER_ACK><HEADER><STATUS_CODE>100</STATUS_CODE><STATUS_REMARKS>check</STATUS_REMARKS></HEADER><DETAILS><DETAIL><SEGMENT_NUMBER>2</SEGMENT_NUMBER><REMARKS>rp polytechnic</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>3</SEGMENT_NUMBER><REMARKS>rp polytechnic administration</REMARKS></DETAIL><DETAIL><SEGMENT_NUMBER>4</SEGMENT_NUMBER><REMARKS>rp polytechnic finance</REMARKS>[code]......

 For the two tags HEADER and DETAILS I have the table: 

create table xxrp_acct_details(status_code number,status_remarks varchar2(100),segment_number number,remarks varchar2(100)); 

before I've created a create directory test_dir as 'c:esterno'; --

where I have my acct.xml and after, can you give me a script for loading data by using XMLTABLE? I've tried this but it doesn't work: 

DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code,         x1.status_remarks,        x2.segment_number,         x2.remarks from xmltable(  '/ACCOUNT_HEADER_ACK/HEADER'  [code]......

This should allow me to get something like this: select * from xxrp_acct_details; Statuscode status remarks segement remarks --------------- -------------------- ------------- ------------ 100 check 2 rp polytechnic100 check 3 rp polytechnic administration100 check 4 rp polytechnic finance100 check 5 rp polytechnic logistics500 process exception 20 base polytechnic500 process exception 30500 process exception 40 base polytechnic finance500 process exception 50 base polytechnic logistics but I get:

 Error report:ORA-06550: line 19, column 11:PL/SQL: ORA-00932: inconsistent datatypes: expected - got NUMBERORA-06550: line 4, column 2:PL/SQL: SQL Statement ignored06550. 00000 -  "line %s, column %s:

%s"*Cause:    Usually a PL/SQL compilation error. and if I try to change the script without using the column HEADER_NO o keep track of the header rank inside the document: 

DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') );BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code,         x1.status_remarks,        x2.segment_number,         x2.remarks from xmltable(  '/ACCOUNT_HEADER_ACK/HEADER'  [code]......

I get this message: Error report:ORA-19114: error during parsing the XQuery expression:  ORA-06550: line 1, column 13:PLS-00201: identifier 'SYS.DBMS_XQUERYINT' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignoredORA-06512: at line 419114. 00000 -  "error during parsing the XQuery expression: %s"*Cause:   

An error occurred during the parsing of the XQuery expression.*Action:   Check the detailed error message for the possible causes. My oracle version is 10gR2 Express Edition

View 3 Replies View Related

PL/SQL :: Load Data Into A Table From LDR File?

Aug 6, 2012

how can I load data into a table from *.ldr* file? How exactly I can use such files to run in loader?

View 16 Replies View Related

Forms :: Select A File And Load CSV Data Into A Table?

Mar 16, 2010

I am new to oracle designer, forms. The requirement is to select a csv file in a form ,read the file and load selected columns from a csv file into a table.

I am using CLIENT_TEXT_IO. I want to know how to extract the data from selected columns from csv file and insert into a table if the lenth of the columns are of variable length.

Another condition is that if there are duplicate rows based on orderid then take the maximum order seq nbr.Do I need to use
temp table for this logic?

View 1 Replies View Related

Load Into Oracle Table Of A Fixed Width File

Jun 25, 2013

i have a problem in my ODI 11g with the load into Oralce table of a fixed width file, i configured all the datasource in ODI and when i do view data i see all correct, the end of file is signed like "0D0A" but when i try my load interface i receive the message that my last field is more big than the one declared.

My file have an header of fields and the last field is a data-field of 2000 characters. I controlled and is really fixed the length cause is a COBOL file from a Mainframe. So it looks that ODI don't understand the end of that field and go ahead to the other, i just tryed to enlarge the limit but is always more big like if the file is shifting on the right.

Have i forgot some configuration in some place? The definition of the file present the end of file like Microsoft hexadecimal \u000D\u000A i try all the combination there but no way to avoid this problem.

View 1 Replies View Related

SQL & PL/SQL :: Load Xml File Into Table Using Dbms_xslprocessor.read2clob Package?

Jul 24, 2013

I'm trying to load xml file into table using dbms_xslprocessor.read2clob package, it loads for small file, but it throws READ ERROR for big file. I have given READ, WRITE permission on directory and also DBA role to user. Also I have attached file (change file extension to xml)

check below given code.

CREATE TABLE loadxmlfile (xmldata CLOB);
CREATE TABLE loadxml (id NUMBER, xmldata XMLTYPE);
create or replace PROCEDURE load_xmlfile

[Code]...

Note: Reason I'm doing is to remove NON BREAKING SPACE (NBS) character from xml file

View 6 Replies View Related

SQL & PL/SQL :: Procedure To Load Data In Oracle Table Into Excel File

Dec 29, 2010

send me the procedure for loading the data in an oracle table into an excel file.

View 5 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 (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,

[code]....

And this is csv:

7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10

[code]....

View 37 Replies View Related

Server Utilities :: Sql Loader - Load Whole File In Oracle Table?

Oct 26, 2012

I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.

e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date
oracle table bill_temp having the below column:
emp_name,emp_sal,join_date

Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.

Is there any way to skip the loading of particular column data from data file into table?

View 12 Replies View Related

Server Utilities :: Truncate Table And Load It With Data Present In File

Jul 17, 2010

My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.

View 6 Replies View Related

PL/SQL :: Load Data From Flat File To Target Table With Scheduling In Loader

Sep 7, 2013

I have requirement as follows.  I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table.  If first week I loaded AP data, then second week on Saturday karnatak, etc.

Provide code also how can i schedule the data load with every Saturday  with different state column values automatically.

View 2 Replies View Related

Export/Import/SQL Loader :: Read CSV File And Load Into Oracle Table

Aug 27, 2012

I want to read the csv file and load into oracle table.But I am getting file with filename_<today date> for every day. Is it possible to use single External table to read file in dynamic.

or what is the best way to do this? My oracle version 10g in windows OS.

View 3 Replies View Related

Server Utilities :: How To Write Control File To Load Data Into Revenue Table

Aug 16, 2011

I have a table revenue

create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)

and i have data in a file like below

Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678

i want to load it into the table in the following way.

Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.

View 2 Replies View Related

JDeveloper, Java & XML :: Load Huge XML File With Hundreds Of Records Into Oracle Table?

Jun 29, 2011

I need to load (using SQL Loader) an huge XML file, with several hundreds of records into an Oracle Table.The XML file schema is pretty simple, and it's anything like this:

<dataroot>
<record>
<companyname>LimitSoft S.A.</companyname>
<address>Street Number 1</address>

[code]...

I'm trying to use the help included in this link [URL]...

When they refer to schema[URL].... what should I use?? I do not need to use the Oracle website to register anything, right?

View 4 Replies View Related

Server Utilities :: How To Load Date And Time From Text File To Oracle Table Through Sql Loader

May 31, 2010

what i miss to load date and time from text file to oracle table through sqlloader

this is my data in this path (c:externalmy_data.txt)

7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30

my table in database emp2

create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);

the control file code in this path (c:externalctrl.ctl)

load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)

this is the error :

C:>sqlldr scott/tiger control=C:externalctrl.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
C:>

View 21 Replies View Related

SQL & PL/SQL :: Inserting Values In Table - XMLTYPE

Oct 28, 2013

I am trying to insert the following values in a table called as inf_content

The table INF_CONTENT has following two cols
(REQUEST_ID NUMBER(10,0) ,
FLTER_SET_XML "SYS"."XMLTYPE" NOT NULL ENABLE);
===========================================================================================
INSERT INTO inf_content

[code]...

I am getting the error:

Error:
SQL Error: ORA-06553: PLS-307: too many declarations of 'XMLTYPE' match this call

View 6 Replies View Related

SQL & PL/SQL :: Storing And Reading Xml Data From XMLType Table

Aug 21, 2010

Since I need to do something along the lines of inserting an xml document into the database for later to use.

Anyway, the problem I get with the below is

Quote:
Error at line 95
ORA-00984: column not allowed here

Script Terminated on line 95.

It's on the INSERT line

declare

xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>

[Code].....

View 2 Replies View Related

SQL & PL/SQL :: How To Find Certain Rows From A Table According To Position Of Node In XMLTYPE

May 8, 2013

I am having a table with content_id and content_data , content_data is of XMLTYPE

Content_data field in table is like below

<Content id="123234354" version="3">
<Definition>
<Code>ABC</Code>

[Code].....

i want to find in how many rows <Title> tag is after <XYZ> tag in the content_data field of the table?

View 2 Replies View Related

JDeveloper, Java & XML :: Loading Xml_data Into Xmltype Field In Table

Nov 1, 2012

Version of DB: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

I have issue while loading xml_data into xmltype field in the table.Issue is whenever there is a special character like this 'revisions to §6' in xml text then it is 'revisions to §6' when its in the xmltype field of the table.

There is this new character appended before every special character.I have checked the database characterset.Characterset: NLS_ CHARACTERSETValue: AL32UTF8

create table xx_testxml(lx xmltype);/
DECLARE
x_item_doc sys.XMLTYPE := NULL;
BEGIN
SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
INTO x_item_doc
FROM dual;
INSERT
[code]....

View 23 Replies View Related

XML DB :: How To Select Parent / Child Related Data From XMLTYPE Table

Jan 9, 2013

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.

Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.

how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.

My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.

-- WHat I would like to determine from a select statement:

LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202

(The data has been simpliied and masked, but is true to the content and is queryable).

select * from V$VERSION

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....

View 3 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

PL/SQL :: Conversion Of BLOB Datatype To CLOB Datatype

Aug 17, 2012

I would need to convert the column datatype from BLOB to CLOB. currently in the table, the BLOB column has the data. the requirement is to convert this column from BLOB to CLOB datatype.

How to convert from BLOB datatype to CLOB datatype ?

View 10 Replies View Related

SQL & PL/SQL :: Load Data To File?

May 24, 2012

I have a table in oracle and i want to load data to flat file which is present on the server. how to and where to provide the destination file location to load the flat file.

Can it be also provided in the plsql program.

View 6 Replies View Related

PL/SQL :: How To Load XML File Into Database

Sep 14, 2012

I've always only loaded data into the database by using SQL-Loader and the data format was Excel or ASCII

Now I have to load a XML.

How can I do?

The company where I work has Oracle vers. 8i

View 5 Replies View Related

TNSNAMES Sub File Load Possible

Apr 22, 2013

Possible to call a tnsnames file from master tnsnames like the principle of ifile in the init.ora...My primary tnsnames.ora has a lot of development entries which are repointed frequently, it would work with logistics if I could manage these in a second tns file and load from primary so when my dev instances are repointed I'm not editing the master tnsnames

View 4 Replies View Related

Getting Error When Try To Load The Control File

Mar 12, 2012

I am receiving errors when trying to load the control file. The errors are as follows:

SQL*Loader-500 Unable to open file (homework.ctl)
SQL*Loader-553 file not found
SQL*Loader-559 SYstem error: The system cannot find the file specified.

My control file is located directly in the C drive (C:\homework.ctl). The control file contains the following

LOAD DATA INFILE 'c:\country.dat'
APPEND INTO TABLE homework
fields terminated by ',' optionally encloded by '"'
(country, month, day)
WHEN (month='April')

On the command line, I am entering:

sqlldr system/password control=homework.ctl

View 1 Replies View Related

Trying To Load A XML File Into Multiple Tables

Oct 8, 2010

I've been given the task of importing an XML file into multiple tables within our database using PL / SQL and I am wondering what the best approach would be.

The files will be quite large and I need the code to be as flexible as possible.

View 1 Replies View Related

PL/SQL :: Load PDF File Into BLOB Field?

May 10, 2013

The SQL below successfully inserts a row into my PDF_TEMPLT table and reads the referenced pdf file into the TEMPLT field. However, the pdf stored in the blob is incomplete or somehow corrupted. It's 438655 bytes long and causes the application that uses it from the database to crash. If I load the same file into the blob field using Quest Software's Toad GUI, it's 438667 bytes (12 bytes longer), and the consuming application works fine. I have the same problem with other pdfs, too, though the difference in length varies from 2 to 17 bytes, with the SQL-loaded blob always being shorter.

why a blob loaded by this SQL would differ from one loaded via Toad, and what changes I'd need to make to this SQL to get it work properly?

DECLARE
l_dir VARCHAR2(10) := 'RPWS_DIR';
l_file VARCHAR2(30) := '5214OR_Rev_1_13.pdf';
l_bfile BFILE;

[Code]......

View 4 Replies View Related

Unable To Load Data In Oracle 11g From XLS File?

Sep 6, 2011

CASE 1:

when i tried to load the data i got the below error,

Error starting at line 2 in command:
INSERT INTO RECON_MATCHED_DETAILS (RECON_MATCHED_DETAIL_OID, RECON_ID, STATEMENT_DATE, EXECUTION_DATE, TRANSACTION_NUMBER, TRANSACTION_DATE, TRADE_ID, TRANSACTION_TYPE, LINK_ID, ITEM_TYPE, ASSET_CODE, ISIN, BUYSELL_INDICATOR, SETTLEMENT_DATE, CURRENCY, QUANTITY, VALUE,

[code]...

CASE 2:

i tried to load the data in oracle 11g but i'm unable to load the data,and for testing i tried with a single row of data.but surprisingly the table filled with (null)s

View 3 Replies View Related

Server Utilities :: Load From A Text File

Aug 8, 2012

I would like to load a text file into an oracle table.

View 10 Replies View Related







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