Integer to Binary Conversion in Oracle SQL

Interestingly enough, Oracle does not have a built-in function to convert Decimal numbers (i.e. integers) into Binary. This post offers an elegant way of doing so.

The following script is intended to be executed in SQL*Plus, so it uses some SQL*Plus commands:

column bin format a40
undefine N
SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=&&N;

Result (for N=400):

BIN
-------------
110010000

Result (for N=1401):

BIN
------------
10101111001

Explanation:

How many digits may the resulting binary string have? The answer comes from Math: not more than LOG(2, N) + 1. Let’s first generate a numeric range from 1 to LOG(2,N)+1:

SELECT LEVEL
FROM dual
CONNECT BY LEVEL<=LOG(2,&N)+1

Result (for N=1401):

 LEVEL
------
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11

Alternatively, we can use mathematically equivalent condition in the CONNECT BY clause using POWER instead of LOG function:

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL)<=&N*2

or

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&N

Now, we will check every bit of the desired result (i.e. binary representation of N) by using BITAND function:

SELECT LEVEL, BITAND(&&N, POWER(2,LEVEL-1)) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          4
    4          8

Positive values in the bit column refer to a bit 1 in the corresponding position (in reverse order) of the binary value. It’s easy to turn those values to 1 by using SIGN function:

SELECT LEVEL, SIGN(BITAND(&&N, POWER(2,LEVEL-1))) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          1
    4          1

Here, we can see that we need to concatenate the values in the bit column in reverse order. This is very easy to do using LISTAGG function:

SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

BIN
----------
1100

Note that we sorted all the rows in descending order of the LEVEL to obtain the correct order of bits.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Show Leading Spaces in Character String in SQL*PLus

SQL*Plus with its default settings ignores leading spaces when you attempt to output a character string variable using DBMS_OUTPUT.PUT_LINE procedure:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

As you can see in the above example, two leading spaces are trimmed.

To make SQL*Plus showing the leading space characters, we need to change the following setting:

SET SERVEROUTPUT ON FORMAT WRAPPED

Now, the spaces will be preserved:

SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

Alternatively, you can set the format to TRUNCATED:

SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
  12345

PL/SQL procedure successfully completed.

The default setting is WORD WRAPPED:

SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
SQL>
SQL> exec DBMS_OUTPUT.PUT_LINE('  12345')
12345

PL/SQL procedure successfully completed.

SQL> SHOW SERVEROUTPUT
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Round-Robin Schedule SQL Puzzle

Puzzle: With a single SELECT statement create a schedule of play for a round-robin tournament. The query should work for odd or even number of players. For odd number of players, the player with bye should be listed last for that round.

You can read about round-robin tournament definition here: https://en.wikipedia.org/wiki/Round-robin_tournament

Expected Results:

4 players:

 ROUND PAIRING
------ --------
     1 1 vs 2
     1 3 vs 4
     2 1 vs 4
     2 2 vs 3
     3 3 vs 1
     3 4 vs 2

OR

ROUND PAIRING
----- --------
    1 1 vs 2
      3 vs 4

    2 1 vs 4
      2 vs 3

    3 3 vs 1
      4 vs 2

5 players:

  ROUND PAIRING
------ --------
     1 5 vs 1
     1 4 vs 2
     1 3 - bye
     2 1 vs 2
     2 5 vs 3
     2 4 - bye
     3 1 vs 4
     3 2 vs 3
     3 5 - bye
     4 2 vs 5
     4 3 vs 4
     4 1 - bye
     5 3 vs 1
     5 4 vs 5
     5 2 - bye

OR

ROUND PAIRING
----- -------
    1 5 vs 1
      4 vs 2
      3 - bye

    2 1 vs 2
      5 vs 3
      4 - bye

    3 1 vs 4
      2 vs 3
      5 - bye

    4 2 vs 5
      3 vs 4
      1 - bye

    5 3 vs 1
      4 vs 5
      2 - bye

