In ORDS, I was working with a GET PL/SQL Handler and I need to produce an output variable inside a JSON Object. My ORDS version, as of today, is 22.1.6. Example: BEGIN ORDS.DEFINE_TEMPLATE( p_module_name => 'test', p_pattern => 'test', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'test', …

Continue reading

Those days, I had a requirement to create an ORDS endpoint that would call a dynamic SQL. Also, my ORDS endpoint was using a GTT and thus first I needed to load this table with some temporary data. Thus, I was left with no other option than using the PL/SQL handler type in my ORDS. …

Continue reading

Below are the 3 views that will list the tables with identity columns in Oracle: USER_TAB_IDENTITY_COLS ALL_TAB_IDENTITY_COLS DBA_TAB_IDENTITY_COLS   PS: I know this post does not make any sense, but sometimes I try to quickly find those table names in Google and for whatever reason, Google Indexer can't find proper websites. So I make this …

Continue reading

Those days, I was building a REST API service for retrieving the columns of a checksum table. The table structure was pretty simple: CREATE TABLE "DEMO" ( "PATH" VARCHAR2(500 CHAR), "MD5_HASH" RAW(16) ); INSERT INTO DEMO ( PATH, MD5_HASH ) VALUES ( 'bundle.xml', '70D53BE37073B31347EE42B622EA4A75' ); COMMIT; So, as you can see, very simple table with …

Continue reading

If you need to retrieve the current APEX server URL using SQL, there are 3 different functions that can help with that. For example, if my current page is: https://example.oracle.com/ords/r/myworkspace/myapp/mypage?session=12345 APEX_PAGE.GET_URL: SELECT APEX_PAGE.GET_URL FROM DUAL; SQL> /ords/r/myworkspace/myapp/mypage?session=12345 APEX_UTIL.HOST_URL: SELECT APEX_UTIL.HOST_URL FROM DUAL; SQL> https://example.oracle.com APEX_MAIL.GET_INSTANCE_URL: SELECT APEX_MAIL.GET_INSTANCE_URL FROM DUAL; SQL> https://example.oracle.com/ords/r/myworkspace/myapp/ If you want to …

Continue reading