tag:blogger.com,1999:blog-1378961241059327992.post3950156673868365357..comments2023-04-05T09:35:55.180+02:00Comments on KiBeHa: ODCI dynamic table function parserKim Berg Hansenhttp://www.blogger.com/profile/06491635470794828550noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-1378961241059327992.post-75857133659659102782019-11-02T19:39:09.463+01:002019-11-02T19:39:09.463+01:00Hi, Craig
Well, probably the issue is that I shou...Hi, Craig<br /><br />Well, probably the issue is that I should have provided a link to a source file ;-) I am guessing (since your error is on column 25 of line 100) that you missed the weird way I published the code in this blog post.<br /><br />The *type* body delimited_col_row starts with line 1: "create or replace type body delimited_col_row as". It contains multiple functions. I show each function separately, but each snippet needs to be merged together into one single type body before it will compile.<br /><br />So the 5 snippets that make up the type body needs to be appended to one another. Line 1-100, 102-179, 181-198, 200-250 and 252-261. Then it should compile.<br /><br />I am sorry I didn't describe this very well in the blog post. But you could see my presentation on "Twisting Data": <a href="http://bit.ly/kibeha_datatwist4_pptx" rel="nofollow">http://bit.ly/kibeha_datatwist4_pptx</a>. Part 3 is all about ways to split delimited data, several of which are easier to use than this ODCI function. This ODCI function is mostly useful for very specialized cases.<br /><br />But the presentation has a companion script you can download: <a href="http://bit.ly/kibeha_datatwist4_sql" rel="nofollow">http://bit.ly/kibeha_datatwist4_sql</a>. In that script is also the source code of the delimited_col_row type and type body, which you can then grab directly instead of piecing it together from the blog post.<br /><br />Hope you can get it to run :-)<br /><br />Cheerio<br />/Kim<br />Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-20955661117628502712019-11-02T06:40:06.949+01:002019-11-02T06:40:06.949+01:00When trying to create function type body delimited...When trying to create function type body delimited_col_row I am getting a error. [Error] PLS-00103 (100: 25): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map<br /><br />Any idea what the issue is?Stormblessed https://www.blogger.com/profile/15281009008939586312noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-51165078562094989832019-01-14T22:29:39.377+01:002019-01-14T22:29:39.377+01:00As promised, a post demonstrating the dynamic SQL:...As promised, a post demonstrating the dynamic SQL:<br />https://www.kibeha.dk/2019/01/ora-22905-when-calling-odci-table.html<br /><br />Hope it helps ;-)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-75444133413615131082019-01-09T21:55:49.384+01:002019-01-09T21:55:49.384+01:00Hi again, Daniel
I've tried to use my table f...Hi again, Daniel<br /><br />I've tried to use my table function in PL/SQL now (just in an anonymous block, but that's sufficient to show the problem), and I get the error message: ORA-22905: cannot access rows from a non-nested table item.<br /><br />Even though it should not be a problem, it looks like this is one of the very few cases where SQL that is embedded in PL/SQL is not behaving exactly the same as when it is pure SQL running in the SQL engine alone.<br /><br />I can do a workaround by putting the SQL in an EXECUTE IMMEDIATE to make it dynamic SQL - that way only the SQL engine touches it, not the PL/SQL engine. It shouldn't be necessary and I consider it actually to be a bug in Oracle. However, it is a type of bug that is probably not a high priority to fix (and there might even be arguments that it is not really a bug but has to be this way - who knows ;-)<br /><br />Anyway, I'll do a addendum post demonstrating the use of dynamic SQL as a workaround.<br /><br />Cheerio<br />/KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-49026455932598960212019-01-09T16:10:06.583+01:002019-01-09T16:10:06.583+01:00Hi Daniel
I don’t know of any restrictions that s...Hi Daniel<br /><br />I don’t know of any restrictions that should cause your example to fail. I can try to reproduce when I get to a computer. <br /><br />In the meantime, could you please post which error you are getting?<br /><br />(And a comment: based on your argument name, it looks like you wish to use this to parse JSON. If that is the case, then there are better ways to do that - depending on which database version you are running. )<br /><br />Cheerio <br />/KimKim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-1378961241059327992.post-22785259771467801782019-01-09T15:42:52.422+01:002019-01-09T15:42:52.422+01:00Excellent publication, but I have a question you c...Excellent publication, but I have a question you can use this type of dynamic tables within a function, you can make an example please to know how it can be done. Thanks for everything and greetings.<br /><br />I leave you an example of my code, and I get an error, but if I consult it, I directly execute everything normal.<br /><br />CREATE OR REPLACE TYPE NUBERH.rec_idnmb as object (<br /> id INTEGER,<br /> nombre VARCHAR2(4000)<br />);<br />/<br /><br /><br />CREATE OR REPLACE TYPE NUBERH.tab_idnmb as TABLE OF rec_idnmb;<br />/<br /><br /><br />CREATE OR REPLACE FUNCTION get_test<br />(<br /> p_marcas_json varchar2<br />)<br />RETURN tab_idnmb<br />AS<br /> v_ret tab_idnmb;<br />BEGIN<br />select <br /> rec_idnmb(<br /> ID,<br /> NAME<br /> )<br /> BULK COLLECT INTO v_ret<br /> from table(<br /> delimited_col_row.parser(<br /> '1:Adam:M,2:Belinda:F,3:Carl:M',<br /> 'ID:NUMBER,NAME:VARCHAR2(10),GENDER:VARCHAR2(1)',<br /> ':',<br /> ','<br /> )<br /> )<br /> order by gender, name;<br /> <br /> RETURN v_ret;<br /> <br /> END; <br /><br />Anonymousnoreply@blogger.com