ANY Types, Pipelined Table Functions, Dynamic SQL and Pivot
Amazing what you can do with ANYTYPE, ANYDATA, ANYDATASET, DBMS_TYPES, DBMS_SQL and pipelined table functions implemented using the interface approach. You can end up with a dynamic sql mashup like you’ve never seen before, and pivot like you’ve never pivoted before. Of course, if you’re on Oracle DB 10g or below, you can never pivot like this.
1 Comment | Filed in Oracle | Tags: datatype, dynamic, pipelined, pivot, sqlWhen The Combination of an Oracle Sequence and a PLS_INTEGER is Deadly
The following is a true story. We had our Oracle MRP system down for a couple of days because of a simple, yet hidden bug that went unnoticed for years until it hit us couple of days ago (somehow the image of a terrorist cell comes to mind).
In Oracle Applications (11.03) there is a table that stores forecast records (mrp_forecast_dates). In this table there is the column transaction_id that gets populated from a sequence (mrp_forecast_dates_s).
When the current value of the sequence exceeded the number 2,147,483,647 the MRP concurrent programs started to fail with an “ORA-01426: numeric overflow” error. After a lot of research, and an Oracle Service Request, we discovered what the problem was and what was special about the number 2,147,483,647.
Let me reproduce the problem in the following example: Continue reading…
3 Comments | Filed in Oracle, Tips | Tags: datatype, pl/sql, sequenceOne more reason to upgrade your Oracle 8i
On my Oracle database 8i (8.1.7.4) instance, I ran the following:
SQL> SELECT 1 my_number
2 FROM DUAL
3 UNION
4 SELECT NULL my_number
5 FROM DUAL
6 /
SELECT 1 my_number
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
SQL> SELECT SYSDATE my_date
2 FROM DUAL
3 UNION
4 SELECT NULL my_date
5 FROM DUAL
6 /
SELECT SYSDATE my_date
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
What’s up with the ORA-01790 error?
As you know, NULL doesn’t have a data type, and NULL can be used in place of a value of any data type. If you purposely select NULL as a column value in a union query, Oracle no longer has two datatypes to compare to see whether the union queries are compatible. As you can see from the above queries, this is an issue for the DATE and NUMBER datatypes in Oracle 8i . However, Oracle does not have a problem with character datatypes:
SQL> SELECT 'C' my_char
2 FROM DUAL
3 UNION
4 SELECT NULL my_char
5 FROM DUAL
6 /
M
-
C
To solve this issue, you have two options:
Option 1: Explicitly convert NULL to the right datatype:
SQL> SELECT 1 my_number
2 FROM DUAL
3 UNION
4 SELECT TO_NUMBER (NULL) my_number
5 FROM DUAL
6 /
MY_NUMBER
----------
1
SQL> SELECT SYSDATE my_date
2 FROM DUAL
3 UNION
4 SELECT TO_DATE (NULL) my_date
5 FROM DUAL
6 /
MY_DATE
---------
15-JUN-06
Option 2: Upgrade your Oracle database to 9i or above. Oracle 9i, and later releases, are “smart enough” to know which flavor of NULL to use. I executed the following in 9.2.0.6:
SQL> SELECT 1 my_number
2 FROM DUAL
3 UNION
4 SELECT NULL my_number
5 FROM DUAL
6 /
MY_NUMBER
----------
1
SQL> SELECT SYSDATE my_date
2 FROM DUAL
3 UNION
4 SELECT NULL my_date
5 FROM DUAL
6 /
MY_DATE
---------
15-JUN-06
Go for option 2, if you have not done so already.
2 Comments | Filed in Oracle, Tips | Tags: datatype, null
