Use Oracle Data Base in Command Line

Cadmus Asks the Delphic Oracle Where He Can Find his Sister, Europa by Hendrik Goltzius, 1615

Sometimes I need to make some quick changes in data base. Or I need to do several changes using some patterns and templates. In that case I don't want to open editor (I use DataGrip) and wait for loading all resources, connecting to DB, opening SQL window and inserting or copying SQL query. I am that kind of person who keeps open only those applications that I am going to use in next 30 min. If I don't plan to use something in next half an hour I close it, so every opening takes some time and effort.

The best way to save time and effort is command line or, actually, scripts. SQLPlus is a great tool for using Oracle data base in the console. You can find instructions about installing SQLPlus on MacOS on StackOverflow: Oracle Sqlplus client on Mac.

The annoying thing about using data base in the terminal is connecting: you have to remember and write all credentials of data base every time you want to connect to it. But, as usually, scripting resolves this problem. I wrote simple Bash script connect-to-oracle-db.sh, that opens connection using small alias as an input. For example, I write sql test in terminal and it opens connection with test data base where I can write some SQL right ahead. Instead of test it can be any base that I work with: demo, live, production etc.

Second script that I use is changing some value in specific data base in specific table. Some times one of the clients asks me to change specific data in their DB, so now instead of opening an editor I can just run a script with parameter given by client and it changes all the data, which saves me time and doesn't disturb my attention on other tasks so much. The script looks something like that:


Of course, SQLPlus doesn't replace editors like SQL Developer or DataGrip, but it can save a lot of time and effort in performing small and routine tasks.