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?
How can I add an attribute node to an existing xml in a xmltype?
Say I have
declare
t_xml xmltype;
begin
t_xml := xmltype( '<x a="1"/>' );
end;How can I manipulate this variable t_xml to become
<x a="1" b="2"/>Anton
I believe, in a multiple RAC Physical Standby nodes only one node applies the archivelogs to the standby database. How you can find which node is the APPLY NODE using a SQL query?
I know you can veiw this information by querying the standby configuration using DGMGRL but how would you find out this information from a remote client using SQLPlus/PLSQL?
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
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].....
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.
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]....
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]....
current environment
Oracle VM 2 Node RAC cluster
Oracle 11G 11.2.0.3.0
Oracle 11g rel 2 GI
on current 2 node cluster we have GI and RAC db configured Nodes: vmorarac1,vmorarac2
we shutdown vmorarac1 to clone it to vmorarac5
on new node I have changed the hostname to vmorarac5 In /etc/sysconfig/network-scripts/ifcfg-eth0 change ip to new ip and same for /etc/sysconfig/network-scripts/ifcfg-eth1 152.144.199.210,152.144.199.211
mad echanges to /etc/hosts on vmorarac1/2 for 2 new IP address assigned to vmorarac5
152.144.199.171 vmorarac1.pbi.global.pvt vmorarac1
192.168.2.30 vmorarac1-priv.pbi.global.pvt vmorarac1-priv
152.144.199.184 vmorarac1-vip.pbi.global.pvt vmorarac1-vip
[Code]....
Query is is that all ok to reboot the new node to reflect changes as below and change the hostname to vmorarac2
152.144.199.210 vmorarac2.pbi.global.pvt vmorarac2
192.168.2.32 vmorarac2-priv.pbi.global.pvt vmorarac2-priv
152.144.199.211 vmorarac2-vip.pbi.global.pvt vmorarac2-vip
hostname change in /etc/sysconfig/network
On new node vmorarac5, there is alreay software for RAC DB and GUI cloned from vmorarac1
addnode.sh is used to add existing node, but as part of pre-requisite configuratoin is there any config step missing
We have oracle database 10g(10.2.0.4) running on AIX 5.1. we have installed 2-node RAC on another server AIX 6.1 . Now we want to migrate our single node database to new 2-node rac environment. how we can move/migrate/cluster-aware our single node database to 2-node rac?
View 3 Replies View RelatedI am installing Oracle 10g Clusterware in RHEL 5 Server. When i run root.sh in the second node the last step Running vipca(silent) for configuring nodeapps failed. Then i run VIPCA manually in second node and configured vip configuration successfully,but when i checked the post-checks for cluster services the Checking existence of VIP node application,Checking existence of ONS node application and Checking existence of GSD node application in second node failed. I am able to ping both the servers one another with vip name successfully and the CSS,CRS and EVM appears healthy in both nodes. 1. Does the vip configuration is proper? If no what is causing this error and how to rectify it? Find the output below
[root@Rhel52 bin]# ./crsctl check crsCSS appears healthyCRS appears healthyEVM appears healthy[root@Rhel52 bin]#[root@Rhel52 bin]# clear[root@Rhel52 bin]#[root@Rhel52 bin]# ./crs_stat -tName Type Target State Host------------------------------------------------------------------------------------ora.rhel5.gsd application ONLINE ONLINE rhel5ora.rhel5.ons application ONLINE ONLINE rhel5ora.rhel5.vip application ONLINE ONLINE rhel5ora.rhel52.gsd application ONLINE ONLINE rhel52ora.rhel52.ons application ONLINE ONLINE rhel52ora.rhel52.vip application ONLINE ONLINE rhel52 [root@Rhel52 bin]#./runcluvfy.sh stage -post crsinst -n
[code]....
I would like get more details about single node RAC.. What is single Node RAC?..
1). just configure only one server instead of two node... same RAC installation.. cluster installation and shared disk configuration in single server itself.. in future we can add node to this ...
2). in 11gR2 having new feature Single Node RAC.. Multiple instances running on same node and configure using Cluster...
is there any advantage using single node rac(other than online upgrade to full RAC).
is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?
View 4 Replies View RelatedI have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.
The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.
Assume that we have table Test with a column TestId and Status. This table is having 10,000 records. Now, we need to write a stored procedure that does the following:
1) Select top 10 rows whose "Status = NEW'
2) Update the selected top 10 rows with "Status = PROCESSING"
3) Return those top 10 rows.
Assuming there are hundreds of such requests coming to Oracle database, how can we make sure each request NEVER gets a duplicate record at any given point of time and how to fetch the records that have been updated individual request?
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?
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.
I'm trying to add the third node to my existing two node cluster. In fact i succeded adding the node but i have the following problem:
-when i start node 1 and 2, everything is ok, but on node 3 startup one of the other nodes fails
-this happens in any combination: start 1 and 3, everything is ok. when i start node 2, node 3 fails
The asm configuration (disks, permissions, etc) is the same on all three nodes.find below the asm and crsd log details from the failure time:
alert_ASM.log
NOTE: enlarging ACD for group 1/0x124851d1 (DATA)
WARNNING: cache read a corrupted block group=DATA dsk=4 blk=1 from disk 4
+NOTE: a corrupted block from group DATA was dumped to /u01/app/oracle/diag/asm/+asm/+ASM3/trace/+ASM3_rbal_4109.trc+
WARNNING: cache read(retry) a corrupted block group=DATA dsk=4 blk=1 from disk 4
[code]...
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
as
[Code].....
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!.
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?
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"?>
<indexAttributes>
<attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App123 "/>
<attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>
[Code]...
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
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?
I have included a calender control in my form and its working fine.Now, my problem is how can I automatically place the calender next to the text-item (for example, "block.invdate")if the push button is pressed to call a calender.
View 1 Replies View RelatedSay, a table has existing columns of A, B, D, and E.Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
View 6 Replies View RelatedHow to add a new column to specified position in a existing table.I have using the oracle database 10g.This below code is not working in oracle 10 g
example:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER FIRST:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER AFTER JOB:
SQLserver in stop condition when i play it. it again stop
View 1 Replies View Relatedsuppose i have a file named chk.txt and I have write 10 line in that file and i just want to write some text at line 5 or line first then how we can do this ?
View 3 Replies View RelatedI have to convert string 1234567 as 123,456,7 .
note 1234567 is a string.
have a column cstomer_ts from table customer, which contain records like:
"cstomer account"||"PRIVATE"||"foundation"
"cstomer account"||"PRIVATE"||"foundation of money"
"cstomer account"||"PRIVATE"||"Moneycost"now i want to fetch the third record from the column cstomer_ts which starts with 'Foundation'
i mean i want to fetch only 3rd postion frm the column cstomer_ts and tht should starts with 'Foundation'
I want to get current mouse cursor position dynamically,what should i do.
View 3 Replies View Related