Accessing Oracle DB via Shell possible ?

UNIX AIX
1

At work we are using AIX 4.2 for the client. On this system, there is Oracle and kron, Bash and C shell.

Is it possible to access Oracle via the shell to create/update tables ?

There is this hourly data that we accumulate on the AIX and we manually copy the infos to Excel for stats. I thought it would be easier just to use Oracle on AIX.

2

AIX 4.2 ... that's old. You got 2 things? You want
a) create/update tables
b) accumulate data and copy it to an Excel sheet

For a)
You did not say what Oracle version you use, but 8 has sqlplus. Not sure if former versions had this tool too. You should look for something like sqlplus on your version and start using this, when you want to be on the shell.

For b)
When you have sqlplus or the sqlplus similar tool, extract the data into a file on your AIX box with an appropriate SQL-query and if that's not enough, maybe work it with shell/awk/sed etc.

3

sqlplus was available in Oracle 7 too :slight_smile:

Under the assumption, that the collected data is in same format all time,I'd approach this task like this:

  • Create the tables using your favorite db-tool (be it sqlplus or whatever) once. Creating tables periodically is usually bad practice.
  • Use sqlloader to insert your data into your table periodically via cron. You could use sqlplus and here-docs too, but that might be tricky - depending on your data.
  • Write a commandscript for sqlplus that creates a CSV-file or any other excel-readable format to present your data.

This links may help you with the commandline tools oracle provides:
SQL*Loader
SQL*Plus