[Oracle] Verify if column has not null check

To test whether a column in an Oracle DB has been declared with a NOT NULL check, it is possible to query the dba_tab_columns table:
 
SELECT nullable
FROM   dba_tab_columns
WHERE  owner       = :owner
  AND  table_name  = :table
  AND  column_name = :column
;





Where owner, table_name and column_name are all upper case. That would output (always in upper case):

N = not nullable
Y  = nullable

This will NOT work if the check is done with a constraint instead

[PL/SQL] Convert number to binary string

Here is a simple function to convert a number to a binary string in Oracle PLSQL. This can also easily be converted to an number array instead.

 function num_to_bin(   
  n number   
  )   
  return varchar2   
  is   
  binval varchar2(64);   
  n2  number := n;   
  begin  
  if (n == 0) then  
   return '0';  
  end if;   
  while (n2 > 0) loop   
   binval := mod(n2, 2) || binval;   
   n2  := trunc(n2 / 2);   
  end loop;   
     
  return binval;   
  end num_to_bin;   

[SQL] Excel COUNTIFS - count columns matching criteria in a row

Excel in his arsenal of useful functions has COUNTIFS, basically a count of how many elements in a one dimensional range match a specific criteria. It says multidimensional, but it's not, it's either the same criteria twice for more dimensions or a different criteria. Key point: one list at a time.

However this is a very basic need which is not immediately achievable in SQL as well since we cannot loop over columns in a row. That is, unless we remember that PIVOTing is actually a thing. In this specific case we use the inverse operation, UNPIVOT.

[Oracle] Aurora module exception when compiling Java code on the DB

Sometimes it is possible to receive an exception when compiling Java code or running tools that generate Java code on the Oracle DB with this signature:

ORA-29516: Aurora assertion failure: Assertion failure at some_source:some_line_number

It is usually easy to prevent this error by disabling the JIT compilation on the DB:

alter session set java_jit_enabled = false;

[Oracle] Initialise DB statistics to improve performance

A couple days ago we were analysing a customer issue concerning DB performance on simple and already optimised queries (indexes + hints).

Somehow, the customer had bad performance even after calculating statistics for tables and indexes. Turns out that everything was good except for the fact that Oracle can't imagine future statistics without some help; if a table is only filled after some time that the applications run, it is important to gather statistics again after the first initialisation.

It is also possible however to provide fake statistics immediately after the objects are created or modified so that the DB already has an idea of how the objects will look like and choose different query execution plans than the ones it would use for freshly created objects.

That's what the DBMS_STATS package and its EXPORT_ and IMPORT_ procedures are for.

[Oracle] List uploaded Java resources

After extending functionality on an Oracle DB with Java resources, it is possible to list the available ones and their status with a query on the user_objects table:

SELECT

  object_name

 ,object_type

 ,status

 ,timestamp

FROM

  user_objects

WHERE

  (    object_name NOT LIKE 'SYS_%' 

   AND object_name NOT LIKE 'CREATE$%' 

   AND object_name NOT LIKE 'JAVA$%' 

   AND object_name NOT LIKE 'LOADLOB%'

  ) 

AND object_type LIKE 'JAVA%'

ORDER BY

  object_type

 ,object_name

;

[Oracle] SQL SELECT cast to table

In Oracle it is possible to cast one type to another simply by using the CAST keyword.

This also applies to collections, meaning that the result of a SELECT can be casted to varray or nested table; in this case it is necessary to add the MULTISET keyword and make sure that all elements in the collection have a valid match.

Eg suppose you have a type:

CREATE TYPE int_list AS TABLE OF NUMBER(9);

You can run a query that selects some integers and cast the result set to int_list:

SELECT
  CAST(
    MULTISET(
      SELECT t.int_value
      FROM myTable t
    )
   AS int_list)
FROM dual;

[PL/SQL] existing state of package has been invalidated error

After compiling Oracle packages, it is possible to get a "existing state of packages has been discarded" or "existing state of package has been invalidated" error.

