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

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

Script Terminated on line 95.

It's on the INSERT line


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


View 2 Replies


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

Jan 9, 2013

select * from V$VERSION

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Solaris: Version - Production
NLSRTL Version - 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:

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

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

View 3 Replies View Related

Forms :: Reading Excel Data And Inserting Into Table Using 6i?

Feb 16, 2004

how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.

View 3 Replies View Related

SQL & PL/SQL :: Ftp File To Remote Server By Reading Data From Table?

May 1, 2012

I need a way to ftp file to remote server by reading data from table. I searched a couple of sites which asked me to use Chris xutl_ftp package..but unfortunately the site is no accessible..

Here is the code

* LICENSE: GNU Lesser General Public License (LGPL)
* Copyright (C) 2003-2006 Russ Johnson (john_2885@yahoo.com)


View 3 Replies View Related

SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?

Jun 25, 2012

I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.

1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.

2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.

However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.

From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.

View 1 Replies View Related

JDeveloper, Java & XML :: Reading Data From Database Table That Contains Control Characters

May 1, 2011

I have the following bit of code that reads data from the an Oracle table (Note: This is running on Jdk 1.4.2)

ResultSet message = messageStatement.executeQuery(getMsgSql);
String messageData = message.getString("MESSAGE_DATA");

The data in the MESSAGE_DATA column contains text but also control characters that separate data elements in the message (i.e (char)31, (char)29) and (char)28) . What i am finding is that for some reason message.getString() is sometimes truncating the message. I can read the majority of messages but some of them are truncated. Am i supposed to be reading the data using a different method? If so how?

I have tried to use sqlplus to look at the data in the database and it is all there it is just truncated by the message.getString() method. I saw this when i tried to output the result i.e. System.out.println(message.getString()).

I ran an sql query using the length() function in Oracle and length("MESSAGE_DATA") returns 2032 whereas in java message.getString(1).length() returns 2000.

View 5 Replies View Related

XML DB :: Access Data Using XMLType

Oct 2, 2013

I have the following XML structure: 

<TZR Action="Insert" OrgaT="400"> <Division id="4">  <label language="de">ok</label>   <label language="en">Live engl.</label> <Productstructure id="300" Sparte="5">  <label language="en">Live engl.</label> </Productstructure> </Division></TZR>    

 I'd like to get  4, ok, Live engl, 300, 1, Live engl.   

using XMLTYPE but how to get

id="4" or Sparte="1"   SELECT *FROM XMLTable(       '/TZR'       passing XMLType(bfilename('XML_DATA_DIR', 'xxx.xml'), nls_charset_id('AL32UTF8'))       columns Division_id       varchar2(30)       path 'Division/id'     ); Oracle 11.2.02 on Windows 2008.

View 2 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
INSERT INTO inf_content


I am getting the error:

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

View 6 Replies View Related

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

id NUMBER(5),
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" -->
<FileSource>Java&Conversion 1.2</FileSource>

insert xmlfile having '&' data into xmltype column.

View 4 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">


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 - 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);/
x_item_doc sys.XMLTYPE := NULL;
SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
INTO x_item_doc
FROM dual;

View 23 Replies View Related

SQL & PL/SQL :: Storing Images In A Table

Mar 22, 2011

I am trying to store an image in a table. What is the easiest way to do this?

View 8 Replies View Related

Forms :: Data Storing In Multi-record Block

Aug 10, 2013

I have a form with 3 blocks in our oracle application. Block1 is a single record block. Block2 and block 3 are multi record blocks with no of records 5. Block1 and block2 are in same canvas1 of a window1. Block3 is in canvas2 of window2. There is a relation between block1 and block2. Another relation with block2 and block3 is also available.

Now my issues is while creating a record, first i entered the data in block1 (which is a single record block). Then i entered data in five rows of second block. Then i entered data in block3(multi record block) by pressing the button in block2 (each button for each row). But before saving the entire form, when i checked the data in block3 by pressing the button of first record of block2, screen shows empty.

Code in button of block2 is as follows


Code in button of block3 is as follows


If post; is included in block3 button. then problem we will be solved. But we don't want to use post; as it lock the table.

View 16 Replies View Related

SQL & PL/SQL :: Column Validation For Types And Storing Data For Subtypes?

Apr 3, 2010

I have following structures:

SQL> CREATE TYPE address_typ AS OBJECT (line1 VARCHAR2(20), city VARCHAR2(20), COUNTRY VARCHAR2(20));
2 /

