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