6 players:

ROUND PAIRING
----- --------
    1 5 vs 1
    1 4 vs 2
    1 3 vs 6
    2 1 vs 2
    2 5 vs 3
    2 6 vs 4
    3 1 vs 4
    3 2 vs 3
    3 5 vs 6
    4 1 vs 6
    4 2 vs 5
    4 3 vs 4
    5 3 vs 1
    5 6 vs 2
    5 4 vs 5

OR

ROUND PAIRING
----- -------
    1 5 vs 1
      4 vs 2
      3 vs 6

    2 1 vs 2
      5 vs 3
      6 vs 4

    3 1 vs 4
      2 vs 3
      5 vs 6

    4 1 vs 6
      2 vs 5
      3 vs 4

    5 3 vs 1
      6 vs 2
      4 vs 5

 Solution (SQL*Plus script):

accept players prompt "Enter the number of players: "
set pagesize 100
break on "ROUND" skip 1
col pairing for a10

WITH prompt AS (
   SELECT &players AS oplayers
   FROM dual
), x AS (
SELECT LEVEL n, oplayers,  oplayers + MOD(oplayers,2) AS players
FROM dual, prompt
CONNECT BY LEVEL<=oplayers + MOD(oplayers,2)-1
), w AS (
SELECT a.n AS rnd, 1+MOD(a.n+b.n,a.players-1) AS p,
       CASE WHEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)<=a.players/2 THEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY a.n, b.n )-1
            ELSE a.players-ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)
       END AS match_id, a.oplayers  
FROM x a, x b 
UNION ALL
SELECT n, players AS p, 0 AS match_id, oplayers
FROM x
)
SELECT rnd AS "ROUND", 
       CASE WHEN MAX(p)>oplayers THEN  MIN(p) || ' - bye ' 
            WHEN MOD(MAX(p)-MIN(p),2)=1 THEN MIN(p) || ' vs ' || MAX(p)
            ELSE MAX(p) || ' vs ' || MIN(p) 
       END AS pairing
FROM w
GROUP BY rnd, match_id, oplayers
ORDER BY 1, CASE WHEN MAX(p)>oplayers THEN oplayers ELSE MIN(p) END;

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Build Department Size Bar Chart Report with a Single SELECT statement

Build Department Size Bar Chart Report with a Single SELECT statement.

Recently, when I was working on a completely different problem, I realized that in some cases, SQL plus will allow us generating simple bar graphs on selected data. I challenged myself to build a “graph” that will look like this:

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Each column represents a “bar” and its “height” represents the number of employees working in a corresponding department.

Below, you will find several strategies for building such “graphs” as well as generating “reverse graphs”:

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 1: Pivot simulation

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n
/

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 2: Pivot

SELECT "10","20","30"
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N
/

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT "10","20","30"
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY rn;

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
   X  X
   X  X
      X

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
SELECT MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY rn
ORDER BY rn;

Reverse Bar Chart Report:

Method/Workaround 1:

WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT n,
       MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
       MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
       MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n DESC;

  N 10 20 30
--- -- -- --
  6       X
  5    X  X
  4    X  X
  3 X  X  X
  2 X  X  X
  1 X  X  X

Method/Workaround 2:

SELECT *
FROM (
  WITH x AS (
  SELECT deptno, COUNT(*) cnt
  FROM emp
  GROUP BY deptno
  )
  SELECT LEVEL n, deptno, 'X' as c
  FROM dual, x 
  WHERE LEVEL<=x.cnt
  CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
   MAX(c)
   FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N DESC;

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

SELECT *
FROM (
  SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n, 
         deptno, 'X' c
  FROM emp
)
PIVOT
(
  MAX(c)
  FOR deptno IN (10,20,30)
)
ORDER BY n DESC;

Method/Workaround 4:

WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
       CASE WHEN deptno=20 THEN 'X' END "20",
       CASE WHEN deptno=30 THEN 'X' END "30",
       ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n
FROM emp
)
SELECT n, 
       MAX("10") AS "10",
       MAX("20") AS "20",
       MAX("30") AS "30"
