1. Download Oracle XE
2. Install Oracle XE on windows XP. After installation, Oracle Application Expression 2.0.1 is installed as default.
3. Download Oracle Application Express version 3.2.
4. Install Oracle Application Express 3.2.
Change to directory of the Oracle Application Expression 3.2 software.
sqlplus sys as sysdba
@apexins.sql sysaux sysaux temp /i/
@apechpwd.sql
@apxldimg.sql /apex_3.2
5. Well done.
Monday, January 11, 2010
Monday, March 23, 2009
Oracle BINARY_FLOAT and BINARY_DOUBLE datatypes
Oracle introductes the BINARY_FLOAT and BINARY_DOUBLE datatypes since Oracle database 10g. These two datatypes are different from the NUMBER.
NUMBER stores data using decimal precision. BINARY floating-point numbers are stored
using binary precision (the digits 0 and 1).
BINARY_FLOAT is a 32-bit, single-precision floating-point number
datatype. BINARY_DOUBLE is a 64-bit, double-precision floating-point number
datatype. You can not specify a precision when you define a column with the BINARY_FLOAT or BINARY_DOUBLE datatype.
For example,
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float(20));
f3 binary_float(20))
*
ERROR at line 4:ORA-00907: missing right parenthesis
After removing the precision from the create table statement, it can create table successfully.
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float);
table created.
There are some special values for the BINARY_FLOAT/BINARY_DOUBLE datatypes.
BINARY_FLOAT_NAN A value of type BINARY_FLOAT for Not a Number
BINARY_DOUBLE_NAN A value of type BINARY_DOUBLE for Not a Number
BINARY_FLOAT_INFINITY A value of type BINARY_FLOAT for positive infinity(+∞)
BINARY_DOUBLE_INFINITY A value of type BINARY_DOUBLE for positive infinity(+∞)
Let's insert some rows using these special literal values.
insert into t1 values (1, 'string one', 1234567.89103245678);
insert into t1 values (2, '234.5678', BINARY_FLOAT_INFINITY);
insert into t1 values (3, 'string two', null);
insert into t1 values (4, '654.213', BINARY_FLOAT_NAN);
insert into t1 values (5, 'string three', -BINARY_FLOAT_INFINITY);
commit;
SQL> select * from t1;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
3 string two
4 654.213 Nan
5 string three -Inf
Now, we have 5 rows.
Note: We use the -BINARY_FLOAT_INFINITY for the negative infinity(-∞).
You can test not a number using IS [NOT] NAN in the WHERE clause. But this is only working with the BINARY_FLOAT ot BINARY_DOUBLE datatype.
select * from t1 where f2 is nan;
select * from t1 where f2 is nan
*
ERROR at line 1:ORA-01722: invalid number
This is because f2 is not a BINARY_FLOAT or BINARY_DOUBLE datatype. It is a VARCHAR2 datatype.
select * from t1 where f3 is nan;
F1 F2 F3
---------- -------------------- ----------
4 654.213 Nan
select * from t1 where f3 is not nan;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
5 string three -Inf
Note: NULL is neither a NAN, nor a not NAN. BINARY_FLOAT_INFINITY is special number, so not NAN is return TRUE for the BINARY_FLOAT_INFINITY value.
You can use the IS [NOT] INFINITY to test infinity condition.
SQL> select * from t1 where f3 is infinite;
F1 F2 F3
---------- -------------------- ----------
2 234.5678 Inf
5 string three -Inf
SQL> select * from t1 where f3 is not infinite;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
4 654.213 Nan
Note: NULL is neither a INFINITE number, nor a not INFINITE number.
SQL> select * from t1 where f3 is null;
F1 F2 F3
---------- -------------------- ----------
3 string two
SQL> select * from t1 where f3 is not null;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
4 654.213 Nan
5 string three -Inf
BINARY_FLOAT_INFINITY and BINARY_FLOAT_NAN are special values. They are not NULL.
Binary floating-point numbers are stored using binary precision, so it can not represent all values
using decimal precision exactly.
SQL> select * from t1 where f3=1234567.89103245678;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.89103245;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.8;
no rows selected
SQL> select * from t1 where f3=1234567.9;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567;
no rows selected
SQL> select * from t1 where f3=1234568;
no rows selected
Now, we add another column for BINARY_DOUBLE datatype, and update the value of BINARY_DOUBLE column with the same value of the BINARY_FLOAT datatype.
update t1 set f4=1234567.89103245678 where f1=1;
update t1 set f4=binary_double_infinity where f1=2;
update t1 set f4=null where f1=3;
update t1 set f4=binary_double_nan where f1=4;
update t1 set f4=-binary_double_infinity where f1=5;
SQL> commit;
Commit complete.
SQL> select * from t1;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
2 234.5678 Inf Inf
3 string two
4 654.213 Nan Nan
5 string three -Inf -Inf
Note: The BINARY_FLOAT_INFINITY is identical with the BINARY_DOUBLE_INFINITY. Also, the BINARY_FLOAT_NAN is identical with the BINARY_DOUBLE_NAN.
The interesting thing is that the value of BINARY_FLOAT and BINARY_DOUBLE for row 1 is not equal for row 1, even you insert it using the same literal value.
SQL> select * from t1 where f3=f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
2 234.5678 Inf Inf
4 654.213 Nan Nan
5 string three -Inf -Inf
SQL> select * from t1 where f3<>f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
This is because Oracle stores different values for different datatype.
SQL> select dump(f3) dumpf3, dump(f4) dumpf4 from t1 where f1=1;
DUMPF3 DUMPF4
------------------------------ --------------------------------------------------
Typ=100 Len=4: 201,150,180,63 Typ=101 Len=8: 193,50,214,135,228,26,179,254
Also, you can insert BINARY_FLOAT_INFINITY value or BINARY_FLOAT_NAN value in a BINARY_DOUBLE datatype column, and insert BINARY_DOUBLE_INFINITY and BINARY_DOUBLE_NAN in a BINARY_FLOAT datatype column.
NUMBER stores data using decimal precision. BINARY floating-point numbers are stored
using binary precision (the digits 0 and 1).
BINARY_FLOAT is a 32-bit, single-precision floating-point number
datatype. BINARY_DOUBLE is a 64-bit, double-precision floating-point number
datatype. You can not specify a precision when you define a column with the BINARY_FLOAT or BINARY_DOUBLE datatype.
For example,
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float(20));
f3 binary_float(20))
*
ERROR at line 4:ORA-00907: missing right parenthesis
After removing the precision from the create table statement, it can create table successfully.
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float);
table created.
There are some special values for the BINARY_FLOAT/BINARY_DOUBLE datatypes.
BINARY_FLOAT_NAN A value of type BINARY_FLOAT for Not a Number
BINARY_DOUBLE_NAN A value of type BINARY_DOUBLE for Not a Number
BINARY_FLOAT_INFINITY A value of type BINARY_FLOAT for positive infinity(+∞)
BINARY_DOUBLE_INFINITY A value of type BINARY_DOUBLE for positive infinity(+∞)
Let's insert some rows using these special literal values.
insert into t1 values (1, 'string one', 1234567.89103245678);
insert into t1 values (2, '234.5678', BINARY_FLOAT_INFINITY);
insert into t1 values (3, 'string two', null);
insert into t1 values (4, '654.213', BINARY_FLOAT_NAN);
insert into t1 values (5, 'string three', -BINARY_FLOAT_INFINITY);
commit;
SQL> select * from t1;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
3 string two
4 654.213 Nan
5 string three -Inf
Now, we have 5 rows.
Note: We use the -BINARY_FLOAT_INFINITY for the negative infinity(-∞).
You can test not a number using IS [NOT] NAN in the WHERE clause. But this is only working with the BINARY_FLOAT ot BINARY_DOUBLE datatype.
select * from t1 where f2 is nan;
select * from t1 where f2 is nan
*
ERROR at line 1:ORA-01722: invalid number
This is because f2 is not a BINARY_FLOAT or BINARY_DOUBLE datatype. It is a VARCHAR2 datatype.
select * from t1 where f3 is nan;
F1 F2 F3
---------- -------------------- ----------
4 654.213 Nan
select * from t1 where f3 is not nan;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
5 string three -Inf
Note: NULL is neither a NAN, nor a not NAN. BINARY_FLOAT_INFINITY is special number, so not NAN is return TRUE for the BINARY_FLOAT_INFINITY value.
You can use the IS [NOT] INFINITY to test infinity condition.
SQL> select * from t1 where f3 is infinite;
F1 F2 F3
---------- -------------------- ----------
2 234.5678 Inf
5 string three -Inf
SQL> select * from t1 where f3 is not infinite;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
4 654.213 Nan
Note: NULL is neither a INFINITE number, nor a not INFINITE number.
SQL> select * from t1 where f3 is null;
F1 F2 F3
---------- -------------------- ----------
3 string two
SQL> select * from t1 where f3 is not null;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
4 654.213 Nan
5 string three -Inf
BINARY_FLOAT_INFINITY and BINARY_FLOAT_NAN are special values. They are not NULL.
Binary floating-point numbers are stored using binary precision, so it can not represent all values
using decimal precision exactly.
SQL> select * from t1 where f3=1234567.89103245678;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.89103245;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.8;
no rows selected
SQL> select * from t1 where f3=1234567.9;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567;
no rows selected
SQL> select * from t1 where f3=1234568;
no rows selected
Now, we add another column for BINARY_DOUBLE datatype, and update the value of BINARY_DOUBLE column with the same value of the BINARY_FLOAT datatype.
update t1 set f4=1234567.89103245678 where f1=1;
update t1 set f4=binary_double_infinity where f1=2;
update t1 set f4=null where f1=3;
update t1 set f4=binary_double_nan where f1=4;
update t1 set f4=-binary_double_infinity where f1=5;
SQL> commit;
Commit complete.
SQL> select * from t1;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
2 234.5678 Inf Inf
3 string two
4 654.213 Nan Nan
5 string three -Inf -Inf
Note: The BINARY_FLOAT_INFINITY is identical with the BINARY_DOUBLE_INFINITY. Also, the BINARY_FLOAT_NAN is identical with the BINARY_DOUBLE_NAN.
The interesting thing is that the value of BINARY_FLOAT and BINARY_DOUBLE for row 1 is not equal for row 1, even you insert it using the same literal value.
SQL> select * from t1 where f3=f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
2 234.5678 Inf Inf
4 654.213 Nan Nan
5 string three -Inf -Inf
SQL> select * from t1 where f3<>f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
This is because Oracle stores different values for different datatype.
SQL> select dump(f3) dumpf3, dump(f4) dumpf4 from t1 where f1=1;
DUMPF3 DUMPF4
------------------------------ --------------------------------------------------
Typ=100 Len=4: 201,150,180,63 Typ=101 Len=8: 193,50,214,135,228,26,179,254
Also, you can insert BINARY_FLOAT_INFINITY value or BINARY_FLOAT_NAN value in a BINARY_DOUBLE datatype column, and insert BINARY_DOUBLE_INFINITY and BINARY_DOUBLE_NAN in a BINARY_FLOAT datatype column.
insert into t1 values (6, null, BINARY_FLOAT_INFINITY, BINARY_FLOAT_INFINITY);
insert into t1 values (7, null, BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_INFINITY);
commit;
SQL> select f1, f3, f4, dump(f3) dumpf3, dump(f4) dumpf4 from t1;
F1 F3 F4 DUMPF3 DUMPF4
---------- ---------- ---------- ------------------------------ ------------------------------------
1 1.235E+006 1.235E+006 Typ=100 Len=4: 201,150,180,63 Typ=101 Len=8: 193,50,214,135,228,26,179,254
2 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
3 NULL NULL
4 Nan Nan Typ=100 Len=4: 255,192,0,0 Typ=101 Len=8: 255,248,0,0,0,0,0,0
5 -Inf -Inf Typ=100 Len=4: 0,127,255,255 Typ=101 Len=8: 0,15,255,255,255,255,255,255
6 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
7 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
Friday, March 13, 2009
Full export error caused by DBMS_LOGMNR_LOGREP_DICT invalid
Full export successfully finished with warning. I checked the export log file and found the following error:
EXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_expEXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_exp
Using sql*plus found that the sys schema package body DBMS_LOGMNR_LOGREP_DICT is invalid.
Trying to validate this package using the following statement not success:
Alter package DBMS_LOGMNR_LOGREP_DICT compile;
Searched the $ORACLE_HOME/rdbms/admin and found that this package is created with the Oracle supplied script prvtlmrd.plb. Trying to validate this package using this script is still not working.
@$ORACLE_HOME/rdbms/admin/prvtlmrd.plb
Finally, try to rebuild the whole database stored procedures using catproc.sql did not validate this package body(but has a side effect, explained later). So I asked the Oracle support for help. The reply went back quickly with an action plan based on Doc ID 550197.1.
For the packages of the LogMiner to be created successfully, you need to drop the SYSTEM.LOGMNR_SESSION$ table first, and then you can run the Oracle supplied scripts to recreate the LogMiner packages successfully. But the action plan missed the prvtlmrd.plb.
So, it should be:
SQL> connect / as sysdba
SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;
SQL> @?/rdbms/admin/dbmslm.sql
SQL> @?/rdbms/admin/prvtlm.plb
SQL> @?/rdbms/admin/dbmslmd.sql
SQL> @?/rdbms/admin/prvtlmd.plb
SQL> @?/rdbms/admin/prvtlmc.plb
SQL> @?/rdbms/admin/prvtlmrd.plb
This makes the invalid sys schema package body DBMS_LOGMNR_LOGREP_DICT valided.
But the export still got error. This time, the log is like this:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Searched on Metalink and found the Doc ID 339938.1. I made some SYS.KU$_% views crashed. Fixed this problem with the following steps:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql
Now, I could successfully export my full datase. Cheers!
EXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_expEXP-00008: ORACLE error 4063 encounteredORA-04063: package body "SYS.DBMS_LOGMNR_LOGREP_DICT" has errorsORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGMNR_LOGREP_DICT"ORA-06512: at line 1EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGMNR_LOGREP_DICT.system_info_exp
Using sql*plus found that the sys schema package body DBMS_LOGMNR_LOGREP_DICT is invalid.
Trying to validate this package using the following statement not success:
Alter package DBMS_LOGMNR_LOGREP_DICT compile;
Searched the $ORACLE_HOME/rdbms/admin and found that this package is created with the Oracle supplied script prvtlmrd.plb. Trying to validate this package using this script is still not working.
@$ORACLE_HOME/rdbms/admin/prvtlmrd.plb
Finally, try to rebuild the whole database stored procedures using catproc.sql did not validate this package body(but has a side effect, explained later). So I asked the Oracle support for help. The reply went back quickly with an action plan based on Doc ID 550197.1.
For the packages of the LogMiner to be created successfully, you need to drop the SYSTEM.LOGMNR_SESSION$ table first, and then you can run the Oracle supplied scripts to recreate the LogMiner packages successfully. But the action plan missed the prvtlmrd.plb.
So, it should be:
SQL> connect / as sysdba
SQL> drop table SYSTEM.LOGMNR_SESSION$ purge;
SQL> @?/rdbms/admin/dbmslm.sql
SQL> @?/rdbms/admin/prvtlm.plb
SQL> @?/rdbms/admin/dbmslmd.sql
SQL> @?/rdbms/admin/prvtlmd.plb
SQL> @?/rdbms/admin/prvtlmc.plb
SQL> @?/rdbms/admin/prvtlmrd.plb
This makes the invalid sys schema package body DBMS_LOGMNR_LOGREP_DICT valided.
But the export still got error. This time, the log is like this:
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
Searched on Metalink and found the Doc ID 339938.1. I made some SYS.KU$_% views crashed. Fixed this problem with the following steps:
SQL> connect / as sysdba
SQL> @?/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/utlrp.sql
Now, I could successfully export my full datase. Cheers!
Subscribe to:
Posts (Atom)