tag:blogger.com,1999:blog-5215551487816981140.post7873988734670519080..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: SELECT * FROM spreadsheet (or How to parse a CSV file using PL/SQL)Morten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger34125tag:blogger.com,1999:blog-5215551487816981140.post-66089290588985552502021-12-14T19:58:16.350-08:002021-12-14T19:58:16.350-08:00Hi, Morten.
I am running similar kind of query as...Hi, Morten.<br /><br />I am running similar kind of query as <br />"select csv_util_pkg.array_to_csv (t_str_array(company_id, company_name, company_type)) as the_csv_data<br />from company<br />order by company_name"<br /><br />But getting an error ORA-06553<br />ORA-06553: PLS-306: wrong number or types of arguments in call to 'ARRAY_TO_CSV'<br /><br />Please help.DGhttps://www.blogger.com/profile/01944805708178489211noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-81163693275199907372018-03-09T01:36:21.072-08:002018-03-09T01:36:21.072-08:00Hi, Morten.
Do you have a pl/sql loader that loa...Hi, Morten.<br /><br /><br />Do you have a pl/sql loader that load by column names not by position based?<br />Thanks in Advance.Coco Sancheznoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-14369080100260333312017-10-24T06:48:43.315-07:002017-10-24T06:48:43.315-07:00Hi Morten
Do you think this approach could handle...Hi Morten<br /><br />Do you think this approach could handle 200k rows?Eirikur Emilssonhttps://www.blogger.com/profile/06452109066672039383noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-77205329048328620022017-05-16T08:01:46.499-07:002017-05-16T08:01:46.499-07:00@Drew: If you need more columns you must add them ...@Drew: If you need more columns you must add them to the t_csv_line type, but also amend the code in csv_util_pkg.clob_to_csv (see https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/csv_util_pkg.pkb#L232 and https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/csv_util_pkg.pkb#L277) to add additional columns.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-9859435663475546482017-05-04T12:24:28.313-07:002017-05-04T12:24:28.313-07:00I see you have to create a type for the CSV parsin...I see you have to create a type for the CSV parsing functionality. It only has 20 fields for the column data??? Does this mean the application is limited to a CSV file with 20 or less columns? If my CSV file has 30 columns, can I just add 10 more entries to the t_csv_line type?Drewhttps://www.blogger.com/profile/05810185785379857057noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-22705923064763651282016-06-14T10:54:00.054-07:002016-06-14T10:54:00.054-07:00@Claudia: The link to the Alexandria PL/SQL Utilit...@Claudia: The link to the Alexandria PL/SQL Utility Library on GitHub is<br /><br />https://github.com/mortenbra/alexandria-plsql-utils<br /><br />The CSV_UTIL_PKG specifically is here:<br /><br />https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/csv_util_pkg.pks<br />https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/csv_util_pkg.pkb<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-30198898609616899572016-05-27T07:05:07.434-07:002016-05-27T07:05:07.434-07:00I want to dowload this package and I can't fin...I want to dowload this package and I can't find the link in this post. When I go to Alexandria page redirectme to github and github redirect me to this page. <br />How can I download this? thanks in advance!Clauhttps://www.blogger.com/profile/08577577254034101652noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-11053061397305493702016-01-04T09:33:45.528-08:002016-01-04T09:33:45.528-08:00Just a tip for large CSV data import
I was once i...Just a tip for large CSV data import<br /><br />I was once in situation when I needed to load huge amount of data (like 250 000 000 rows) from billing system into a DB for some calculations. The problem with downloading the file is obvious - it is too large and performance was not very good.<br />Instead, I saved the file locally and made it external table (create directory, where the file is, create table organization external...) - the point here is that when you do insert or select, Oracle uses data pump which is very fast.Peter Veprekhttps://www.blogger.com/profile/17462900651506682445noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-73836445602750267272015-11-03T07:44:45.740-08:002015-11-03T07:44:45.740-08:00Hi there,
I have created a new solution called LO...Hi there,<br /><br />I have created a new solution called LOB2Table for this task.<br /><br />See my project at sourceforge: https://sourceforge.net/projects/lob2table/<br /><br />It's reliable, handles LFs in delimiters and supports also fixed width formats.<br /><br />And last but not least: it's fast. In typical scenarios it parses up to 15,000 rows per second.<br />In my tests LOB2Table is about 10 to 20 times faster than csv_util_pkg.<br /><br />For example, a CLOB containing data with 20,000 rows each with 20 columns (in total about 5 MBytes) gets parsed in less than 10 seconds. (On my workstation the exact timing is 2.283 seconds). <br /><br /><br /><br />Michaelhttps://sourceforge.net/projects/lob2table/noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-61585226402953310792015-06-06T00:52:40.013-07:002015-06-06T00:52:40.013-07:00@Anonymous: The package handles values enclosed in...@Anonymous: The package handles values enclosed in quotes as a single column. The package does not currently handle line endings in the middle of lines, whether they are enclosed in quotes or not.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-28299755079264129112015-05-26T11:50:56.203-07:002015-05-26T11:50:56.203-07:00I'm just wondering. How is this package dealin...I'm just wondering. How is this package dealing with fields that are enclosed in ".<br /><br />So:<br />1,"my name is, Richard the wise",01-01-2015<br /><br />And this nice construct:<br />1,"my name is,<br />Richard the wise",01-01-2015<br /><br />I.e. a CRLF or LF in the field?<br /><br />Most of the time I find myself using shell scripts again, to use SQL*Loader (as its very fast) and occasionally the SED regular expression features. Many others use Perl.<br /><br />Thoughts appreciated!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-69740290882545301842015-04-24T05:15:00.862-07:002015-04-24T05:15:00.862-07:00This comment has been removed by the author.andysantosahttps://www.blogger.com/profile/00393028664529119879noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-81916234746691916372015-02-02T21:18:12.895-08:002015-02-02T21:18:12.895-08:00Do u have any idea how to fast query when uploadin...Do u have any idea how to fast query when uploading csv from apex and then insert into a table as blob column. After that trying to insert another on table with that csv columns from blob.<br />I have tried 514924 rows.It took more than 1 hr to completely insert all rows.Anonymoushttps://www.blogger.com/profile/00279350159904036694noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-61964770449806408312015-01-28T08:17:26.450-08:002015-01-28T08:17:26.450-08:00Hi
In function csv_util_pkg.clob_to_csv, try to r...Hi<br /><br />In function csv_util_pkg.clob_to_csv, try to remove the concatenations:<br />p_csv_clob || l_line_separator ---> p_csv_clob /*|| l_line_separator*/<br /><br />You will have to manage it differently.<br /><br />Cheers<br /><br />SebastianAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-64398564553072669092014-11-04T01:17:34.358-08:002014-11-04T01:17:34.358-08:00Hi As per previous post, Im also experiences slow ...Hi As per previous post, Im also experiences slow extraction times.. I have 20,000 records.. with an original CSV file of 4.5megs and its taking 8 minutes or so.. to extract from a blob->clob->oracle table.. Anyone any ideas on how to speed the extraction up?<br /><br />Many thanksRichardLhttps://www.blogger.com/profile/09306966522237305577noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-60246750680827648832014-06-04T02:16:35.182-07:002014-06-04T02:16:35.182-07:00Hi, I need your help.
Its takes 1 hour to read a f...Hi, I need your help.<br />Its takes 1 hour to read a file with 66000 lines. I thought this very bad time, any ideas to improve the performance?<br />Elishttps://www.blogger.com/profile/02592287693084061778noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-22257498417650094862014-04-22T13:38:23.843-07:002014-04-22T13:38:23.843-07:00pouvez vous m'aider à comprendre cet article m...pouvez vous m'aider à comprendre cet article mon prof nous a demandé à importer un fichier csv via pl/sql en nous donnant ce lien sachant qu'il nous a signaler à compiler le script "setup/types.sql" et le package "ora/csv_util_pkg"<br />merci de m'aider Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-32571243497065330282014-03-27T07:59:41.685-07:002014-03-27T07:59:41.685-07:00Do you have a version optimized for 11g, without a...Do you have a version optimized for 11g, without all of the dreaded GOTO statements?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-12430302249492202922013-08-16T05:13:04.050-07:002013-08-16T05:13:04.050-07:00I have used the code example you have to parse a c...I have used the code example you have to parse a csv I have in apex and it is going really slow. I have tried multiple things to speed it up. I have tried straight SQL like in your first example, I have tried using a cursor, and I have tried using bulk collect. you have any ideas?<br /><br />declare<br /> <br /> cursor l_cur is<br /> select csv.*<br /> from zfimsmgr.csv_dump d, table(zfimsmgr.csv_util_pkg.clob_to_csv(d.clob_value)) csv<br /> where d.dump_id = 1;<br /> <br /> type l_cur_tab is table of l_cur%rowtype;<br /> v_l_cur_tab l_cur_tab;<br /><br />begin<br /> open l_cur;<br /> loop<br /> fetch l_cur bulk collect<br /> into v_l_cur_tab limit 800;<br /> dbms_output.put_line(v_l_cur_tab.count || 'rows');<br /> exit when l_cur%notfound;<br /> end loop;<br /> close l_cur;<br />end;Anonymoushttps://www.blogger.com/profile/10853467040623893301noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-49301413251136001642012-10-24T10:28:35.848-07:002012-10-24T10:28:35.848-07:00The results when parsing an odd number of double-q...The results when parsing an odd number of double-quotes seem inconsistent. Sometimes included, sometimes now.<br /><br /><br />SELECT * FROM TABLE(csv_util_pkg.clob_to_csv('a,b,c,"d<br />a,b,c",d<br />a,b"c,d<br />"<br />,"<br />",<br />""'))<br /><br />Seanhttps://www.blogger.com/profile/15790298349995376048noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-80705498989749570382010-05-15T05:02:57.737-07:002010-05-15T05:02:57.737-07:00@Rathore: As for running your PL/SQL in Apex, what...@Rathore: As for running your PL/SQL in Apex, what you need is a Process (of type PL/SQL) that runs when the page is submitted (after validations). You would typically set a Condition to only run the Process when a button is clicked.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-55647712580730453792010-05-15T04:56:18.540-07:002010-05-15T04:56:18.540-07:00@Rathore: It depends on what you are trying to acc...@Rathore: It depends on what you are trying to accomplish.<br /><br />Do you want to download a CSV file from a URL? If so, see my second code example (insert/select).<br /><br />Or do you want to allow the user to upload a CSV file via Apex, and then store the data in a database table? If so, see the Apex documentation for information about uploading and downloading (http://download.oracle.com/docs/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#CJAHDJDA). Once you have the CSV data stored in a CLOB column, you can use my third code example (skip lines 18-21 as you will already have your data in a table).<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-48620549226054932352010-05-14T04:41:16.259-07:002010-05-14T04:41:16.259-07:00hi,
i am very new in apex,
could you please tell m...hi,<br />i am very new in apex,<br />could you please tell me how to use <br />your code in apex.<br /><br />i installed your pkg <br />but i don't know how to call your code in apex pl-sql process <br /><br />could you help pleaseRathorenoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-42006536737127553302010-05-08T23:32:21.308-07:002010-05-08T23:32:21.308-07:00our pl/xls product supports binary XLS, we impleme...our pl/xls product supports binary XLS, we implemented an XLS parser in PL/SQL, see plpdf.comAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-50733682324475134252010-04-24T02:32:19.955-07:002010-04-24T02:32:19.955-07:00@Jon: Thanks!
@Dan: Will do when I get some free...@Jon: Thanks! <br /><br />@Dan: Will do when I get some free time... :-)<br /><br />@Tyler: Thanks! Will consider packaging the code up somewhere if the codebase starts to develop.Morten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.com