FROM x
GROUP BY n
ORDER BY n DESC; 

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the week #4 Solution

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 6, or 16 teams (power of 2) in the 2nd round.

Solution

WITH x AS (
  SELECT &teams p
  FROM dual
), y AS (
SELECT ROWNUM home, POWER(2,CEIL(LOG(2,p)))-ROWNUM+1 away, 
       POWER(2,CEIL(LOG(2,p))) maxp
FROM dual, x
CONNECT BY LEVEL<=POWER(2,CEIL(LOG(2,p))-1)
)
SELECT CASE WHEN away<=p AND p>1 THEN ROWNUM+p-maxp END AS "Game #",
       CASE WHEN away>p AND p>1 THEN 'Team-' || home || ' advances to Round 2'
            WHEN p=1 THEN 'Team-1 is a Champion!'
            ELSE 'Team-' || home || ' vs Team-' || away
       END AS "Playoff Round 1 Pairings"
FROM y, x
ORDER BY 1 NULLS LAST, home

Explanation

The trick here was to figure out which teams should be playing and which simply advance to the Round 2. Suppose that we have a power of 2 number of teams. Then top 1st team plays against the bottom 1st, top 2ng vs bottom 2nd, etc. If there are N teams, then we will have N/2 games. This is the simplest case. What if we have 6 teams? We should add 2 fake teams to “round up” the number of teams to the nearest power of 2 that is greater or equal to N. For 6 teams, we round up to 8. Those 2 fake teams should be paired against top 2 teams, and this gives us an answer which teams should advance to the Round 2 without playing. General rule, if we have to add K “faked teams” to “round up” to the nearest power of 2 number, this means that top K teams advance to the next round without playing.

We used CEIL, LOG, and POWER functions to get the next power of 2 for any whole N:

POWER(2,CEIL(LOG(2,p)))

 

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Puzzle of the week #4

Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 8, or 16 teams (power of 2) in the 2nd round.

Expected Results:

For 6 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-3 vs Team-6
     2 Team-4 vs Team-5
       Team-1 advances to Round 2
       Team-2 advances to Round 2

For 7 teams:

Game # Playoff Round 1 Pairings
------ --------------------------
     1 Team-2 vs Team-7
     2 Team-3 vs Team-6
     3 Team-4 vs Team-5
       Team-1 advances to Round 2

For 8 teams:

Game # Playoff Round 1 Pairings
------ ------------------------
     1 Team-1 vs Team-8
     2 Team-2 vs Team-7
     3 Team-3 vs Team-6
     4 Team-4 vs Team-5

For 1 team (no playoffs needed):

Game # Playoff Round 1 Pairings
------ ---------------------------
       Team-1 is a Champion!

For 11 teams:

Game # Playoff Round 1 Pairings
------ ---------------------------
     1 Team-6 vs Team-11
     2 Team-7 vs Team-10
     3 Team-8 vs Team-9
       Team-1 advances to Round 2
       Team-2 advances to Round 2
       Team-3 advances to Round 2
       Team-4 advances to Round 2
       Team-5 advances to Round 2

You can use a substitution variable and run the query in SQL*Plus to test the results.

To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Create and test a function that returns a cursor.

Interview Question: How to create and test a function that returns a cursor?

Level: Beginner/Intermediate

Step 1: Create a sample function

All you need to know to complete this step is how to use SYS_REFCURSOR type:

SQL> CREATE OR REPLACE FUNCTION get_emp_data RETURN SYS_REFCURSOR
  2  AS
  3     c SYS_REFCURSOR;
  4  BEGIN
  5     OPEN c FOR
  6     SELECT empno,ename,job,deptno
  7     FROM emp;
  8
  9     RETURN c;
 10  END;
 11  /

