tag:blogger.com,1999:blog-5215551487816981140.post4007421636683962454..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: The APEX_JSON package: Generating JSON from PL/SQLMorten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5215551487816981140.post-82873444530907021092014-07-17T10:56:45.897-07:002014-07-17T10:56:45.897-07:00Hi Morten,
Thank you so much for the help. Tha...Hi Morten, <br /><br /> Thank you so much for the help. That's exactly what I was looking.. <br /><br />-SivaAnonymoushttps://www.blogger.com/profile/16480398065974475916noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-32092713604546559162014-07-16T23:26:26.518-07:002014-07-16T23:26:26.518-07:00@Siva: Consider the following example:
create tab...@Siva: Consider the following example:<br /><br />create table my_table (first_name varchar2(100), last_name varchar2(100), zip_code varchar2(10), country_code varchar2(2));<br /><br />create or replace package my_package<br />as<br /><br /> g_my_table_rec my_table%rowtype;<br /> <br /> type t_my_custom_record is record (<br /> favorite_color varchar2(50),<br /> favorite_car varchar2(50),<br /> favorite_fruit varchar2(50)<br /> );<br /><br /> g_favorite_rec t_my_custom_record;<br /><br />end my_package;<br />/<br /><br />Given the above, you can now use apex_util_pkg.get_items to populate either of the global variables in the package header, with the following code in an Apex page process;<br /><br />begin<br /> apex_util_pkg.get_items (:app_id, :app_page_id, p_target => 'my_package.g_my_table_rec');<br />end;<br /><br />The items on the page must have names matching the record, for example P1_FIRST_NAME, P2_LAST_NAME, etc.<br /><br />Or, if you use p_target => 'my_package.g_favorite_rec', then your item names should be P1_FAVORITE_COLOR, P1_FAVORITE_CAR, etc.<br /><br />After having populated the items, you can then refer to the values using my_package.g_my_table_rec.first_name (ie package_name.record_name.field_name).<br /><br />Hope this helps.<br /><br />You can read more about record types here: http://www.oracle.com/technetwork/issue-archive/2012/12-may/o32plsql-1578019.html<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-8390807315773110092014-07-16T05:09:34.093-07:002014-07-16T05:09:34.093-07:00Hi Morten,
My comments are not related to the ab...Hi Morten, <br /><br />My comments are not related to the above post. I didn't know what is best way to contact you. I need some help using apex Alexandria library. <br /><br />In Alexandria library, I want to use apex_util_pkg.get_items procedure. The purpose of this procedure is to get multiple item values from page into custom record type. <br /><br />In the remarks section of this procedure following comments were posted.<br />==========<br />this procedure grabs all the values from a page, so we don't have to write code to retrieve each item separately<br /> since a PL/SQL function cannot return a dynamic type (%ROWTYPE and PL/SQL records are not supported by ANYDATA/ANYTYPE),<br /> we must populate a global package variable as a workaround<br /> the global package variable (specified using the p_target parameter) must have fields matching the item names on the page<br />=============<br />I'm new to pl/sql. Can you show me with an example as to how I can invoke this method?<br /><br />I've to implements a audit like feature across 50 tables.. I want to handle that using this library. <br /><br />Thanks for your help.<br /><br />-SivaAnonymoushttps://www.blogger.com/profile/16480398065974475916noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-8661862982672056152014-07-15T07:03:25.408-07:002014-07-15T07:03:25.408-07:00@Christian:
1. Good point. I would normally use s...@Christian:<br /><br />1. Good point. I would normally use static SQL as much as possible, but for testing purposes I just wanted a simple way to run any SQL statement in my test app. I have added a disclaimer about this in the text above.<br /><br />2. Cool! I was looking at the xmltype documentation, but I missed the fact that the xmltype constructor can take an object type as input directly. I have updated the blog post accordingly.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-1694941555729313672014-07-15T03:19:18.582-07:002014-07-15T03:19:18.582-07:00Hi Morten,
nice to see that you wrote about the n...Hi Morten,<br /><br />nice to see that you wrote about the new APEX_JSON APIs. I have just 2 very minor comments:<br /><br />1. open l_cursor for :P2_SQL<br /><br />This example can be dangerous, because it allows SQL injection. The reason why we created the cursor write() API was that, contrary to APEX_UTIL.JSON_FROM_SQL, it makes it easy to use bind variables, as in<br /><br /> open l_cursor for<br /> select *<br /> from emp<br /> where deptno = :P2_DEPTNO;<br /><br />2. Object to XMLType conversion<br /><br />The XMLType constructor can automatically convert object types, no need for anydata.convertObject:<br /><br /> select xmltype(t_car('Toyota', 'Yaris', 2014, 1234)) from dualAnonymousnoreply@blogger.com