Selecting DISTINCT values from an associative array

Problem: Is there any built-in function in Oracle for selecting distinct values from an associative array.

Answer: You can use SET operator but take into account that the set operator doesn't work on nested tables of PL/SQL records. You can use SET on nested tables of single values.

               

Look the following example that shows how to get DISTINCT value form an associative array.

SQL>

DECLARE

    TYPE num_list IS TABLE OF NUMBER;

    id_list       num_list := num_list();

    id_list_2     num_list := num_list();

BEGIN

    FOR i IN (SELECT 1 AS value

                FROM all_tables

               WHERE ROWNUM < 10

             )

    LOOP

        id_list.EXTEND;

        id_list(id_list.LAST) := i.value;

    END LOOP;

    FOR o IN id_list.FIRST .. id_list.LAST

    LOOP

        dbms_output.put_line('Org: '||id_list(o));

    END LOOP; 

    id_list_2 := set( id_list ) ;

    FOR d IN id_list_2.FIRST .. id_list_2.LAST

    LOOP

        dbms_output.put_line('New: '||id_list_2(d));

    END LOOP; 

END;

/

--Output

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

Org: 1

New: 1

How to set current value of a sequence without dropping/recreating

Oracle does not let you change directly the value of a sequence. If you need to change its value, you should re-create the sequence.
On the other hand, we can use a trick to change the value of a sequence without dropping/recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

SQL> CREATE SEQUENCE test_seq

            INCREMENT BY 1

            START WITH 1000

            MINVALUE 1

            MAXVALUE 99999999;

SQL> SELECT last_number

       FROM user_sequences

      WHERE sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

1000

SQL> ALTER SEQUENCE test_seq INCREMENT BY -500;

SQL> SELECT test_seq.NEXTVAL FROM dual;

SQL> ALTER SEQUENCE test_seq INCREMENT BY 1;

SQL> SELECT last_number

       FROM   user_sequences

      WHERE  sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

500

By this way, we can set the new value of the given sequence.

Just for fun and for easy use of that logic in the future, let’s create a procedure for it.

CREATE OR REPLACE PROCEDURE Set_Sequence_value

              ( pi_owner         IN VARCHAR2,

                pi_seq_name      IN VARCHAR2,

                pi_new_seq_value IN NUMBER)

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    l_last_number            NUMBER;

    l_incr_by                NUMBER;

    CURSOR c_get_seq_param IS

    SELECT increment_by

      FROM all_sequences

     WHERE sequence_owner = UPPER (pi_owner)

       AND sequence_name = UPPER (pi_seq_name);

BEGIN

    OPEN c_get_seq_param;

    FETCH c_get_seq_param INTO l_incr_by;

    CLOSE c_get_seq_param;

    IF l_incr_by IS NOT NULL

    THEN

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.CURRVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY '||(pi_new_seq_value - l_last_number);

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.NEXTVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY ' || l_incr_by;

    END IF;

END Set_Sequence_value;

Note: In order to not affect the transaction control, the procedure is created under AUTONOMOUS_TRANSACTION.

Have a fun and thank you for your time to read this article.

Change a user's password in Oracle

Question: How do I change the password for a user in Oracle?

Answer: To change a user's password in Oracle, you need to execute the alter user command.

Syntax:
The syntax for changing a password in Oracle is:
ALTER USER user_name IDENTIFIED BY new_password;

Parameters or Arguments:
user_name - The user whose password you wish to change.
new_password - The new password to assign.

Example
Let's look at an example of how to change a password for a user in Oracle/PLSQL.

ALTER USER smith IDENTIFIED BY Pass05_NM;

This example would change the password for the user named smith and set the new password to Pass05_NM.

ORA-22950 cannot ORDER objects without MAP or ORDER method

The following is the cause of this error:

The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared.
But an object type, such as a some_type, which can have multiple attributes of various data types, has no predefined axis of comparison.
To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
The action you can take to resolve this issue: Define a MAP or ORDER method for the object type.

For more information, you can see https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000).

RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The error number and message is displayed to the user.

Syntax:
RAISE_APPLICATION_ERROR ({error_number}, {error_message});

{error_number} - The Error number must be between -20000 and -20999

{error_message} - The Error message is the message you want to display when the error occurs


Using in Execution section

BEGIN
...
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
...
END;


Using in  Exception section:

BEGIN
...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20003, 'This is another user error message');
END;


Examples:

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
l_number = 2

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
There is no output due to our ERROR
line 19: ORA-20001: This is a user error message

ORA-06512: at line 5