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.

PLS-00313: "xxxxxx" not declared in this scope

PLS-00313: "string" not declared in this scope

Cause: There is no declaration for the given identifier within the scope of reference. The identifier might be misspelled, its declaration might be faulty, or the declaration might be placed incorrectly in the block structure.
Action: Check the spelling and declaration of the identifier. Also confirm that the declaration is placed correctly in the block structure.


If you do not want some procedures/functions to be publicly available you may not declare them in the package specification.
Declare them only in the package body. The cause of the error is declaration order of the procedures/functions in the package body or lack of forwarding declaration.

For example:

 create or replace package Test_pkg as
  2    procedure Proc1;
  3  end;
  4  /

Package created

create or replace package body Test_pkg as
  2  
  3    procedure proc1 is
  4    begin
  5      proc2;
  6    end;
  7  
  8    procedure Proc2 is
  9    begin
 10      dbms_output.put_line('proc2 is being executed');
 11    end;
 12  
 13  end;
 14  /

Warning: Package body created with compilation errors
Error: PLS-00313: 'PROC2' not declared in this scope

This is happening because we are calling Proc2 which declared later in the package. In this case our choices are:

Declare pro2 before the procedure which calls it

 create or replace package body Test_pkg as
  2  
  3  
  4    procedure Proc2 is
  5    begin
  6      dbms_output.put_line('proc2 is being executed');
  7    end;
  8  
  9    procedure proc1 is
 10    begin
 11      proc2;
 12    end;
 13  
 14  end;
 15  /

Package body created

Use forward declaration.

create or replace package body Test_pkg as
  2  
  3    procedure Proc2;
  4  
  5    procedure proc1 is
  6    begin
  7      proc2;
  8    end;
  9  
 10    procedure Proc2 is
 11    begin
 12      dbms_output.put_line('proc2 is being executed');
 13    end;
 14  
 15  
 16  end;
 17  /

Package body created

SQL> exec test_pkg.Proc1;

proc2 is being executed

PL/SQL procedure successfully completed

How to Fix "ORA-12505, TNS: listener does not currently know of SID given in connect descriptor"

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

To properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.

SIDs vs SERVICE_NAMES
It is important to understand the (slight) difference between what Oracle defines as a SID compared to a SERVICE_NAME, as we’ll use this information to create a proper connection string later on.

In Oracle, the system identifier (or SID) is a local identifier of up to eight characters in length that is used to identify a particular database and differentiate it from other databases on the system.

Often the SID is the prefix word or DB_UNIQUE_NAME that precedes the DB_DOMAIN.

SERVICE_NAMES, on the other hand, represent the names by which database instances can be connected to. A SERVICE_NAME will typically follow the format of the SID followed by the database domain, like so: DB_UNIQUE_NAME.DB_DOMAIN

The TNS Listener
When a client is attempting to connect to an Oracle database, rather than connecting to the database directly, there is a broker service that intervenes and handles the connection request for the client.

This broker application is known as the listener and it performs the task of listening for incoming client requests. When a request is received, the listener processes and forwards that request onto the appropriate Oracle database server using a service handler, which just acts as the connection between the listener and the database server.

TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have tnsnames.ora, which is a configuration file that informs the server about NET_SERVICE_NAMES which are valid database connections. By default, this file is located at ORACLE_HOME/network/admin.

For example, a NET_SERVICE_NAME descriptor in tnsnames.ora may be formatted like this:

myDatabaseNetService =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)(QUEUESIZE = 100))
    (CONNECT_DATA =
      (SERVICE_NAME = bookstore.company.com)
    )
  )

This would define a NET_SERVICE_NAME using the SERVICE_NAME we discussed earlier and connecting to localhost through port 1521.

Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how connection strings are formatted.


Connect via TNS NAME
When connecting through a NET_SERVICE_NAME as specified in your tnsnames.ora config file, you must use the username, password, and then append the NET_SERVICE_NAME with the @ symbol, like so:

username/password@NET_SERVICE_NAME

Connect via NET_SERVICE_NAME
Thus, for our previous NET_SERVICE_NAME descriptor above, the actual NET_SERVICE_NAME we defined was myTestDatabaseNetService, so our connection string might look something like this:

john/Pass123@myTestDatabaseNetService

Connect via SERVICE_NAME
When connecting through a SERVICE_NAME, you’ll also need to add the host and port, along with the / symbol preceding the SERVICE_NAME itself:

username/password@host:port/SERVICE_NAME

Connect via SID
Finally, if connecting without a configured NET_SERVICE_NAME or even SERVICE_NAME, you can do so directly through the SID by using the : symbol instead of the / symbol as with the SERVICE_NAME connection string:

username/password@host:port:SID

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

Useful options that you should know about SQL Developer

1. Search Preferences 
You can easily navigate to any of SQL Developer preferences simply start typing the name of it in the search box. DO NOT HIT ENTER, this will close the preferences dialog with hit “OK” button.

2. Database – Worksheet – Show Query Results in new tabs

Wouldn’t it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. You can turn on/off with the feature with hit the red pin under ‘Query Result’.  Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course, the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.

3. Open Object on Single Click

Good for n00bs, probably annoying to experienced users.

4. Hiding Database Object Types from your Connection Trees
You probably don’t work with EVERYTHING in Oracle – so set your trees to show just what you need. Less scrolling, less searching. More happy

 5. Connection Script Startup
You can setup your connection startup script (LOGIN.SQL) under Database. The contents of the script will be executed every time when the database connection is established.