Function created.

Step 2: Test the new function
I will demonstrate 3 methods/workarounds for testing the function that has just been created.

Method/Workaround #1: Using Anonymous Block
Assumption: We know the structure of the returning cursor, i.e fields and their data types.

set serveroutput on size 2000 format wrapped

DECLARE
  c_emp SYS_REFCURSOR;
  TYPE typ_emp IS RECORD
   (
      empno  emp.empno%TYPE,
      ename  emp.ename%TYPE,
      job    emp.job%TYPE,
      deptno emp.deptno%TYPE
   );
   v typ_emp;
BEGIN
   c_emp:=get_emp_data;
   --Print the header line:
   DBMS_OUTPUT.PUT_LINE(LPAD('empno', 10) || '  ' ||
                        RPAD('ename', 15) ||
                        RPAD('job', 15) ||
                        LPAD('deptno', 6)
                        );
   DBMS_OUTPUT.PUT_LINE(LPAD('------', 10) || '  ' ||
                        RPAD('------', 15) ||
                        RPAD('------', 15) ||
                        LPAD('------', 6)
                        );
   LOOP
       FETCH c_emp INTO v;
       EXIT WHEN c_emp%NOTFOUND;
       --Print the current employee record:
       DBMS_OUTPUT.PUT_LINE(LPAD(v.empno, 10) || '  ' ||
                            RPAD(v.ename, 15) ||
                            RPAD(v.job, 15) ||
                            LPAD(v.deptno, 6)
                           );
   END LOOP;
   CLOSE c_emp;
END;
/
     empno  ename          job            deptno
    ------  ------         ------         ------
      7369  SMITH          CLERK              20
      7499  ALLEN          SALESMAN           30
      7521  WARD           SALESMAN           30
      7566  JONES          MANAGER            20
      7654  MARTIN         SALESMAN           30
      7698  BLAKE          MANAGER            30
      7782  CLARK          MANAGER            10
      7788  SCOTT          ANALYST            20
      7839  KING           PRESIDENT          10
      7844  TURNER         SALESMAN           30
      7876  ADAMS          CLERK              20
      7900  JAMES          CLERK              30
      7902  FORD           ANALYST            20
      7934  MILLER         CLERK              10

PL/SQL procedure successfully completed.

Method/Workaround #2: Using Select Statement
This method does not require us to know the structure of the cursor returned by the function:

SQL> SELECT get_emp_data
  2  FROM dual;

GET_EMP_DATA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

Method/Workaround #3: Using Bind Variable and PRINT command in SQL*PLus
The following method does not require us to know the structure of the cursor returned by the function, but it can only be executed in SQL*Plus:

SQL> var c refcursor
SQL> exec :c:=get_emp_data

PL/SQL procedure successfully completed.

SQL> print c

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7566 JONES      MANAGER           20
      7654 MARTIN     SALESMAN          30
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7788 SCOTT      ANALYST           20
      7839 KING       PRESIDENT         10
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7902 FORD       ANALYST           20
      7934 MILLER     CLERK             10

14 rows selected.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Use EZConnect Syntax for Oracle connection string when you cannot change the TNS Names file.

In some companies, there are security regulations that disallow users to change their local TNS Names file, so if there is a need to connect to a host that is not already defined in the TNS Names file, a different approach has to be employed.

There are a couple of ways to work around the above mentioned limitation:

Note: To get to a SQL*Plus command prompt without connecting use “/nolog” attribute with sqlplus:

C:\>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 20 14:22:25 2016
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
SQL>

1) TNS-less connection string:

SQL> conn scott/tiger@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.180)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
Connected.

2) EZConnect connection string:

SQL> conn scott/tiger@192.168.1.180:1521/ORCL
Connected.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

I was able to use both these approaches with SQL*Plus and SQL Loader.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.