1

I'm new to working with ORACLE over SSH (putty) terminal on an Ubuntu OS, my workstation has Windows OS.

I'm programming a shell that connects to the oracle DB then exports a table into a text file and then delete some records depending on a column´s value.

No SQL Plus is installed and this is a must because we need to simulate the infraestructure of our client.

This is part of the script's code:

#!/bin/bash
su oracle
export ORACLE_SID=ORA_SID
export ORACLE_HOME=/uxx/app/oracle/product/11.2.0/client_1
export TWO_TASK=[hostname]:[port]/[ORA_SID]
isql -v [db] [user] [pass] <<EOF
CREATE OR REPLACE DIRECTORY test AS '/xxx/yyy/';
DECLARE
f utl_file.file_type;
BEGIN
f := utl_file.fopen('TEST','sample.txt','W');
for s in (select * from [table]);
loop
utl_file.put_line(f,s);
end loop;
utl_file.fclose(f);
END;
quit;
exit
EOF

When I run the script I got the following output:

SQL> SQLRowCount returns -1
SQL> [S1000][Oracle][ODBC][Ora]ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

[ISQL]ERROR: Could not SQLExecute
SQL> [37000][Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

[ISQL]ERROR: Could not SQLExecute
SQL> [S1000][Oracle][ODBC][Ora]ORA-06550: line 1, column 5:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

[ISQL]ERROR: Could not SQLExecute
SQL> [37000][Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

[ISQL]ERROR: Could not SQLExecute
SQL> [37000][Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

[ISQL]ERROR: Could not SQLExecute
SQL> [37000][Oracle][ODBC][Ora]ORA-00900: invalid SQL statement

Any help would be greatly appreciated.

Cabrakan
  • 65
  • 9
  • For one thing, no semicolon after for statement – OldProgrammer Oct 06 '14 at 20:04
  • Ok just for the record Ichecked with both the semicolon and without it, get the same errors. – Cabrakan Oct 06 '14 at 20:17
  • you are missing a `/` after the `end;` http://stackoverflow.com/a/10207695/330315 (of course that is only true for SQL*Plus - I have no idea what that `isql` tool is that you are using. That might have different rules in defining an alternate delimiter) –  Oct 06 '14 at 21:37

1 Answers1

0

It resulted SqlPlus was indeed installed on the system but since it has been years since the last time I worked on UNIX like system I didn't know how to make the application work.

I first exported the bin path to my looged session:

export PATH=$ORACLE_HOME/bin:$PATH

Then I could run the sql plus and use the spool tool.

sqlplus usr/pass

Now with SqlPlus I can just use the regular spool command.

Thanks all who helped.

Cabrakan
  • 65
  • 9