tag:blogger.com,1999:blog-5215551487816981140.post1968219836477219691..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: Working with Office 2007 (OOXML) files using PL/SQLMorten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-5215551487816981140.post-45627030010090187832017-10-23T17:11:26.051-07:002017-10-23T17:11:26.051-07:00Hi.
I have implemented PDF / XLS output. I am try...Hi.<br /><br />I have implemented PDF / XLS output. I am trying to input a word file and output it as a word file. Is there no function to edit the ooxml_util_pkg?<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-29878380685423177242017-04-13T04:59:31.808-07:002017-04-13T04:59:31.808-07:00Hi Morten,
I just discovered your packages and ho...Hi Morten,<br /><br />I just discovered your packages and hoping this will satisfy a requirement I have. <br />The requirement is to produce a 26 slide Powerpoint Presentation with data from the Oracle database 11g, using Apex 4.2 (soon to be 5.1). <br />Most of the slides will have multiple records displaying in a table, all will need to use different queries.<br /><br />Working with one query, the following is the statement<br /><br />Slide 1 has #THE_DATE#<br />Slide 3 has a table with the following in a table:<br />#SUSP1# #REQUIREMENT1# #POC1#<br />#SUSP2# #REQUIREMENT2# #POC2#<br />and so on.<br /><br />For i In (Select rownum, suspense_dt, requirement, poc<br /> From (select suspense_dt, requirement, poc<br /> From my_table<br /> Order by 1, 3)<br /> Where rownum <= 9) -- Only 9 records will fit on one slide.<br />Loop<br /> l_new_file := ooxml_util_pkg.get_file_from_template(l_template, t_str_array('#THE_DATE'||i.rownum||'#','#SUSP'||i.rownum||'#','#REQUIREMENT||i.rownum||'#,'#POC||i.rownum||'#'),t_str_array(To_Char(sysdate,'FMDD Month YYYY'),i.suspense_dt, i.requirement, i.poc));<br />End Loop;<br /><br />I put in debug statements. I see it looping throw the slides. I puts the date on Slide 1. When it gets to slide 3 it doesn't do a replace. Is there something else I need to do?<br /><br />Next question is: How do a incorporate a second query for a different slide? <br /><br />Thanks so much!<br />BeckyBeckynoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-26570974703408367982015-01-05T04:22:29.488-08:002015-01-05T04:22:29.488-08:00hi Morten,
just trying out xlsx_builder_pkg in t...hi Morten, <br /><br />just trying out xlsx_builder_pkg in the OOXML_UTIL_PKG and so far i am loving it but i have a question about setting column number formats.<br /><br />this is what i am trying<br /><br />begin<br />xlsx_builder_pkg.query2sheet( 'select case when mod(rownum,2)=0 then -rownum else rownum end vrow,s.* from tab s' );<br />xlsx_builder_pkg.set_column(1,p_numFmtId => xlsx_builder_pkg.get_numFmt( '#,##0 ;[Red](#,##0)' ));<br />xlsx_builder_pkg.save( 'TEST', 'my.xlsx' );<br />end;<br /><br />looking to see negative numbers in red with brackets no minus sign<br /><br />it completes without error but the format is not applied to the column.<br /><br />what have i missed ?<br /><br />Dandanbromleyhttps://www.blogger.com/profile/17332970814669716062noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-7188847375389285852013-09-15T23:11:23.161-07:002013-09-15T23:11:23.161-07:00Great little utility...
But, I've got some is...Great little utility...<br /><br />But, I've got some issues writing to substitution strings that exist in a table in the document. Only the first column ever gets written, the other columns are ignored.<br /><br />Also, it seems leading and trailing spaces get removed before substitution. Is there a call to trim somewhere in the code?<br /><br />Nick<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-59388064365309311302013-06-13T16:56:28.547-07:002013-06-13T16:56:28.547-07:00THIS IS THE ANSWERE http://msdn.microsoft.com/en-u...THIS IS THE ANSWERE http://msdn.microsoft.com/en-us/library/office/ee922775(v=office.14).aspx <br /><br />xDAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-1728231779641411082013-06-13T15:00:52.242-07:002013-06-13T15:00:52.242-07:00I have the same problem... I don't know if som...I have the same problem... I don't know if some one has the solution?? <br /><br />I can put Chr(13)||Chr(10) :(Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-51594042692596473392013-05-03T08:02:45.183-07:002013-05-03T08:02:45.183-07:00Hi Morton,
Really great solution, but I'm not...Hi Morton,<br /><br />Really great solution, but I'm not sure how to get a string (Adress) across multiple lines. CHR(13)||CHR(10) doesn't seem to work as somebody else also noted. Any idea how to solve this?<br /><br />Regards.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-78633989977079379752013-02-20T16:25:32.400-08:002013-02-20T16:25:32.400-08:00Morten - this is a great tool. When you use the t...Morten - this is a great tool. When you use the template feature with excel is there a way to write a value rather than text?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-27170314706924770242013-02-20T01:26:49.973-08:002013-02-20T01:26:49.973-08:00Hi Morten, Stojan,
I had exactly the same issue a...Hi Morten, Stojan,<br /><br />I had exactly the same issue and tryed to print special characters by using dbms_xmlgen.convert. It again failed and word is giving illegal XML character error.<br /><br />So I fixed it by creating following function:<br />-- function converts string to hex what can be used<br />-- in ooxml document<br />-- created by: David Michel<br />-- created on: 19.02.2013<br />create or replace<br />function func_string_to_xmlhex(<br /> p_string IN NVARCHAR2<br />)<br /> RETURN CLOB<br />IS<br /> v_hex_string CLOB;<br /> v_return CLOB;<br />BEGIN<br /> FOR i in 1..length(p_string) LOOP<br /> SELECT case length(RAWTONHEX(substr(p_string, i, 1)))<br /> when 2 then '&#x00'||RAWTONHEX(substr(p_string, i, 1))||';'<br /> when 3 then '&#x0'||RAWTONHEX(substr(p_string, i, 1))||';'<br /> else '&#x'||RAWTONHEX(substr(p_string, i, 1))||';'<br /> end<br /> INTO v_return<br /> FROM dual;<br /> <br /> v_hex_string := v_hex_string||v_return;<br /> END LOOP;<br /> v_return := v_hex_string;<br /> <br /> RETURN v_return;<br />END;<br /><br />Regards,<br />DavidAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-85394573100562182392013-02-17T01:18:11.854-08:002013-02-17T01:18:11.854-08:00@Stojan: Try encoding the value using the followin...@Stojan: Try encoding the value using the following function:<br /><br />dbms_xmlgen.convert (l_your_value, dbms_xmlgen.entity_encode)<br /><br />http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1013100<br /><br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-426590225835918982013-02-16T15:33:55.909-08:002013-02-16T15:33:55.909-08:00Hi Morten,
I have almost same problem like one o...Hi Morten,<br /><br />I have almost same problem like one of the guys above that has a problem if he tries to replace text in a docx with something containing the GBP £ character. I have the same problem when I use cyrillic characters.<br />The resulting file will not open in Word saying that the £ is an illegal XML character.<br /><br />Any suggestions?<br />Thanks in advance.<br /><br />Stojan Gacov BulgariaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-54803889813733679882013-02-14T23:15:44.890-08:002013-02-14T23:15:44.890-08:00Hi Morten,
that's a pretty solution. Thanks a...Hi Morten,<br /><br />that's a pretty solution. Thanks a lot!<br />But also I have issues by inserting special characters (like €, ä, ü, ö, ß) to documents. Can you let us know how to handle it?<br /><br />Thanks a lot!<br />DavidDavidnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58111184406152708822013-02-14T00:47:05.232-08:002013-02-14T00:47:05.232-08:00@Jiten: Creating a Powerpoint file from scratch wo...@Jiten: Creating a Powerpoint file from scratch would be a lot of work, therefore it is easier to start with an existing file, and add contents to that, via placeholders as I have demonstrated.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-49499969714821315062013-02-11T03:01:39.756-08:002013-02-11T03:01:39.756-08:00Hi Morten,
Your blog is awesome, i learn few thin...Hi Morten,<br /><br />Your blog is awesome, i learn few things which i never heard about. Actually, I want to read data from table and create ppt according to it. Can you please help me on this.<br /><br />Thanks,<br />JitenJiten Patelnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-17779313073629321652013-01-07T09:03:27.460-08:002013-01-07T09:03:27.460-08:00Hi,
Having a problem if I try to replace text in ...Hi,<br /><br />Having a problem if I try to replace text in a docx with something containing the GBP £ character. <br />The resulting file will not open in Word saying that the £ is an illegal XML character.<br /><br />Any suggestions?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-12686309469394837212012-11-28T04:52:02.734-08:002012-11-28T04:52:02.734-08:00his is excellent. I have managed to take a word do...his is excellent. I have managed to take a word doc as a template, substitute text values and save the result.<br />But I'm struggling with embedding line feeds into the substitution strings, for a letter address for example where the whole address is one item (to avoid blank lines)<br /><br />Tried the chr(10)||chr(13) thing but just puts everything on one line.<br />Hope someone can help.<br /><br />Regards<br />MarkAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-32407255766305239642012-08-13T10:11:25.571-07:002012-08-13T10:11:25.571-07:00@dipakchandnani: The link to download the OOXML_UT...@dipakchandnani: The link to download the OOXML_UTIL_PKG is provided in the blog post.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-80736876740131173152012-08-13T09:50:22.117-07:002012-08-13T09:50:22.117-07:00Hi Morten,
You have a great idea.
Before that i d...Hi Morten,<br /><br />You have a great idea.<br />Before that i did not found functionality to access Microsoft Documents from PLSQL. So it's a magic for me also.<br />I have not OOXML_UTIL_PKG package in my database.<br />Can I have a script of package to run in my database.dipakchandnanihttps://www.blogger.com/profile/04771333891884827573noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-27456141224130564442011-03-02T09:59:33.941-08:002011-03-02T09:59:33.941-08:00@Anton: I have added a link to your blog post unde...@Anton: I have added a link to your blog post under the OOXML section of the Alexandria library page.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-69941885887316050152011-02-24T10:25:04.738-08:002011-02-24T10:25:04.738-08:00Have you seen this blog of mine?
Using my unzip pa...Have you seen <a href="http://technology.amis.nl/blog/8090/parsing-a-microsoft-word-docx-and-unzip-zipfiles-with-plsql" rel="nofollow">this</a> blog of mine?<br />Using my unzip package you can query the plain text of a OOXML Word document.<br />Antonblogjehttps://www.blogger.com/profile/14785008192037832811noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-73172574778455994692011-02-23T10:13:26.777-08:002011-02-23T10:13:26.777-08:00Hi Morten,
looks really interesting! Well done!
...Hi Morten,<br /><br />looks really interesting! Well done!<br /><br />PatrickPatrick Wolfhttps://www.blogger.com/profile/16652802762749621200noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-5969137365145517832011-02-21T13:20:14.071-08:002011-02-21T13:20:14.071-08:00Hi Morten,
Thanks a lot for posting this! I was l...Hi Morten,<br /><br />Thanks a lot for posting this! I was looking for something like this last week.<br /><br />MartinMartin D'Souzahttps://www.blogger.com/profile/06992954599899886499noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-18949005640291203142011-02-21T03:25:04.627-08:002011-02-21T03:25:04.627-08:00Hi Morten
That's pretty damn cool, I've h...Hi Morten<br /><br />That's pretty damn cool, I've had quite a few requests in the past to generate XLSX and DOCX files and I've always put it in the too hard basket.<br /><br />Not anymore... thanks again for sharing. <br /><br />P.S. I have no idea where you find the time to do all this majic.<br /><br />Cheers<br />Mattmnolanhttps://www.blogger.com/profile/03589368399513500774noreply@blogger.com