[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] 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;

[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

[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] 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] 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] Find DB components versions

Here's a simple query to quickly find the currently installed versions of Oracle DB's components:

SELECT * FROM v$version;

[Oracle] Get user's tablespace quota

To check the current quota status for user's tablespaces, run this query on DBA_TS_QUOTAS:

SELECT tablespace_name, username, ROUND(bytes/1024/1024) MB, ROUND(max_bytes/1024/1024) MAX_MB 

FROM dba_ts_quotas;

remembering that you will need the grants to access it

[Oracle] Get DB tables size in MB

To have an idea of how much space your Oracle DB tables occupy, you may run this query on ALL_TABLES:

SELECT owner, table_name, num_rows, ROUND((num_rows*avg_row_len)/(1024*1024)) MB 
FROM all_tables;

Remember that it is accurate only if you gathered statistics before:

DBMS_STATS.GATHER_SCHEMA_STATS('your_schema_name');

Usually you could have the grants to query it but a more precise query, would be to query DBA_SEGMENTS, which would require you to have additional grants:

SELECT segment_name, segment_type, ROUND(bytes/1024/1024) MB
FROM dba_segments
WHERE segment_type='TABLE'
AND segment_name='your_table_name';

and will include also the data currently in the bin. If you want to ignore it, query DBA_EXTENTS instead

[Oracle SQL] SELECT FROM list of string values

In Oracle SQL, you can perform a SELECT from a list of string values using the DBMS_DEBUG package as:

SELECT *
FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('value1', '...'. 'valueN'))

Which will create a table on the fly populating it with the values passed, one for each new row in a single column. Of course, you'll need to be able to execute that package's procedures/functions.

[SQL] Matrix multiplication

The second assignment in my Coursera Big Data course regarded data analysis in SQL using SQLite3. We used a test dataset, which you can download from here, composed of:

•  matrix.db: a simple database representing two square sparse matrices stored as tables A and B each with row_num, col_num and value as columns
• reuters.db: a database containing a single table frequency(docid, term, count), where docid is an identifier corresponding to a particular file, term is an English word, and count is the number of the times the term appears within the document indicated by docid

I will not post all scripts produced here since many were just simple SELECTs so we'll just focus on the most interesting stuff: matrix multiplication in SQL.

It sounds more complicated than it really is but, given the two sparse matrices, it is possible to compute their multiplication AxB by doing a simple JOIN between A columns and B rows, then GROUPing BY A rows and B columns and finally SELECTing, for each matrix cell, the SUM of the multiplication between the two cell values in both matrices:

SELECT a.row_num, b.col_num, SUM(a.value*b.value)
FROM A a JOIN B b ON a.col_num=b.row_num
GROUP BY a.row_num, b.col_num;

Which is exactly the implementation of the matrix multiplication formula.

[SQL] Oracle IF SELECT statement

While working with PL/SQL, you may encounter the need to use something like:

IF EXISTS(SELECT ...) THEN


or

IF (SELECT ...) IS NULL THEN

but Oracle doesn't allow this, instead, you'll have to alter your function/procedure:

DECLARE
    myFlag INTEGER;
BEGIN
    SELECT COUNT(*) INTO myFlag
    FROM table t
    WHERE [conditions]
  AND ROWNUM = 1;
  IF myFlag = 1 THEN
    --something was returned
  ELSE
    --else no data was found
  END IF;
END;

Using both COUNT and ROWNUM=1 ensures us that, no matter whether something was returned by the SELECT or not, myFlag will in any case be either 1 or 0, which reflects the outcome you'd have had if you could have used the two statements at the beginning of this post.

[SQL] Oracle insert multiple rows with single statement

In Oracle, to insert multiple rows from a single INSERT statement you can:

INSERT ALL
INTO table(column_list) VALUES (values)
INTO table1(column_list1) VALUES (values1)
...
SELECT *
FROM dual;

Should you need to, you may actually write your own SELECT statement. You can insert data into multiple tables at once too.

[SQL] Oracle run procedure

So, you've created your procedure to spare yourself some time and need to execute it, but how?

EXEC your_package.your_procedure; --no input parameters

If the procedure needs parameters you can add them inside the call.

[SQL] Oracle align sequence values after database import

In Oracle, after you import a database which had sequences in it, you may incur in violations of some primary key constraints when attempting to insert new data using said sequences.

This happens because the sequence values are not aligned with the DB tables so the next value the sequence will offer may be already in use. To check this you can:

SELECT MAX(key) FROM table;

To get the last sequence value used for that table key, then:

SELECT sequence_name.NextVal FROM DUAL;

To see where is the sequence at now. If that value is lower than the one you got from the last query, you'll have to correct the sequence. You can do so in many ways:

1- Drop and recreate the sequence with a new (correct) START WITH value:

DROP SEQUENCE sequence_name;

CREATE SEQUENCE sequence_name
START WITH new_correct_value--eg key+1
MAXVALUE how_high_can_it_go
MINVALUE how_low_can_it_be
[other additional parameters based on your needs];

2a- Query the sequence until you reach the desired value reusing multiple times the query we ran to check where it was.

2b- Alter the sequence increment step (either up or down) then query it for a new value, forcing it to reach your desired value in a single shot.

ALTER SEQUENCE sequence_name
INCREMENT BY x
MINVALUE 0;

Where x is your desired increment (must be negative to go back).

Note: by running our check query, you will effectively lose the value returned. If you need a strict control over which key values are generated, you may try the solution at 2b to rewind the sequence and recover the used value.

[SQL] Oracle ORA-21000 "error number argument to raise_application_error of X is out of range"

Oracle allows us to raise user defined exceptions from PL/SQL code with RAISE_APPLICATION_ERROR.

The most common usage is:

RAISE_APPLICATION_ERROR(code, message)

Where code is an integer and message a string. Now, when the exception occurs, the user will see ORA-code: message.

Sometimes you may get the ORA-21000: error number argument to raise_application_error of X is out of range error. This happens because, for user defined errors, the error code (first argument) MUST be between -20000 and -20999 included.