I 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 RepliesMy 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.
how 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 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?
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.
i 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?
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?
I have a requirement that is to convert XML message to CSV format and assign this to a "anyType" element. How would I convert to CSV?
View 1 Replies View RelatedI'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 Replies View RelatedI'm trying to get a unique sequential element id for every field in a manual tabular form. Here is the code to generate the form:
SELECT apex_item.hidden(1, lpad(rownum,4,0)) row_num,
apex_item.hidden(2, skillset_demand_id) sdi,
apex_item.text(3, domain, 20, 20, null, 'f03_#row_num#') Domain,
apex_item.text(4, target, 20, 20) Specialization,
apex_item.text(5, skill, 20, 20) Skill
FROM RI_SKILLSET_DEMAND
WHERE WORK_ID = :P511_WORK_IDAccording to the APEX API manual I should be able to set the attribute id with p_item_id*, which in the example of a text item, is the sixth parameter. In line 3 of the code above, I set the p_item_id to 'f03_#row_num#' after having created row_num in line 1.
The HTML I get is:
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_#row_num#" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">What I want is:
<input id="f03_0001" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
<input id="f03_0002" type="text" value="xxxxxxxxx" maxlength="20" size="20" name="f03">
...
etc.
I've tried a lot of things but I can't seem to get parameter substitution inserted into the id attribute so I can get each element uniquely with javascript later.
I 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]....
I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.
The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide
update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........
If i try to use the package DBMS_XMLDOM, i have the following error :
ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored
I think it's because ORACLE XDB component is not installed in my database.
I'm writing a PHP page to display some data from an Oracle database. Unfortunately, I can't copy the code because it's proprietary. One of the columns in the db is of type CLOB. I'm having trouble getting the data from the CLOB column.
The way the code is now, is there is a query string read into a variable, and the query string variable is read into a function that then retrieves the data from the db. Once the result set is returned, it is parsed to get data from all of the columns. The issue is that I've never worked with CLOB data before, so I'm having some difficulty extracting the data for that column. I know I can use the DBMS_LOB.READ function, but I'm not sure how to apply it in this case. The following is a generic form of the query string I'm using with b.clob_col being the column I'm having issues with.
$queryString = <<<EOD
SELECT a.col1,
a.col2,
a.col3,
b.col4,
b.col5,
b.clob_col,
from table1 b
inner join b.col4
on blah1
inner join a.col2
on blah2
where blah
and blah
EOD;
create table top_uid(oldUID number,newUID number);
select * from top_uid;
OLDUID NEWUID
---------------------- ----------------------
1 1001
2 1002
3 1003
4 1004
create table topdUIDXML (uidinfo clob);
insert into topdUIDXML select '<filter name="test" topologyUID="1">' from dual;
insert into topdUIDXML select '<filter name="test2" topologyUID="2">' from dual;
insert into topdUIDXML select '<filter name="ftest" topologyUID="3">' from dual;
insert into topdUIDXML select '<filter name="qtest" topologyUID="4">' from dual;
select * from topdUIDXML
UIDINFO
---------------------------------------
<filter name="test" topologyUID="1">
<filter name="test" topologyUID="2">
<filter name="test" topologyUID="3">
<filter name="test" topologyUID="4">
the topdUIDXML table will contain the oldUID's in the clob XML. need to update the topologyUID in that topdUIDXML with the newUID from the top_uid.
I the table VOYAGERS with the following data.
ID is of type number and DETAILS is of type CLOB.
ID DETAILS
--- --------
100 The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov 10, 2011.
I need to update the record(id = 100) by replacing the url "http:/localhost/icons/sandiego.png" with "http:/localhost/icons/okinawa.png".
I need a procedure where I will pass the ID value, replace string(i.e http:/localhost/icons/sandiego.png) and replace with string (ie. http:/localhost/icons/okinawa.png).
How can I insert xml to clob ? I'm taking from database clob making it in xml type and when I want to insert xml type into clob field it calls and error:
Error(316,43): PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
View 2 Replies View RelatedOk, CLOB columns are such a hassle.
I have a variable in my script: v_field1 VARCHAR2(32000);
This is part of a cursor record:
v_mf_table IS TABLE OF mf_detail%ROWTYPE INDEX BY BINARY_INTEGER;
v_mf_record v_mf_table;
I use a FORALL to insert the data into a table:
FORALL x IN v_mf_record.FIRST .. v_mf_record.COUNT
INSERT INTO monthly_mf_snapshot VALUES v_mf_record(x);
BUT! v_field1 is > 4000 characters. Does this trash my changes of using FORALL? Do I need to deal with 4k chunks in an UPDATE instead?
I have table like-
CREATE TABLE C2X_TEST(NAME VARCHAR2(100),XML_CLOB CLOB,XML_XML XMLType);
here in XML_CLOB column xml files are stored.
Now i want to update XML_XML column with corresponding XML_CLOB value .
Working on building an application which will be in C# (.NET 3.5) and some PL/SQL. However, for the tables which have to store the data, I added a column, comments, to the table and assigned it a data type CLOB. No big deal, except my PL/SQL function is giving me errors if the CLOB built from several varchar2 rows exceeds 4000 characters. I understand the varchar2 field is restricted in 10g, but I was under the impression there wasn't such a restriction on a CLOB (hence the reason we're using it.)
I end up getting:
ORA-6502: PL/SQL: numeric or value error: character string buffer too small
ORA-6512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:The db is 10g (will be upgrading to 11 in October, needs to work on both) on a Windows Server 2003 box (Archive/backup server and db)
I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..
DECLARE
clientid NUMBER;
OUT_RESULTSET clob;
BEGIN
GET_clnts ( clientid, OUT_RESULTSET);
DBMS_OUTPUT.Put_Line('');
END;
[code]...
any way to select the clob value from dblink.?
View 7 Replies View RelatedHow to convert a varchar2 column to CLOB when there is a thousands of records in it.
View 15 Replies View RelatedWe have oracle 10 g and a table contains a clob field . The table size is getting increased day by day . We have decided to zip all the clob data inside the table except last 1 month records.
Table : GENERIC_MESSAGE
Column:
message_id(number)
received_date(date)
message_xml(clob)
How we can do that keeping in mind this is huge data is present in table and huge transaction happened (10k transaction/hr)?