Friday, May 05, 2017

Current Schema Name

To find out the current schema that we are connect to, we may use function sys_context(). I connected to Oracle SQL Live at https://livesql.oracle.com and ran the following query:

select sys_context('USERENV', 'SESSION_USER') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
APEX_PUBLIC_USER
The returned result showed that the schema name was APEX_PUBLIC_USER.

Sunday, February 26, 2017

Online Training Course- Oracle SQL for Data Science

Upon the requests of many people who listened to my presentations, I have built the online course Oracle SQL for Data Science. This course requires that students already have basic knowledge about SQL. The following is the description of the course.

Most of the core business data in an enterprise are stored in relational databases that support Structure Query Language (SQL). When data scientists perform common tasks such as data cleanse, validation, manipulation and feature variable calculation using SQL within database environment, they can achieve important advantages such as more compact code, easy deployment and security in comparison to moving the data outside of the database to a separate analytics environment. The instructor Dr. Jiang Zhou is one of the pioneers in developing SQL based in-database analytics solutions that are used by banks and insurance companies. In addition to his technical skills, he is, as one of his clients put it, "a great trainer, and a good presenter of theoretical data mining concepts so that they can be understood by most".
In this course, students will learn practical Oracle SQL skills to solve problems such as:

  • Data Validation
  • Data Summary
  • Detect and Remove Duplicates
  • Binning Variable Based on Equal Frequency
  • Build Good Variables for Predictive Models or Business Rules, e.g., RFM Analysis, Time Elapse Since Last Purchase, Number of Transactions in Last 3 Days, Moving Average Purchase Amount in Last 7 Days
  • Random Sampling
  • Gain Chart
  • Using View to Organize Process Flows
  • Histogram
There are totally about 4 hours and 30 minutes video presentations. SQL scripts that create data sets and perform the Data Science tasks are provided. Slides are included so that students can easily find the topics that they interested in.

Monday, February 13, 2017

Display Date in SQLPLUS Client

When I select column of date type within SQLPLUS, I got the following result.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
---------
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
02-OCT-11
03-OCT-11
23-OCT-11
23-OCT-11
23-OCT-11

9 rows selected.
Since I want to see the hour, minute and second of a transaction, I use to_char() to format the date.
SQL> select to_char(txn_date,'YYYYMMDD:hh24:mi:ss') from card_txn 
where rownum <10;

TO_CHAR(TXN_DATE,
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.
If we want to display dates in desired format without using to_char() conversion, we can use the following command. After running this command, all dates will be automatically formatted.
SQL> alter session set nls_date_format='YYYYMMDD:hh24:mi:ss';

Session altered.

SQL> select txn_date from card_txn where rownum <10;

TXN_DATE
-----------------
20111002:22:53:54
20111002:22:21:30
20111002:11:38:17
20111002:23:45:52
20111002:02:19:17
20111003:11:38:17
20111023:23:30:24
20111023:23:30:52
20111023:14:57:46

9 rows selected.

Friday, February 10, 2017

Troubleshooting: Could not Connect to Amazon RDS Oracle Instance

I could not connect to the Oracle Instance on Amazon RDS platform when I worked in the town library. I followed the following steps and resolved the issue:

1. Find my IP address using Google Search "my ip address". Google will display my ip address 64.64.117.xx.

2. Go to Amazon AWS console/Services/RDS/Dashboard/Instance. Right click the instance and select "See details".

3. From the detail view, click the link to next to "Security Groups".
4. At the bottom section, click Inbound and then Edit

5. Click Add Rule and add the IP address 64.64.117.xx/32.

Wednesday, February 08, 2017

Oracle SQLPLUS Client Installation on Windows 64

I want to run SQLPLUS interactive command environment to query Oracle databases. When I type my SQL commands and hit return key, I get the response from the database right away. It is like I am having a conversation with the data. To me, this is a more preferable way than using a GUI-based tool. I wrote a post 3 years ago, Oracle SQLPLUS Client Installation on Windows Troubleshooting. But that one was about installing 32 bit version installation. Since I am installing 64 bit version on my Windows Machine, I write down the steps.

Step 1. Go to the Oracle site:
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Step 2. Accept OTN License Agreement and download the following two files:

Version 12.1.0.2.0
   Instant Client Package - Basic: All files required to run OCI, OCCI, 
and JDBC-OCI applications
Download instantclient-basic-windows.x64-12.1.0.2.0.zip (72,416,242 bytes) 
(cksum - 1751087003)
  Instant Client Package - SQL*Plus: Additional libraries and executable for 
running SQL*Plus with Instant Client Download 
instantclient-sqlplus-windows.x64-12.1.0.2.0.zip (876,515 bytes) 
(cksum - 1826332995) 
Step 3. I unzip both zip files and put files under the same directory. I run the following command and am able to connect the my Oracle DB on Amazon cloud.
sqlplus myusername/mypassword@mysid.ctbxjhstgq8o.us-east-1.rds.amazonaws.com:1521/mysid

Thursday, January 26, 2017

Dr. Jiang Zhou will Give a Talk at The University of Massachusetts Boston

I am going to give a talk at Department of Computer Science, the University of Massachusetts Boston, on January 31. The following is the abstract:

Abstract: Will a new mobile phone customer pay her monthly bill? Is a check deposited into a bank customer’s account fraudulent? Will an insurance claim become expensive? For nearly two decades, Dr. Jiang (Jay) Zhou has been helping mobile phone service providers, banks, insurance companies and other businesses solve problems like these. The predictive models that he built have resulted in over $200 million savings for clients. In the seminar, Dr. Zhou will discuss topics including challenges and best practices when building these predictive models in real world environments.