Friday, April 10, 2015

Extract SQL Source Code For Objects From Datapump Dumpfile

When we dump the whole schema as a dumpfile, the SQL statements for creating those database objects (DDL) can be extracted from the dumpfile.
First, we create the dumpfile using Oracle datapump export utility expdp.

expdp myuser/myuser dumpfile=myuserdump.dmp directory=myuser_dir
We use Oracle datapump import utility impdp and specify "sqlfile=".
impdp myuser/myuser dumpfile=myuserdump.dmp directory=myuser_dir 
sqlfile=myuserdump.sql
When "sqlfile=" is used, data import is not actually done. Instead, it produces a file containing the SQL statements to create database objects. For example, the following "create table" and "create index" are taken from the sqlfile produced by impdp. We can simple run those SQL queries to create objects.
CREATE TABLE "MYUSER"."DMPZKM" 
   ( "CLUSTER_ID" NUMBER, 
 "RECORD_COUNT" NUMBER, 
 "PARENT" NUMBER, 
 "TREE_LEVEL" NUMBER, 
 "DISPERSION" BINARY_DOUBLE, 
 "NORM" BINARY_DOUBLE, 
  CONSTRAINT "DMPSKM" PRIMARY KEY ("CLUSTER_ID") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT);

CREATE UNIQUE INDEX "MYUSER"."DMPQKM" ON "MYUSER"."DMPXKM" 
("CLUSTER_ID", "ATTRIBUTE_NAME", "ATTRIBUTE_SUBNAME", "BIN_ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 164 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "DMUSER" PARALLEL 1 ;

Sunday, April 05, 2015

Table Joining Using Function Based Index

The post Create Function Based Index on Oracle Table shows that indexes can be created based on functions. Here is an example of the actual use of function based index in table joining. In a project, I try to join the following two tables based on a unique sequence numbers:
1. Checks deposited, tbl_checks_deposited.
2. Checks that are returned, tbl_checks_returned.
Both tables contain column sequence_ids that are unique for each record and are supposed to link these two tables. I used the following query to join them.

select a.*, b.return_code
from  tbl_checks_deposited a,  tbl_checks_returned b
where a.sequence_id=b.sequence_id;
However, I did not get many successful matches. After some investigation, I have found that the sequence_id in the second table tbl_checks_returned has extra leading and trailing blanks. This could happen when the data is loaded from text files into an Oracle database. I used trim() function to remove the leading and trailing blanks and redo the join using the following query. To make the join faster, I first create a function based index on table tbl_checks_returned.
create index tbl_checks_returned_idx on tbl_checks_returned(trim(sequence_id));

select a.*, b.return_code
from  tbl_checks_deposited a,  tbl_checks_returned b
where a.sequence_id=trim(b.sequence_id);
Now the join returns the correct number of records.

Remove Character at Specific Location in String

In the post Remove the Trailing Character From a String Using SQL, we use substr SQL function. To remove a character at a specific location in a string, I create a function that takes advantage of substr again as shown below.

create or replace function remove_at(
   str IN varchar2,
   pos IN number)
return varchar2
is
begin
return substr(str,1,pos-1)||substr(str, pos+1, length(str));
end;
/
Now, I apply this function using different locations and the results look correct.
SQL> select id, val from tbl_val;

     ID VAL
------- --------------------------------
      1 abcdef
SQL> select id, remove_at(val,3) val from tbl_val;

     ID VAL
------- --------------------------------
      1 abdef

SQL> select id, remove_at(val,5) val from tbl_val;

     ID VAL
------- --------------------------------
      1 abcdf

SQL> select id, remove_at(val,1) val from tbl_val;

     ID VAL
------- --------------------------------
      1 bcdef

SQL> select id, remove_at(val,0) val from tbl_val;

     ID VAL
------- --------------------------------
      1 abcdef

SQL> select id, remove_at(val,10) val from tbl_val;

     ID VAL
------- --------------------------------
      1 abcdef

Saturday, April 04, 2015

Remove the Trailing Character From a String Using SQL

We can using SQL function substr to remove the last character from a string. For example, the following query shows there is a extra comma at the end of the string. Please also see Remove Character at Specific Location in String.

SQL> select * from tbl_test;

     ID VAL
------- --------------------------------
      1 Hello,
The following query will remove it. Substr takes three parameters. The first parameter is the string to process, the second is the starting position and the third is the length of the substring to extract. Here, we define the length to be the total length of the original string minus one.
SQL>  select id, substr(val, 1, length(val)-1) val from tbl_test;

     ID VAL
------- --------------------------------
      1 Hello

Convert Oracle Number to Hex and Vice Verse

To convert a hexadecimal number (base 16) to a decimal number, we use to_number function and specify the format as 'XXXXX' as shown below.

SQL> select to_number('30003','XXXXXX') from dual;

TO_NUMBER('30003','XXXXXX')
---------------------------
       196611
We can verify the conversion by using the following query.The fifth position from the right represents 16 to the 4th power.
SQL> select power(16,4)*3+3 from dual;

POWER(16,4)*3+3
---------------
         196611
To convert a decimal number to a hex number, we use to_char() function.
SQL> select to_char(196611, 'XXXXXX') from dual;

TO_CHAR
-------
  30003

Friday, April 03, 2015

Chi-square Test in Oracle Database

When we want to test the association of two categorical variables, such as sex and certain disease, we may use Chi-square test. Fortunately, with Oracle statistical function stats_crosstab, this can be done using a SQL query. For example, we have a table containing two columns of discrete values, sex and target.

SQL> select sex, target, count(*) num from tbl_chiqs group by sex, target
 order by sex, target;

S     TARGET        NUM
- ---------- ----------
F          0        152
F          1         13
M          0        411
M          1         26
We want to find out if sex and target are associated. Since both are categorical variables, we perform Chi-square test using a SQL query.
select
stats_crosstab(sex, target, 'CHISQ_OBS') chi_squared,
stats_crosstab(sex, target, 'CHISQ_SIG') p_value
from
tbl;

CHI_SQUARED    P_VALUE
----------- ----------
 .735719671 .391035481   
Based on the p value of the Chi-square statistics, we can not reject the NULL hypothesis that sex and target are independent given the significant level of 0.05. As we see, it is very convenient to perform statistical tests such as Chi-square, within a database using SQL query.It is not necessary to move the data to another statistical software to perform those tests.

Thursday, April 02, 2015

Oracle Impdp Remap Tablespace

When I import a Oracle dump file produced by datapump export utility expdp from another database instance, I get the following error. This is caused the the default tablespace for the user is different.


shell> impdp myuser/mypassword directory=DM_DUMP dumpfile=tmpm_r_pmml.dmp

ORA-39083: Object type TABLE:"MYUSER"."DATA_TRAIN" failed to create with error:
ORA-00959: tablespace 'MYUSER' does not exist
To find our the default tablepsace name for the current user, I log onto the user.
Shell> sqlplus myuser/mypassword

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
TBS

I add "remap_tablespace=DMUSER:TBS" to impdp command and the dump file is imported correctly.
impdp dmuser/dmuser directory=DM_DUMP dumpfile=tmpm_r_pmml.dmp 
   remap_tablespace=MYUSER:TBS