## How to generate a list of first N binary numbers in Oracle SQL?

In my recent post I showed how to convert a decimal number (i.e. an integer) into a binary string. We can build upon that technique to answer the question:

```WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.bin
FROM x, LATERAL (SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=x.N) y
```

Note the LATERAL keyword (Oracle 12c new feature) that enables us to reference “x” in the inline view “y”. In pre-12c world, we would have to use TABLE/CAST/MULTISET function composition to achieve the same result:

```WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.column_value bin
FROM x, TABLE(CAST(MULTISET(
SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=x.N) AS sys.odcivarchar2list)) y
```

The idea used in the following query is based on a totally different approach. It builds a string of “0”s and “1”s in a loop until its length reaches a desired value:

```WITH x(v, n) AS (
SELECT column_value, 1
FROM TABLE(sys.odcivarchar2list('0','1'))
UNION ALL
SELECT x.v || t.column_value, x.n+1
FROM TABLE(sys.odcivarchar2list('0','1')) t JOIN x on LENGTH(x.v)=n
WHERE n<=CEIL(LOG(2,50))
), y AS (
SELECT NVL(LTRIM(x.v,'0'),'0') bin, ROWNUM-1 dec
FROM x
WHERE n=(SELECT MAX(n) FROM x)
)
SELECT *
FROM y
WHERE dec<=50
```

To better understand the above query, try the following one:

```SELECT *
FROM TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1'))
```

If we put enough tables in the Cartesian product and concatenate all column_value columns in a single character string expression, we will achieve our goal. The challenge with this approach is to dynamically change the number of the tables in the FROM clause. This can be simulated in the recursive WITH clause by repeatedly adding more and more collections of bits (0 and 1).

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

## Using SQL%ROWCOUNT with Dynamic PL/SQL

Using SQL%ROWCOUNT attribute when executing static or dynamic DML statement is very handy. Situation changes dramatically when you check this attribute after executing dynamic or static PL/SQL command:

Static PL/SQL

```BEGIN
NULL;
dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/
```

Result:

```Rowcount=
```

Dynamic PL/SQL

```BEGIN
EXECUTE IMMEDIATE 'BEGIN NULL; END;';
dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
END;
/
```

Result:

```Rowcount=1
```

Static PL/SQL “has” NULL as SQL%ROWCOUNT value while Dynamic PL/SQL – always “produces” 1, even if that dynamic PL/SQL does affect certain number of records in a table:

Dynamic SQL:

```BEGIN
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE ROWNUM<=2';
dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
ROLLBACK;
END;
/
```

Result:

```Rowcount=2
```

Same command in Dynamic PL/SQL:

```BEGIN
EXECUTE IMMEDIATE 'BEGIN DELETE FROM emp WHERE ROWNUM<=2; END;';
dbms_output.put_line('Rowcount=' || SQL%ROWCOUNT);
ROLLBACK;
END;
/
```

Result:

```Rowcount=1
```

Sometimes, we have to use dynamic PL/SQL so getting correct number of affected rows may be critical. Here is a simple but effective solution:

```DECLARE
v_cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE 'BEGIN
DELETE FROM emp WHERE ROWNUM<=2;
:0:=SQL%ROWCOUNT;
END;'  USING OUT v_cnt;
dbms_output.put_line('Rowcount=' || v_cnt);
ROLLBACK;
END;
/
```

Result:

```Rowcount=2
```

We use bind variable in the OUT mode to get the result of STATIC SQL inside of

DYNAMIC PL/SQL.

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