Category: Oracle Database General

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

Today, I was trying to apply the database 12.1.0.2 BP 221018 (October 2022). As usual, for all bundle patches, I have to call datapatch utility twice due to some strange errors during the first execution. However, this time the patch apply process was giving me this strange ORA-20000 error: ORA-20000: Insufficient privileges to analyze an …

Continue reading