Preserve Leading Zeros in ApEx CSV Export

I located a method of preserving leading zeros on values when exporting a report region to CSV in HTML DB (ApEx).

When Excel opens up a CSV file it defaults all cells to "General" formatting and attempts to "help the user" by removing leading zeros on cell values and converting them from strings to numbers. That can be a problem for values like product codes (SKUs) that begin with a zero.

The trick is to SELECT the value such that Excel will see it as a string and not a number. So prefix an equals sign and double quote on to the value and post fix a double quote. So

SELECT your_column
FROM your_table

becomes

SELECT '="'||your_column||'"'
FROM your_table

That is great except the users now see the additional characters in the report region. To get a round this use the following SELECT statement:

SELECT your_column, '="'||your_column||'"' quoted_column
FROM your_table

and put a "PL/SQL Express" condition (based on Sergio's blog entry) on each column. The conditional display express for YOUR_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') = 0

The conditional display express for QUOTED_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') != 0

To get even more customized CSV output look at Scott's blog entry.

SQL Developer's "SQL History"

I love the SQL History button (F8) in SQL Developer. Let me tell you what happened to me yesterday. I using SQL Developer to work on a difficult SELECT statement when the electricity at work went out on me. Poof my SELECT statement was gone, right? Not in SQL Developer. When everything came up I started SQL Developer, opened a SQL Worksheet, and clicked on the SQL History button . There was my SELECT statement.

Thanks Oracle for the button.

Table Rows to One String in SQL

I came across of very cool feature yesterday that I had not seen before. It was the ability to take values of a column in multiple rows and concatenate them into one string. This is useful for building a TO distribution list for an email. Here is an example of concatenating all the names of the employees in the EMP table:

SQL> SELECT *
2    FROM emp;

EMPNO ENAME      JOB             MGR  HIREDATE  SAL        COMM       DEPTNO
----- ---------- --------------- ---- --------- ---------- ---------- ----------
7839 KING        PRESIDENT            17-NOV-81       5000                    10
7698 BLAKE       MANAGER         7839 01-MAY-81       2850                    30
7782 CLARK       MANAGER         7839 09-JUN-81       2450                    10
7566 JONES       MANAGER         7839 02-APR-81       2975                    20
7788 SCOTT       ANALYST         7566 09-DEC-82       3000                    20
7902 FORD        ANALYST         7566 03-DEC-81       3000                    20
7369 SMITH       CLERK           7902 17-DEC-80        800                    20
7499 ALLEN       SALESMAN        7698 20-FEB-81       1600        300         30
7521 WARD        SALESMAN        7698 22-FEB-81       1250        500         30
7654 MARTIN      SALESMAN        7698 28-SEP-81       1250       1400         30
7844 TURNER      SALESMAN        7698 08-SEP-81       1500          0         30
7876 ADAMS       CLERK           7788 12-JAN-83       1100                    20
7900 JAMES       CLERK           7698 03-DEC-81        950                    30
7934 MILLER      CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
2    FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
3            FROM (SELECT ename myvalues
4                    FROM emp
5                 )
6         ) data
7   WHERE rn = cnt
8   START WITH rn = 1
9   CONNECT BY PRIOR rn = rn-1;

CATVALUES
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

Here is the SELECT statement:

SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (SELECT ename myvalues
      FROM emp
   )
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;

To figure this out let us look at the inner SELECT:

SQL>
SQL> SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
2    FROM (SELECT ename myvalues
3            FROM emp
4         );

MYVALUES           RN        CNT
---------- ---------- ----------
ADAMS               1         14
ALLEN               2         14
BLAKE               3         14
CLARK               4         14
FORD                5         14
JAMES               6         14
JONES               7         14
KING                8         14
MARTIN              9         14
MILLER             10         14
SCOTT              11         14
SMITH              12         14
TURNER             13         14
WARD               14         14

14 rows selected.

Oracle starts at the top where

rn = 1

which is ADAMS. It connects each node of the tree to the prior element where the prior's RN value is one less

rn = rn -1

until WARD. Now the SYS_CONNECT_BY_PATH function in a CONNECT BY query takes the tree and concatenates the values together putting a comma ',' between the nodes in the tree.

Updated GUI Intensive ApEx Application post

I will keep adding issues to my GUI intensive ApEx Application blog as I encounter them. This will keep them all together in one place.