Bar Solutions WebLog

Yesterday I came across a rather strange problem, which was luckily relatively easily solved. The problem is like this: I am using QDA (Qnxo Development Architecture) packages to get to the data in my tables. In the Query Package there is a function to retrieve a row from the table. I had generated this code and it works correct for my situation and this will work on my development database. But, the order of the columns in the production database happened to be different. As different as just one column in another position.

Consider the emp table on my development database:

SQL> desc emp
Name Type
——– ————
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

The query code generated for this table is like this:

FUNCTION onerow (empno_in IN EMP_TP.EMPNO_t)
RETURN EMP_TP.EMP_rt
IS
  onerow_rec EMP_TP.EMP_rt;
BEGIN
  SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
    INTO onerow_rec
    FROM EMP
  WHERE EMPNO = empno_in;
  RETURN onerow_rec;
EXCEPTION
…
END onerow;

Now, on the production database, the table layout is a little bit different, for example, the hiredate was added to the table later, so the emp table has the following layout:

SQL> desc emp
Name Type
——– ————
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
HIREDATE DATE

If we select the fields in the first order and then try to get them into a record, based on the %rowtype of the table, then this fails.

The easy solution is, instead of using the column names in the select statement, use select * in the select statement. This results in a resultset where the columns have the same order as the order in the %rowtype based record.

FUNCTION onerow (empno_in IN EMP_TP.EMPNO_t)
RETURN EMP_TP.EMP_rt
IS
  onerow_rec EMP_TP.EMP_rt;
BEGIN
  SELECT *
    INTO onerow_rec
    FROM EMP
  WHERE EMPNO = empno_in;
  RETURN onerow_rec;
EXCEPTION
…
END onerow;

Luckily this is a parameter in Qnxo which can easily be set to a different default value.