Show: Today's Messages :: Unanswered Messages :: Polls :: Message Navigator
386 Search Results Found
1 Forum: SQL & PL/SQL «» Posted on: Mon, 19 August 2019 03:47 «» By: sss111ind
Re: xml generation from db
We have faced maximum length error with the above method,however with the below method it worked nicely as follows, WITH emp_det AS ( SELECT ename, sal, dept.deptno dept_dept, emp.deptno emp_dept FROM...
2 Forum: SQL & PL/SQL «» Posted on: Wed, 14 August 2019 03:40 «» By: sss111ind
xml generation from db
Hi All, I want to generate the xml this way as follows. I am generating this using following code and output is also coming as expected. Is it right approach. Please help. --company --companycount (sum(deptcnt)) -- deptno -- deptcn...
3 Forum: Client Tools «» Posted on: Wed, 12 June 2019 09:04 «» By: _jum
Re: generate xml
For the header and the namespace compare: WITH data (scode, sname) AS ( SELECT '@1234567', 'Corbett' FROM dual UNION ALL SELECT '@9876543', 'Begum' FROM dual ) SELECT XMLSERIALIZE( DOCUMENT XMLELEMENT( "...
4 Forum: Client Tools «» Posted on: Wed, 12 June 2019 08:43 «» By: ora9a
Re: generate xml
Hi Michel. I have made some progress. I can get the xml file, but just need to take out the query and headings in the spooled file. CREATE TABLE xmltest ( StudentCode VARCHAR2(50), Given...
5 Forum: Client Tools «» Posted on: Tue, 11 June 2019 11:50 «» By: ora9a
generate xml
Hi, I have to generate an xml file from a query. I have done this code: SELECT TO_CHAR( xmlroot( xmlelement("Students", xmlagg( xmlelement("Student", ...
6 Forum: SQL & PL/SQL «» Posted on: Sun, 09 June 2019 11:45 «» By: John Watson
Re: Backup
Ypu can try to automate it with this sort of thing, run it as SCOTT:SELECT 'insert into ' || table_name || ' (' || ( SELECT rtrim(extract(xmlagg(XMLELEMENT(e, t.column_value.getrootelement...
7 Forum: JDeveloper, Java & XML «» Posted on: Mon, 01 April 2019 09:40 «» By: rs1969
Replace value of node using Xmlquery
I'm trying to replace the value of a node within a XML using the corresponding value from another XML. I have tried using XMLQuery but just can't get it to work. The code attached here is trying to replace the value of node <resourceProvider_MarketPa...
8 Forum: Reports & Discoverer «» Posted on: Sun, 24 March 2019 23:21 «» By: shahzad-ul-hasan
Re: multiples rows into one row.
SQL> select 2 p.mob, 3 (select xmlagg(xmlelement(E, d.stuid || ',')).extract('//text()') AS SE from student d where d.mob = p.mob) 4 from student p; ERROR: ORA-22922: nonexistent LOB value WANT TO USE THIS QUERY IN REPORT BUIL...
9 Forum: SQL & PL/SQL «» Posted on: Mon, 28 January 2019 03:27 «» By: _jum
Re: DataMapper - JSON -> XML - Add a colon in XML tag
You could use namespace notation: WITH data ( id, title) AS (SELECT 114778689, 'My PRODUCT' FROM dual) SELECT xmlelement ("entry" ,xmlelement("g:id", id) ,xmlelement("g:title", title) ...
10 Forum: SQL & PL/SQL «» Posted on: Tue, 31 July 2018 12:23 «» By: Solomon Yakobson
Re: Create JSON from SQL 12C R1
Oops, Double quotes are missing in two places. Corrected version: select '{"departmentId":' || d.department_id || ',"name":"' || d.department_name || '","employees":[' || replace( ...
11 Forum: SQL & PL/SQL «» Posted on: Tue, 31 July 2018 09:47 «» By: Solomon Yakobson
Re: Create JSON from SQL 12C R1
Or: select '{"departmentId":' || d.department_id || ',"name":"' || d.department_name || '","employees":[' || replace( xmlcast( xmlagg(xmlelement(&qu...
12 Forum: SQL & PL/SQL «» Posted on: Sun, 27 May 2018 07:06 «» By: Solomon Yakobson
Re: duplicate part of text message
XML solution: with t as ( select xmlelement("root",xmlagg(xmlelement("a",a))) x from my_tbl_dup_text, xmltable( 'ora:tokenize(.,", ")' ...
13 Forum: JDeveloper, Java & XML «» Posted on: Tue, 24 April 2018 11:30 «» By: SoujanyaSM
Re: Concat XML data in a loop
Thank you so much for the reply! My output data is little complex to use BMS_XMLGEN.GETXML. I another way to do this. This one worked for me - Call the function in a loop. Concat the output into a XMLTYPE(But at the end of the loop data in XMLTYP...
14 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Mon, 01 January 2018 03:38 «» By: marcinsgdz
Re: Export XML
Hellow! MY files XML : select dbms_xmlquery.getxml ('SELECT XMLELEMENT ("USER", XMLELEMENT ("IMIE", IMIE), XMLELEMENT ("NAZWISKO", NAZWISKO), ...
15 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Sat, 30 December 2017 21:25 «» By: Barbara Boehmer
Re: Export XML
You have to double the single quotes that are within the select statement that is surrounded by single quotes. ('SELECT ... where DZIAL=''IT''') select dbms_xmlquery.getxml ('SELECT XMLELEMENT ("USER", ...
16 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Sat, 30 December 2017 20:20 «» By: BlackSwan
Re: Export XML
SELECT XMLELEMENT ("USER", it could work better after you replace line above with line below SELECT XMLELEMENT ("USER"),
17 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Sat, 30 December 2017 17:13 «» By: marcinsgdz
Re: Export XML
where 'DZIAL=IT' SQL Error: ORA-00907: "missing right parenthesis" set long 10000 spool /home/oracle/Desktop/PLIKI_XML/export6.xml; select dbms_xmlquery.getxml(' SELECT XMLELEMENT ("USER", XMLELEMENT (&q...
18 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Sat, 30 December 2017 15:22 «» By: Barbara Boehmer
Re: Export XML
It looks like the problem is probably just the SET LONG value as Littlefoot mentioned. If you do not set it, then the default value is 80. Please see the demonstration below. -- tables and data for testing: SCOTT@orcl_12.1.0.2.0> CREATE TABLE ko...
19 Forum: Application Express, ORDS & MOD_PLSQL «» Posted on: Tue, 26 December 2017 06:43 «» By: marcinsgdz
Export XML
Hellow COL KONTA FORMAT a20000; spool /home/oracle/Desktop/PLIKI_XML/export6.xml; select dbms_xmlquery.getxml(' SELECT XMLELEMENT ("USER", XMLELEMENT ("IMIE", IMIE), XMLELEMENT ("NAZ...
20 Forum: SQL & PL/SQL «» Posted on: Sat, 02 December 2017 02:37 «» By: Michel Cadot
Re: SQL Query
In the contest "how can I poke my eye" here are some examples. SQL> set feed off head off sqlt off sqlbl on SQL> select ' 2 select ' || 3 rtrim(xmlagg(xmlelement(c, 'max('||column_name||'),')).extract('//text()'),',') || ...
21 Forum: SQL & PL/SQL «» Posted on: Mon, 16 October 2017 14:56 «» By: philipebe
How to extract out huge result set in xml format as .xml file
Hi, I have this query in the below format (sample query). So the output of this query is in xml format. And when i run this query on TOAD, i see the output showing as (HUGECLOB), and the size of this CLOB is expected to be close to 2GB. And when i am t...
22 Forum: Marketplace «» Posted on: Wed, 19 July 2017 17:17 «» By: ridham
Re: McOptions: a program which allows to know which options are used in a database
I ran this utility against 12.1.0.2 database with "-c" option and I got following error message: DEBUG: Real-Time SQL Monitoring DEBUG: SELECT used, sofar_exec, dbf_clob FROM (SELECT count(*) used FROM dba_sql_mo...
23 Forum: SQL & PL/SQL «» Posted on: Sun, 09 July 2017 03:33 «» By: keewee279
Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement
I am getting the above error for the following query - only after it runs already for about 30 min. Since I only get the error after this time I was wondering if it is caused by something towards the end of the query, e.g. the .EXTRACT ? I tried usin...
24 Forum: JDeveloper, Java & XML «» Posted on: Thu, 08 June 2017 12:36 «» By: ORA2015
XML file generation
Hello, I have to submit a document in XML format. I was also given a schema to work with but not sure how to apply it. Our data is stored in an oracle database and sql queries are usually done on PL/SQL developer to retrieve output in excel format. Ho...
25 Forum: SQL & PL/SQL «» Posted on: Sat, 24 September 2016 15:01 «» By: Solomon Yakobson
Re: Nested Query- Result Achieved. Performance improvement ?
As Michel already noted I missed you are on 10G where LISTAGG doesn't exists. You can use XMLAGG: with t as ( select job, job || ' ' || min(sal) || nvl2( max(comm) keep(de...
26 Forum: SQL & PL/SQL «» Posted on: Wed, 15 June 2016 12:55 «» By: Barbara Boehmer
Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data
If I enter 2 rows of test data, then run your original query, I get "ORA-01427: single-row subquery returns more than one row". If I run your query that returns 2 rows, then I also get 2 rows. So, it sounds like it is not just a limit problem. ...
27 Forum: SQL & PL/SQL «» Posted on: Wed, 15 June 2016 05:25 «» By: Nikita_01
Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data
ThomasG, The query runs fine if I remove the outermost aggregation and run like this - select XMLElement("lob", XMLAttributes(V1.LOB_ID AS "lobValue"), ( select XMLAGG(XMLElement("language",XML...
28 Forum: SQL & PL/SQL «» Posted on: Tue, 14 June 2016 21:28 «» By: Barbara Boehmer
Re: "ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data
What happens if you remove the TO_CLOB? Do you still get the error? That might help narrow down the problem area. I don't know if it will make a difference or not, but you might try using XMLELEMENT(...).GETCLOBVAL() instead of TO_CLOB(X...
29 Forum: SQL & PL/SQL «» Posted on: Tue, 14 June 2016 09:59 «» By: Nikita_01
"ORA-22813: operand value exceeds system limits" on executing sql returning XMLTYPE data
Hi All, I am facing this issue with below query that fetches data from various table and creates an XML. The query returns data in the format that conforms to XSD file so I cannot modify the query in the way that changes the format of XML nodes. The que...
30 Forum: JDeveloper, Java & XML «» Posted on: Wed, 27 April 2016 19:04 «» By: Barbara Boehmer
Re: Stitching strings into xml (merged)
The following uses corrections to your xml data to make it valid, as previously mentioned by others. I also changed the subid's to different numbers, instead of the same number, so that they can be used for ordering as you stated. The xmltype in the quer...
31 Forum: SQL & PL/SQL «» Posted on: Wed, 06 April 2016 19:25 «» By: Solomon Yakobson
Re: XMLAGG issue
EXTRACT is deprecated: In this particular case all you need is XMLCAST: with emp as (select 'KING' ename from dual union select 'BLAKE' ename from dual union select 'CLARK' ename from dual union select 'JONES' ename from dual union select ...
32 Forum: SQL & PL/SQL «» Posted on: Wed, 06 April 2016 06:46 «» By: Michel Cadot
Re: XMLAGG issue
SQL> set define off SQL> with emp as 2 (select 'KING' ename from dual 3 union 4 select 'BLAKE' ename from dual 5 union 6 select 'CLARK' ename from dual 7 union 8 select 'JONES' ename from dual 9 union 10 select 'SC...
33 Forum: SQL & PL/SQL «» Posted on: Wed, 06 April 2016 05:43 «» By: Xandot
XMLAGG issue
Hello All, Example: with emp as (select 'KING' ename from dual union select 'BLAKE' ename from dual union select 'CLARK' ename from dual union select 'JONES' ename from dual union select 'SCOTT' ename from dual union select 'SMITH' ename ...
34 Forum: JDeveloper, Java & XML «» Posted on: Wed, 16 December 2015 08:20 «» By: wtolentino
Re: Generate XML Extract
thank so much i didn't think about that i can concatenate a string with xmltype. because when i first tried to concatenate a string nside the xmlelement it will not work because of datatype mismatch.
35 Forum: JDeveloper, Java & XML «» Posted on: Tue, 15 December 2015 14:28 «» By: Michel Cadot
Re: Generate XML Extract
You can simply use concatenate operator: SQL> select '<?xml version="1.0" encoding="utf-8"?> 2 ' || xmlserialize (document 3 xmlelement( 4 "Projects", 5 xmlagg( 6 ...
36 Forum: JDeveloper, Java & XML «» Posted on: Tue, 15 December 2015 14:13 «» By: wtolentino
Re: Generate XML Extract
i need to add a header to the xml <?xml version="1.0" encoding="utf-8"?> i tried to use xmlcomment with xmlconcat but it returns with a !-- on the beginning and -- on the end of the element. <!--?xml version="...
37 Forum: JDeveloper, Java & XML «» Posted on: Tue, 08 December 2015 02:15 «» By: Michel Cadot
Re: Generate XML Extract
SQL> select xmlserialize (document 2 xmlelement( 3 "Projects", 4 xmlagg( 5 xmlelement( 6 "Project", 7 xmlattributes(proj_action "a...
38 Forum: JDeveloper, Java & XML «» Posted on: Mon, 07 December 2015 14:14 «» By: wtolentino
Generate XML Extract
i am new to XML and i was attempting to use the xmlElement function in SQL to produced this output: <?xml version="1.0" encoding="utf-8"?> <Projects> <Project action="modify"> <ProgramCode>...
39 Forum: SQL & PL/SQL «» Posted on: Sat, 05 September 2015 07:15 «» By: Solomon Yakobson
Re: Remove Second Occurrence of a word in String
SQL> select * 2 from v$version 3 / BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 ...
40 Forum: SQL & PL/SQL «» Posted on: Sun, 02 August 2015 15:04 «» By: Solomon Yakobson
Re: Generating group of sets from table
Yes, I missed OP is on 10G. Then XMLAGG solution: with t as ( select ps_struct, ps_dia, ps_desc, count(*) cnt from bolts_detail group by ps_struct, ...
Pages (10): [1  2  3  4  5  6  7  8  9  10    »]

Current Time: Sun Aug 25 21:32:17 CDT 2019