Snippets and Thoughts of One Oracle Developer

W When your app is ready for users, you want to mask (trap/catch, translate, log) error messages which slipped throught your standard PL/SQL error handler and show user something less revealing. You might also want to store these errors in your own error log table and not in the APEX log. To achieve this you need to define an Error Handling Function in Shared Components, Application Definition Attributes, Error Handling. Use your own name, mine is "apex_error_handling". To create this function you can get inspired like me by the nice example in Oracle documentation, in APEX_ERROR package, section Example of an Error Handling Function . I modified this function a bit to serve my needs: show whole error message with backtrace to APEX developers show just a error type and log_id to common users show constraint names on integrity errors (you can easily convert them to relevant table and columns) log everything (specially the component causing the error and the er...

I I often use views with APEX items, so I can filter data ASAP, not after they are all loaded to the app. It is also a security layer like using contexts for VPD or views shadow schema. To test these views in SQL Developer is tough. Unless you create an APEX session first and set values to items you want to use. Creating session To access page/app items from APEX in PL/SQL you have to create APEX session first: DECLARE in_user_id CONSTANT apex_workspace_apex_users.user_name%TYPE := '&USER_ID'; in_app_id CONSTANT apex_applications.application_id%TYPE := &APP_ID; in_page_id CONSTANT apex_application_pages.page_id%TYPE := 0; -- not important now -- v_workspace_id apex_applications.workspace%TYPE; BEGIN -- find and setup workspace SELECT a.workspace INTO v_workspace_id FROM apex_applications a WHERE a.application_id = in_app_id; -- APEX_UTIL.SET_WORKSPACE ( p_works...

M Many times I create an interactive grid not just on a simple table, but on a more complex view with transformed rows/columns (pivot/unpivot) with the need to store data in multiple tables on submit. I face similar difficulties when handling complex forms. Then I have to create a custom procedure to handle the grid/form submit and then pass columns from grid (or items from form) to this procedure call. That is a lot of tedious work to write these handlers over and over again. Check this GEN package, it will generate all of this code for you and with nice formatting. You can easily customize this to fit your needs and style. Generating the procedure Imagine you have this table and you have more complex view based on this table. CREATE TABLE plan_certifications ( cert_id VARCHAR2(20) NOT NULL, cert_name VARCHAR2(100) NOT NULL, path_id VARCHAR2(30), -- exam_page_link VARCHAR2(256), credly_link VARCH...

D Did you ever wanted to pass dynamic query to interactive report or grid? I did. I have a uploader page where you can upload multiple files to multiple tables. Lets say there is a 60 tables. All these tables have their clones for DBMS_ERRLOG handling so I can catch errors on upload (including wrong data). Now how do you display these tables to the user? I dont want to create 60 reports for 60 error tables, I want just one. Here is how to achieve that. 1) Create form on APEX_COLLECTION S table and hide it, we need just the items to hold real column names. 2) Create interactive report or grid to display the data from your query. SELECT seq_id AS row#, c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050 FROM a...

W When creating page in APEX I need a view for an object (table/view) I would like to present on page in report/grid. This is very tiring. So I created this simple generator. You just need to pass it a list of tables/view you would like to use (and join) in your new view. This will help you to create the list if you need to use more then one table/view. Just change the order of the tables and aliases to your needs. Joins will be done in this order. WITH x AS ( SELECT '%' AS tables_like FROM DUAL UNION ALL SELECT '%' AS tables_like FROM DUAL ) SELECT LISTAGG(t.table_name || ' ' || t.table_alias, ',') WITHIN GROUP (ORDER BY t.table_name) AS tables FROM ( SELECT t.table_name, CHR(96 + ROW_NUMBER() OVER (ORDER BY t.table_name)) AS table_alias FROM x JOIN user_tables t ON t.table_name LIKE x.tables_like AND t.table_name NOT LIKE '%$' GROUP BY t.table_name ) t; Now pass l...

X XML is not cool anymore, but if you are still using it and want to have it in readable form (indented), there is a "AS CLOB INDENT SIZE" trick. There is a little side effect and that is that the empty tags (departure_at) will be shortened. SELECT '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:app=""> <soap:Header> <a:Action soap:mustUnderstand="1"></a:Action> </soap:Header> <soap:Body> <app:message><![CDATA['|| XMLSERIALIZE(DOCUMENT XMLELEMENT("messages", XMLAGG(XMLELEMENT("message", XMLATTRIBUTES(r.message_id AS "id"), XMLELEMENT("location", XMLELEMENT("country_id", r.country_id), XMLELEMENT("source_id", r.source_id) ), XMLELEMENT(...

Y You might be frustrated when you want to add a column to a table and you can't choose a specific position. You can't easily change column order either. Well, this is the best trick. Just change the position in dictionary without moving data, cloning or recreating a table. Or without using DBMS_REDEFINITION . Move specific column to the end of the table: DECLARE in_table_name CONSTANT user_tab_cols.table_name%TYPE := '&TABLE_NAME'; in_column_name CONSTANT user_tab_cols.column_name%TYPE := '&COLUMN_NAME'; BEGIN EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' INVISIBLE'; -- EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' MODIFY ' || in_column_name || ' VISIBLE'; END; / To reorder all columns in desired order you need to provide a full list of colums. Here is how to get the current list: SELECT L...