So, the easiest way to implement showing a spinner in a dynamic action is as follows. Dynamic Action 1. Execute JavaScript Code popup = apex.widget.waitPopup(); 2. Execute Server-side Code --your PL/SQL code 3.Execute JavaScript Code popup.remove(); Let’s implement it in Apex. I created a button called btnRun and now it’s time to set up the dynamic action. I hope this helps The post How to implement wait spinner in APEX dynamic action appeared first on Oracle Blog ..read more

If you’re looking to learn all the privileges granted to a specific user, you can utilize the following data dictionary tables. Additionally, you have the option to utilize DBA_ tables. --list system privileges to the current user SELECT * FROM USER_SYS_PRIVS; --list object privileges to the current user SELECT * FROM USER_TAB_PRIVS; --list roles granted to the current user SELECT * FROM USER_ROLE_PRIVS; SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '&USER'; SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '&USER'; SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&USER'; So, let ..read more

So, basically, you can use the following code to create a job. BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'JOB_NAME', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN NULL; END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY; BYHOUR=10,11,12,13,14; BYMINUTE=0;', enabled => TRUE, comments => 'First job schedule'); END; If you want to modify the REPEAT_INTERVAL attribute of a job, you can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure for this purpose. BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ..read more

Ever wondered how to turn pictures or other binary data back into readable text in Oracle PL/SQL? Let’s explore the easy steps of converting BLOB (Binary Large Object) to CLOB (Character Large Object). It’s simpler than you think! CREATE FUNCTION BLOB_TO_CLOB( P_BLOB BLOB, P_CHARSET_ID IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID ) RETURN CLOB IS v_result CLOB; v_blob BLOB := BLOB_TO_CLOB.P_BLOB; v_charset_id INTEGER := BLOB_TO_CLOB.P_CHARSET_ID; v_dest_offsset INTEGER := 1; v_src_offsset INTEGER := 1; v_lang_context INTEGER := DBMS_LOB ..read more

Converting text data from CLOB to BLOB can be tricky. Let’s explore how to make that conversion. CREATE FUNCTION CLOB_TO_BLOB( P_VALUE IN CLOB, P_CHARSET_ID IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID ) RETURN BLOB IS v_result BLOB; v_clob CLOB := CLOB_TO_BLOB.P_VALUE; v_charset_id INTEGER := CLOB_TO_BLOB.P_CHARSET_ID; v_dest_offset INTEGER := 1; v_src_offset INTEGER := 1; v_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX; v_warning INTEGER := DBMS_LOB.WARN_INCONVERTIBLE_CHAR; BEGIN DBMS_LOB.CREATETEMPORARY(LOB_LOC => v_result, CACHE ..read more

Basically, there are several ways to do this. In this post, I show two different ways. Let’s start with a basic example. Set Up create table t1 ( id number, c1 varchar2(50), c2 varchar2(50), c3 varchar2(50), c4 date ); insert into t1 values(1, 'row1', 'txt1', 'txt1', trunc(sysdate)); insert into t1 values(2, 'row1', 'txt1', 'txt1', trunc(sysdate)); --duplicate row insert into t1 values(3, 'row1', 'txt1', 'txt1', trunc(sysdate)); --duplicate row insert into t1 values(4, 'row2', 'txt2', 'txt2', trunc(sysdate)); insert into t1 values(5, 'row2', 'txt2', 'txt2', trunc(sysdate)); --duplicat ..read more

ORA-01704 error occurs when a string literal more than 4000 characters in SQL. In the example below the string length is exactly 4000 characters. So, if one character add it, it will give ORA-01704 string literal too long error. select length( 'this line is 100 characters xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx this line is 100 characters xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx this line is 100 characters xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx this line is 100 characters xxxxxxxxxxxxxxxxxxxxxx ..read more

In this post, I am going to explain how to correct the cardinality estimate in 5 different methods. Cardinality estimate is one of the first place to check in the execution plan when dealing with the query performance. 1) Missing Statistics As you might imagine, if the estimated number of rows is not correct, statistics need to be checked if it is up to date. One of the indications that statistics are stale or missing is if you look at the execution plan it may be written that dynamic sampling is used in the note section. I will disable it on purpose in order not to affect the execution pla ..read more

In this post, I would like to explain a function that is not well known. CUME_DIST analytic function is used for calculating the cumulative distribution of a data in the group of data. It returns value greater than 0 and less than or equal to 1. Let’s create our test table. Set Up create table t_test ( id number primary key, team varchar2(50), point number ); insert into t_test values(1, 'Real Madrid', 85); insert into t_test values(2, 'Barcelona', 72); insert into t_test values(3, 'Manchester United', 75); insert into t_test values(4, 'Arsenal', 55); insert into t_test values(5, 'Chel ..read more

As it is known, NVL and COALESCE functions are used in order to eliminate NULL values. However, NVL function is much more popular in comparison with the COALESCE. Basically, COALESCE takes two or more arguments and return the first non-null argument. The main difference between these two functions is that if you are using a function or a SELECT statement as 2 parameters, you should definitely choose COALESCE. Because, NVL always evaluates both parameters under all conditions. Let’s see this with a simple example. SQL> CREATE FUNCTION FNC_TEST_1(P_NUM NUMBER) 2 RETURN NUMBER 3 IS ..read more