I'm studying the guide Oracle XML DB Best practices and it is ok to add, modify or delete nodes. Also to change the value of an attribute, but I'm looking for adding an attribute.What is exactly the statement, for example to add the attribute modification_date for the root element process for a field xml_data ?
View 1 RepliesI am getting the error when trying to run this test block, this is code is to simulate the production error we are getting...
DECLARE
g_xdoc XMLTYPE;
g_trans_type CHAR (20) := NULL;
BEGIN
SELECT xmltype(c) -- column is a clob column
INTO g_xdoc
FROM test_gil
[code]....
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]....
I m using oracle forms 9i in which i want effect on text fields that will change color of text after mouse cursor move on that field . is it possible in oracle form 9i .
View 6 Replies View RelatedI have to generate a big xml. Each part of the xml needs data from different tables. So I have created two clob to hold the portion of the big xml. How can i have the concatenated clob values as an argument for xml element.
View 1 Replies View Relatedhow to add an xml element in oracle 10gr1 without using Oracle XDB?
View 1 Replies View Related i want to display values of same column with comma seperation like this
SELECT RTRIM(XMLAGG(XMLELEMENT(e,DESCR_INDI || ',')).EXTRACT('//text()'),',') INTO DESCR FROM
STUDENT_SKILL_PER WHERE GROUP_NAME=:GROUP_NAME
AND SKILL_NAME=:SKILL_NAME AND SUB_SKILL_NAME=:SUB_SKILL_NAME
AND TERM=:TERM AND STUDENT_CODE=:STUDENT_CODE AND YEAR_CD=:YEAR_CD;
but the error shown to me that xmlelement must be declared...but when i deploy same query at sql editor it gives me correct result..how i can use it on oracle report 6i... i am using oracle 9i as database.
I have a priority column(possible values are 1 or 0) in a table where
i need to get 70% of high(1) and 30% of low (0) and max i can fetch for select is 50 records.
Eg1: Total if i have 60 in which 20 high and 40 low then 70% of 20 = 14 and remaining should be taken from Low i.e. 36 from low. so total will be 50 transactions.
Eg2: Total if i have 60 in which 40 high and 20 low then 70% of 40 28 + remaining should be taken from Low i.e. 22 from Low.
Eg 3: If i don't have any high then total should be picked from low vise versa.
I have below query but it is having problem when there is no low priority.
SELECT ID,PRI FROM temp tbl WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1
UNION ALL SELECT * FROM temp WHERE pri = '0'
AND ROWNUM < 50-(SELECT COUNT(*) FROM temp WHERE pri = '1' AND ROWNUM < ((70/100)*50)+1)
I am trying to correct NMO NOT SETUID-ROOT (UNIX-ONLY) error. On production database (9..7). Googlng this error - it says to run root.sh as root. should this be run in /usr/local/bin. which is the default location.
I don't believe that there would be any problems - but would like some confirmation -to be safe.
I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?
see the picture:
[URL]........
i am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist.
ORA-22160: element at index [string] does not exist
Cause: Collection element at the given index does not exist.
Action: Specify the index of an element which exists.
how to avoid this.
1 create or replace procedure dept_new
2 is
3 type dept_type is table of dept.deptno%type;
4 type tab_type is table of dept%rowtype;
5 --variable declaration
6 dept_t dept_type;
7 tab_t tab_type;
[code]....
my dept and dept1 tables are both of same structure.
I have a table called dept table with nested collection as below. Nested Table Collection:
create or replace
type courselist as table of varchar2(64) Table Using Nested Table Collection:
CREATE TABLE "FCSDWH_STG"."DEPT"
( "NAME" VARCHAR2(20 BYTE),
"DIRECTOR" VARCHAR2(20 BYTE),
"OFFICE" VARCHAR2(20 BYTE),
"COURSES" "FCSDWH_STG"."COURSELIST"
) Content Of Table:NameDirectorofficecoursesAccountingJames CharlesUNOFCSDWH_STG.COURSELIST('natural science','chemistry','Computer Science','Computer Science')
I am trying to select and print an element from nested table collection using below plsql block.
MY_COURSE VARCHAR2(64 CHAR);
BEGIN
SELECT courses INTO MY_COURSE FROM TABLE(SELECT COURSES FROM DEPT) where courses='chemistry';
DBMS_OUTPUT.PUT_LINE(MY_COURSE);
END; Error Message:
PL/SQL: ORA-00904: "COURSES": invalid identifier
I'm trying to write a generic function for un-nesting child nodes where nesting is deemed unnecessary. Typically when the element only has one child element.
For example, given the following source....
<ROOT>
<ITEM>
<DESCRIPTION>TEST1</DESCRIPTION>
</ITEM>
<ITEM>
<DESCRIPTION>TEST2</DESCRIPTION>
</ITEM>
</ROOT>I actually want.....
<ROOT>
<DESCRIPTION>TEST1</DESCRIPTION>
<DESCRIPTION>TEST2</DESCRIPTION>
</ROOT>
because we think ITEM isn't really required before we deliver XML data.I've been trying to achieve this with a function, where I pass in the XPath to the node I want flattening, something like
function UnNest(pXMLData XMLType, pXPath varchar2) return XMLType...Called like the following...
declare
begin
vXMLData := UnNest(SomeXMLData, '/ROOT/ITEM')
end;I tried using XQuery Update (11gR2), as follows,
[code]....
My destination platform is 11gR2 64 bit but going forward I may need a solution for 10gR2 too. Perhaps there is another way without using XQuery Update?
I'm having issues with users logging into Oracle. I installed it on Ubuntu 12.04 running Oracle 11.2 XE. If i am root, i can run sqlplus just fine and log in. But when I use a regular user account and run sqlplus, it will just stay blank. No error messages or any feedback. I echo $ORACLE_HOME and echo $ORACLE_SID and they come back exactly as they do under the root account. My path is set up just like root has it.
It almost seems like a permission issue but even when i try sqlplus /nolog it stays blank.
[URL]
In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.Below is the output from webservice.
<ResponseEnvelope xmlns:xsd="URL....xmlns="URL....> <ResponseHeader xmlns="URL......
[code]....
ResponseId, MessageVersion etc are element name. the value between element names are to be retrived.
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
1. i register scheme like this
DECLARE
schemaurl VARCHAR2(256);
xmlschema xmltype;
BEGIN
[code]...
/2. i try to insert XML
insert into TMP_SPM_ACTLOGLINE values (xmltype(
'<ACTLOGLINE xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
[code]...
The question is how should the scheme look like to insert this XML?
I have a table with an attribute set to auto increment
how can I insert into the table without specifying the value of that attribute?
We have a 2 node RAC runing on 11.2.0.2 and last night the database was totally un-responsive. when i checked the ADDM I noticed the following:
Waiting for event "cursor: pin S wait on X" in wait class "Concurrency" accounted for 98% of the database time spent in processing the SQL statement with SQL_ID "4b2epo0eaqol9".I am wondering what option do i have here? i am looking to do the following:
1) find the root cause why the database was un-responsive
2) ADDM is listing the query, what options do we have further?
I need know the impact in my oracle database 10g R2, if i change root/oracle passwords in my Oracle RAC environment, my database using ASM and the nodes is in Red Hat 4.7.
View 1 Replies View RelatedI am studying a way to block the access from the root(UNIX) or admin(WIN) users to the database.
Are there some way to block the access by the superusers?
I am installing 11gr2 11.2.0.2 grid infrastructure software. while running root.sh on node1 it got failed.
Start of resource "ora.crsd" failed
CRS-2672: Attempting to start 'ora.crsd' on 'rac1'
CRS-5017: The resource action "ora.crsd start" encountered the following error:
Start action for daemon aborted
CRS-2674: Start of 'ora.crsd' on 'rac1' failed
CRS-2679: Attempting to clean 'ora.crsd' on 'rac1'
CRS-2681: Clean of 'ora.crsd' on 'rac1' succeeded
CRS-4000: Command Start failed, or completed with errors.
Clusterware exclusive mode start of Clusterware Ready Services failed at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 6475.
/u01/app/11.2.0/grid/perl/bin/perl -I/u01/app/11.2.0/grid/perl/lib -I/u01/app/11.2.0/grid/crs/install /u01/app/11.2.0/grid/crs/install/rootcrs.pl execution failed
how can I suppress that su: (to orauser) root on none is filling my message.log up.
View -1 Replies View Relatedi want to produce a function who return XmlType element if she checks that a parameter (that i passed to here is contained in a row in a defined table)
for example
create table sec_form(
form_code VARCHAR2(8)
, sec_code VARCHAR2(5)
) ;
insert into sec_form values ('CSA','section1');
insert into sec_form values ('CSA','section2');
insert into sec_form values ('CSA','section3');
then i got my function
create or replace function checkIfSectionEnabled (elem XmlType,name_section varchar2)
return XmlType
is v XmlType;
CURSOR myCursor IS select * from sec_form where form_code = 'CSA';
begin
open myCursor;
loop
[code]....
now i'm gonna call the function (elem XmlType,name_section varchar2) and pass here "section1" for example ; and she must return me my xmltype element if she find "section1" finds section1 present;
when i try t compile i got:
Error(18,13): PL/SQL: Statement ignored
Error(18,26): PLS-00225: subprogram or cursor 'MYCURSOR' reference is out of scope
I have table called shipper with document_xml as CLOB column
The sample content of document_xml is below
<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/pushpin.png</href></Icon>
and there are other attributes continued
Objective: I want to replace "href" element in each of the record with "[URL] ......" value.
After I update the row the data should look as follows
<placemark id = "5467">
<name> 415-CI-78492</name>
<description><![CDATA[<//!!!!//15249599]]></description>
<ICON><href>http://localhost:63077/Icons/Markers/ship.png</href></Icon>.
I have to do this for bunch of records in a table(table_name = shipper).
Is there an oracle function to replace/update the href element in a CLOB column?
My ORACLE DB version is:
('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 Linux: Version 11.2.0.2.0 - Production');
('NLSRTL Version 11.2.0.2.0 - Production');
I have this XML data stored in a CLOB column:
<Activity>
<Changes>
</Changes>
<Inserts>
</Inserts>
<Definition>
[code]....
I need creating an update that will insert another <Activity>SomeGUID</Activity> into the <Spawned> parent.
Just installed 4.2.2.00.09 and imported a workspace and an application. Worked fine.I noted a page display difference when compared to the exported application (4.1.0.00.32). I have (so far) found this:If I have a button with Pre Element Text set to <div class="dummy"> and Post Element Text set to </div>, Apex generates an extra div around it all.If my button was named P1_SUBMIT I end up with
<div id="P1_SUBMIT_CONTAINER">
<div class="dummy"> <input type="button" value="Search" id="P1_SUBMIT" /></div>
</div>
Is it possible for me to turn this extra div off?
difference between stripe attribute and the AU size.The ASM distributes the data in AU in the disk, and what the role of strip size in this case?
View 2 Replies View Relatedhow to get visual attribute name into a variable ??I have used the below code....
s_v:=get_item_property('slot1',visual_attribute);
but i am unable to get the name of the visual_attribute..
I have been tasked to validate the date format of some attributes in a table. So, to verify the data we will be getting the filed and the format to check against.
View 4 Replies View Related