Type created.
SQL> CREATE TYPE phone_typ AS OBJECT (area_cd VARCHAR2(10), phone# VARCHAR2(10));
2 /
Type created.
SQL> CREATE TYPE phone_list_typ AS TABLE OF phone_typ;


My questions are:

1. I have created an object table person_obj of parent type. I have not created object tables of salesperson_typ and customer_typ and want to store data related to both in person_obj. What is the difference between storing data in an object table of parent type and object table of subtypes? In which case should I consider storing data of all subtypes in an object table of parent type instead of object tables of individual subtypes?

2. Second question is regarding validating the column values before the creation of object instance. Suppose before creating an instance of salesperson_typ, I want to check if salary is NOT NULL and greater than 0. I guess constructor is the only option to achieve this but how full-proof it is if constructors are used for this purpose? Is there any other alternate way?

View 4 Replies View Related

SQL & PL/SQL :: Converting XLS With Korean Data To CSV And Reading It

Mar 3, 2011

oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
"CORE Production"

I have a .xls file, which has few columns with Korean data. I have problem converting it into .csv. I converted it to UNICODE TEXT, which was TAB separated. I replaced tabs with commas. but still saved it as .txt file. Now I am using utl_file package to open and read.

utl_file.fopen_nchar(l_dir, l_filename , 'R',8000);

I outputted the first header line, and instead of Korean fieldnames, it displayed some different characters.. cannot even copy-paste..

I am uploading the converted .txt as I cannot upload original .xls file here...

View 18 Replies View Related

SQL & PL/SQL :: Reading Data From Clob Field

Apr 15, 2010

I have one table with clob field. The data in this field will contain string having transaction record data. Now I want to read data from this clob filed and insert different record in other oracle table.

example ->
Data in clob field will be->

I have one master table and one detail table. I want to insert record -> H|12|1233|fff|sss in master table
and records->
in detail table.

End of excercise will redult-> 1 record in header and 5 records in detail table.

I need to implement it in my project.

View 7 Replies View Related

Forms :: Reading And Writing Data In A Text File

Mar 8, 2011

I need to read data from text file(located on application or db server or on some other server, however path is known to me.) and then append some data in it.

Data will be read and written on daily basis so i want to clear all data on date change.

View 3 Replies View Related

SQL & PL/SQL :: Reading Streaming Continuous Binary Data From A Website?

Jan 26, 2011

Is it possible to read streaming continuous binary data from a web site using PL/SQL?

View 8 Replies View Related

SQL & PL/SQL :: Error In Reading Data From Collection Variable For Ref Cursor

Jun 19, 2013

While reading data from collection variable using ref cursor . I am getting the below two errors.

PLS-00382:Expression is of wrong type
ORA-22905 Cannot access rows from a non-nested table item.


View 4 Replies View Related

LPAD - Reading Values From One Table And Inserting Into Another

Apr 20, 2012

LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:

CURSOR update_mpi_cur IS
    select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr
      from table1 A, table2 B
     where B.external_id = A.epn_nbr and B.identifier_type = 'EPN';

should have mentioned that this is Oracle, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.

View 2 Replies View Related

Forms :: Reading Data From Excel File And Load Into Form

Oct 25, 2013

I have a Excel File which contains some columns and rows, i need to load that excel into a form and import that form data to Database Table, using DDE Method.

In simple i say; Just to read the excel and load into a form, which can be imported into a table later.

How to achieve this? only dde method.

View 10 Replies View Related

SQL & PL/SQL :: Reading Data From XML Message And Send Result To Net Using Bulk Collect

Aug 15, 2013

We want to send the following XML message as IN parameter to the procedure

<?xml version="1.0" encoding="UTF-8" ?>
- <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
- <s:Body>
- <CalculateWEDBCached xmlns="http://wedb.GGG.Services/Contracts/">
- <skuItems xmlns:a="http://schemas.datacontract.org/2004/07/wedb.GGG.Services.Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
- <a:SkuItem>

I need a procedure to get the records from the table for every SKU of XML message from the table and take one record for each SKU based on order precedence.And send the set of output records to the .net code. The order precedence is

If MODEL is not null that record should be high precedence and consider max lead_time
If MODEL and VK_UNIT is NULL then the precedence goes to STATE_ID.

The parameters in the procedure are

p_bu_id IN number,
p_model IN varchar2,
p_vk_unit IN XMLTYPE,
p_country_id IN varchar2,

Query to get the data from the table based on the parameters.

SELECT bu_id,

Here every SKU from XML message should be passed as p_vk_unit to VK_UNIT column in the query


Based on the above query assume we got the following records.

0 M123 210-39348 AB 0 20 A
0 M123 210-39348 AB 0 30 B
0 NULL 210-39348 AB 0 10 C
0 M123 405-12132 AB 0 10 A
0 NULL 340-30904 AB 0 30 C
0 M123 340-30904 AB 0 20 B
0 M123 340-30904 AB 0 10 A
0 NULL 403-10890 AB 0 10 B
0 M123 403-10890 AB 0 20 A
0 M123 709-10007 AB 0 10 B

Th final query has to return the following result to the OUT parameters p_Lead_Time p_FC of the procedure.

30 B
10 A
20 B
20 A
10 B
30 A

How to implement my above requirement using BULK COLLECT.

View 11 Replies View Related

Associating XMLTYPE Object With A XSD?

Oct 11, 2012

How to create an AQ queue in with payload of type "sample_orc.xsd".

Right now I created an object of XMLTYPE. Below is the code

CREATE OR REPLACE TYPE sample_payload_type AS OBJECT (
test_data SYS.XMLTYPE

Now How will I associate an XSD say "sample_orc.xsd" to this object?

View 2 Replies View Related

XML DB :: Add Attribute Node To Xmltype

Aug 31, 2012

How can I add an attribute node to an existing xml in a xmltype?

Say I have

  t_xml xmltype;
  t_xml := xmltype( '<x a="1"/>' );
end;How can I manipulate this variable t_xml to become
<x a="1" b="2"/>Anton

View 14 Replies View Related

SQL & PL/SQL :: Create A Function That Return XmlType

Apr 29, 2010

i want to create a function that build a return xml (XmlType):

create or replace
function plainLanguageSummary(nip varchar2,id number,code_language varchar2) return XmlType


but in the compilation i got the following error :

Error(10,62): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others <identificateur> <identificateur entre guillemets> <variable bind> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du jeu de

he seems to not like the first "select" he encounter!.

View 9 Replies View Related

Client Tools :: Insert XML Into XMLType With Value

Mar 29, 2012

I'm trying to insert an XML into an XMLType with the following value:

insert into Table1(
code, xmlColumn) VALUES
(1, '<script> > </script>');

the question is I need to have > in the text, but Oracle insists in putting it like <script> > </script>.

What do I have to do to solve this?

View 15 Replies View Related

JDeveloper, Java & XML :: Selecting The Attribute Value Of Given Xmltype

Dec 12, 2011

I need to get the value of an attribute for the given attribute name of xmltype.

DROP TABLE some_things ;

create table some_things (object_value CLOB);

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App123 "/>
<attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>


Now i need to get the application id and the corresponding ssn number. I tried by using extractValue function. But it's not retuning any value

View 4 Replies View Related

XML DB :: Possible To Remove XMLSchema Restriction From XMLType Column?

Oct 24, 2012

I have an XMLType column that is validated via some XMLSchema. Now I want to remove this restriction and make it just a generic XMLType column. I tried bunch of alter table commands but I couldn't figure out the magic combination. Is this possible?

Here is the actual problem. Unfortunately, we ended up both local and global schemas using the same URL. We have customers out there with both schemas (early customers) or only global schema (new customers). Most of the tables were created before the global schema was added. So, they are referencing the local schema. Now we want to evolve our schema, and I am trying to write a sql script to clean-up this mess. if only global schema exists then (these are late customers)

- do nothingelse if both global and local schema exist then (these are early customers)
- If any table or table.column has dependency on local schema (I can find this out from user_dependencies) I am going to mark those tables/columns as no schema validated. i.e. remove all references to local schema
- delete and purge the local schema
- modify those tables/columns and make them point to the global schema end if
- evolve the schema (only global one is left)

Is there a way to modify a column definition and move its reference from local schema to global schema where both schemas have the same URL? Of course the main constraint is that we do not want to loose customer data. By the way, both local and global schemas are identical in terms of xsd.

is it possible to change XMLSchema/Element settings of an XMLType column from X to Y, X to null, or null to X?

View 2 Replies View Related

Storing Dynamic Dates

Apr 26, 2012

I need to store a date that will always be x years before the current date. Is doing something like

select sysdate -x from dual

Going to result in any weird issues down the road? Also, because I haven't explicitly specified the date, when queried, will the value always be sysdate - x (What I want to happen) as opposed to dateRowWasCreated - x?

View 1 Replies View Related

SQL & PL/SQL :: Storing Unicode In Varchar

Feb 3, 2011

I have a varchar field in a table. When I store the following string, some undefined character is storing.

String to be stored: Test String
String actually stored: Test ¿ string

Please note that here '' is not Hyphen. It is a special character that msword has. when you copy this and paste in a word document you find the difference.

I have listed the character set for CHAR and NCHAR from NLS_DATABASE_PARAMETERS


View 12 Replies View Related

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