Shell Programming and Scripting

Hello,

A couple weeks ago I got some great help from a few of you regarding reading an sql query into a shell script. That post can be viewed here. Thanks to all who posted.

The issue I have now is everytime I run the query I get the following error

Code:

SP2-0027: Input is too long (> 2499 characters) - line ignored

My question to you fine people is; Have you come across something like this before and is there an alternate way to read the query into the shell script? I've tried modifying the sql query by adding breaks and carriage returns, however, the issue persists.

My shell script looks like this:

Code:

#!/bin/bash

while read line
do
   sqlquery="$sqlquery $line"
done < /query.sql

sqlplus <<-SQL
   $user/$pass@$sid
   $sqlquery
SQL

I suspect it means what it says -- you are feeding the database queries too long for it to process.

I don't think there's going to be a magic way to get around this limit just by juggling the shell a bit. The limit is in the database. The obvious thing to do would be to give it shorter queries.

Perhaps you can simplify it somehow, squeeze out some extra whitespace, break it into multiple queries which get combined later through the use of a temporary table, etc, etc.

Can you not do something like this:-

Code:

sqlplus <<-EOSQL
   $user/$pass@$sid
   @/path/to/query.sql
EOSQL

This should read the query.sql file as a deck of SQL to run, a bit like when installing/upgrading you do @catproc or perhaps @utllockt to show database locks.



I hope that this helps,
Robin

Will that cause sqlplus to have a larger buffer, though? Too long a line may be too long a line no matter where you shoehorn it.

Thanks Robin. I agree with Corona, however, modifying the script to include...

Code:

@/path/to/query.sql

...actually made a difference in the output. Now I'm getting the error below, but I think I could find this in the query.

SP2-0552: Bind variable "0" not declared

The error message you see come from the database client, not from the database itself. If you resolve the bind variable issue from your last post you'll run into the first error again.
Oracles maximum query length was 64k in 8i and I do not think that was lowered in newer versions.
If you can not shorten the query statement below the sqlplus-limit using something different to connect to the database may help. Perl for example has libraries to connect to databases and is easy to use (not sure if there are size limits there though).

ok. I guess it's back to the drawing board. I'm not a SQL guru and the query is not mine. This query runs fine when executed from Business Objects, but gives these errors when executing using sqlplus. I guess it's sqlplus that has the limitations.

Thanks to all who chimed in. I'll post back what I've done once I've found a viable solution.