Friday, 17 February 2012

Using SQLFire sqlf CLI to connect to Oracle from a MAC OS-X Lion

Being on a MAC OS-X lion there is no client install for oracle to give me SQLPlus for example. BUT I can use sqlf command line client to connect to oracle to give me the ability to execute SQL much like SQLPLus. Handy indeed even if it isn't exactly SQLPlus it's good ienough for what I need when accessing my Oracle database from my MAC OS-X lion given thier is no oracle client for 11g on that platform.

It's done as shown below.

1. Add oracle JDBC driver to your classpath

export CLASSPATH=/Users/papicella/vmware/jdbcdrivers/11.2/ojdbc6.jar

2. Setup PATH to include SQLFire in your path as shown below.

export PATH=/Users/papicella/vmware/software/sqlfire/vFabric_SQLFire_101/bin:$PATH

3. Run some Oracle SQL as shown below using sql which shows how to load the driver, connect and run some SQL.

[Fri Feb 17 09:04:11 papicella@:~ ] $ sqlf
sqlf version 10.4
sqlf> driver 'oracle.jdbc.OracleDriver';
sqlf> connect 'jdbc:oracle:thin:scott/tiger@172.16.101.70:1521/linux11gr2';
sqlf> select to_char(sysdate, 'dd-MON-yyyy HH24:MI:SS') "Todays Date" from dual;
Todays Date                  
-----------------------------
17-FEB-2012 09:03:34         

1 row selected
sqlf> select * from dept;
DEPTNO                |DNAME         |LOC          
---------------------------------------------------
10                    |ACCOUNTING    |NEW YORK     
20                    |RESEARCH      |DALLAS       
30                    |SALES         |CHICAGO      
40                    |OPERATIONS    |BOSTON       

4 rows selected
sqlf> select * from v$version;
BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production    
PL/SQL Release 11.2.0.2.0 - Production                                          
CORE 11.2.0.2.0 Production                                                      
TNS for Linux: Version 11.2.0.2.0 - Production                                  
NLSRTL Version 11.2.0.2.0 - Production                                          

5 rows selected
sqlf>

2 comments:

Chris Jones said...

What about using Oracle's SQL Developer tool? This is certified on OS X 10.5 Update 4 and above

Pas Apicella said...

Yes I use that as well. Ideally I want a CLI interface on my MAC like SQLPlus but it's clear it's not on the roadmap I guess where SQLDeveloepr is.