## 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.

## 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.

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.

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.

## 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.

## 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
```

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
```

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
```

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

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;
DBMS_OUTPUT.PUT_LINE(LPAD('empno', 10) || '  ' ||
);
DBMS_OUTPUT.PUT_LINE(LPAD('------', 10) || '  ' ||
);
LOOP
FETCH c_emp INTO v;
EXIT WHEN c_emp%NOTFOUND;
--Print the current employee record:
DBMS_OUTPUT.PUT_LINE(LPAD(v.empno, 10) || '  ' ||
);
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
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
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
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

```

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).

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
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.