The cause is usually a global variable or constant declared in the package or package body that is re-initialized after the compilation action. The error is thrown to avoid having programs read the wrong state (variable/constant) of the package.

The easiest way to solve this issue is to simply disconnect the session and reconnect.

[TOAD] Oracle explain plan error looping chain of synonyms - fix

TOAD is a powerful client for Oracle DB, although I have to admit SQLDeveloper is not that bad either (and it's also free).

Sometimes while trying to get an explain plan, the error "ORA-01775 - looping chain of synonyms" might occur.

The fix is quite easy: from the options menu go to the Oracle - General section and find the Table field. The value there should be PLAN_TABLE rather than TOAD_PLAN_TABLE.

Save settings, restart TOAD and you're set

[Oracle] Search in sources

In Oracle it's possible to search for text in source files with a query on the all_source object. Of course the resulting data will only include objects that are accessible by your current user.

For example, to search in sources with owner myOwner that include the myString string you can run:

SELECT *
FROM all_source
WHERE owner = 'myOwner'
AND LOWER(text) like '%myString%' --case insensitive search

[PLSQL] Split string by delimiter and store it in VARRAY

Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY.

 DECLARE  
   
 TYPE t_varchar2_varray IS TABLE OF VARCHAR2(4000);  
 list_val t_varchar2_varray := t_varchar2_varray();  
 l_explode_at PLS_INTEGER;  
   
 l_string VARCHAR2(4000) := your_string;  
 l_separator VARCHAR2(1) := your_separator;  
   
 BEGIN  
   
 l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0   
   
 IF l_explode_at != 0 THEN   
        -- split all the values in the list and store them in our temp variable   
      LOOP   
      EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list   
        
       l_explode_at := INSTR (l_string, l_separator);   
       list_val.EXTEND;   
         
       -- if we have other values after this one   
       IF l_explode_at !=0 THEN   
         
            -- store it and keep going   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value   
            l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one   
              
       -- if there are no more values after me   
       ELSE   
            -- store the last one and quit   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));   
            l_string := NULL;   
       END IF;   
         
      END LOOP;   
        
 END IF;  
   
 END;  

Additionally, if you want to remove "empty" values (eg: tabs or spaces) from the string, you can add this code initially:

l_string := regexp_replace(l_string, '[[:space:]]*','');

[SQL] Oracle count elements in nested table

When working with collections in Oracle, you might need to count the number of elements they contain.

In the case of Nested Tables, you can either COUNT(*) after you unnest them:

SELECT COUNT(nt.*)
FROM myTable t, TABLE(t.nested_table) nt

or use the far simpler CARDINALITY function (returns NULL in case of empty list):

SELECT NVL(CARDINALITY(t.nested_table), 0)
FROM myTable t

In case of VARRAYs instead, you must still COUNT the elements but if you're working in PL/SQL though, you can go with:

myVarray.COUNT

[Oracle] Purge schema

Purging a schema in Oracle isn't a straightforward procedure. Usually it's better to DROP the schema or the USER and recreate it.

But if you do not have the permissions to do that, or have other restrictions preventing you to perform the operation, you might find this piece of SQL code useful:

SELECT 'drop '||object_type||' '||object_name||' '||DECODE(object_type,'TABLE', ' cascade constraints;', ';') FROM USER_OBJECTS

This will generate drop statements for ALL objects in the schema it's run on. Just execute it after connecting as the user whose schema you want to purge, then copy the output and run it as script.

[Oracle] wget or curl binaries from OTN website

Usually when you try to download Oracle's JDK or Oracle's DB driver jars you need to manually accept a license agreement before you are allowed to download them.

This obviously is not possible to do when trying to get those resources using wget or curl, but you can work around this by adding a cookie to your request saying that you do accept the licence:

wget --no-cookies --no-check-certificate --header "Cookie: oraclelicense=accept-securebackup-cookie" "RESOURCE URL"

Where RESOURCE URL for latest JDK7 for example is: http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz

If you do not do this, you'll just download a file saying that you need to accept the licence instead

[Oracle] Remove tablespace with missing DBF file

Well, nobody's perfect. But if a software is good we can afford not to be flawless.

Say instead of dropping a tablespace the proper way from your Oracle DB, you deleted its DBF file instead; how can you make Oracle forget about this and let you create a new one with the same name and location?

Luckily, you can still salvage the situation by issuing some commands while connected as sys:

SELECT * FROM sys.dba_data_files;

Now find your tablespace and copy the value from the FILE_NAME column, then delete the file association:

ALTER DATABASE DATAFILE 'file_name_we_got_before' OFFLINE DROP;

Finally, drop the tablespace itself:

DROP TABLESPACE your_tablespace INCLUDING CONTENTS;

And you're back in business

[SQL] Oracle subquery in join statement

In Oracle, it's possible to use sub queries in a join statement by giving an alias to the subquery and joining on that alias:

SELECT a.column1, a.column2, c.column3

FROM a JOIN (

SELECT b.column1, b.column2, b.column3

FROM b

) c

ON (a.column1 = c.column1 AND a.column2 = c.column2)

Obviously you would never write a SIMPLE query EXACTLY as the example above, it's just to show the mechanics when you actually need to create a slightly more complex one

[Oracle DB] Create and schedule a job

To create and schedule a basic job on an Oracle DB, you'll need to create:

• A script performing the desired action
• A schedule
• A job

The script can be passed as code directly to the job, a function/procedure, a package function/procedure.

Step 1: Create package with our function

CREATE OR REPLACE PACKAGE myPackage AS   

PROCEDURE myProcedure;  

END;

CREATE OR REPLACE PACKAGE BODY myPackage AS

PROCEDURE myProcedure AS

--variable declaration here

BEGIN

  --something

  NULL;--not actually needed

END myProcedure;

END;

Step 2: Create schedule and job. Refer to the linked documentation for more information regarding the various parameters

 BEGIN  
   
 --create schedule  
 DBMS_SCHEDULER.CREATE_SCHEDULE (           
      repeat_interval => 'FREQ=YOUR_FREQUENCY',     
      start_date => TO_TIMESTAMP('START_DATE', 'FORMAT'),  
      schedule_name => '"mySchedule"');  
       
 --create job on mySchedule  
 DBMS_SCHEDULER.CREATE_JOB (  
       job_name => 'myJob',  
       job_type => 'PLSQL_BLOCK',  
       schedule_name => '"mySchedule"',  
       job_action => 'BEGIN myPackage.myProcedure; END;',  
       number_of_arguments => 0,  
       job_class => 'DEFAULT_JOB_CLASS',  
       enabled => true,  
       auto_drop => true,  
       comments => 'SOME_DESCRIPTION');  
 END;  
   

[Oracle] Run commands in different schema

In Oracle, it is possible to change the current schema/user with the ALTER SESSION statement:

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

All subsequent commands will use that schema as default when nothing is specified. Of course, you'll need permission to execute the ALTER SESSION and all other statements on the new schema.

[SQL] Escape special characters in LIKE statement

When using the LIKE statement in a SQL query, there are special characters which have a particular meaning such as _ (single character) and % (sequence of characters) which have to be escaped if you intend to actually search for them in the string.

You can do so as:

LIKE 'pattern' ESCAPE 'escape_character'

for example:

SELECT name
FROM mytable
WHERE name LIKE '%John\_D%' ESCAPE '\';

Which will search for any string containing "John_D"

[Oracle] Stop/start database and listener from command line

You can start or stop an Oracle database instance (you can dbca to configure one) from command line via the SQLPlus tool by connecting to it as SYSDBA user:

sqlplus
CONNECT SYS as SYSDBA

then to stop:

SHUTDOWN NORMAL

and to start:

STARTUP

To manage listeners on Linux (use netca to configure one) you can run instead:

$ORACLE_HOME/bin/lsnrctl stop|start

Where is the name of the listener as configured via netca or found as the output of:

ps -ef | grep tnslsnr