args="$( cat aux.txt )"
sqlplus -s user/pass@inst << EOF
select myfield1 from mytable
where myfield2 IN ( $args );
exit
EOF
Yoda,
Thank you so very much for taking the time to read and reply to my post.
It ALMOST worked. I had forgotten to mention that the aux.txt file is 66k in size (that is the current size, but it can be up to twice as big) and contains up to 12,000 values separated by commas, like 'value1','value2','value3',...,'value12000', which seems to be a little too much for sqlplus and Oracle 11g:
Code:
USER SQL> 2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
2 SP2-0027: Input is too long (> 2499 characters) - line ignored
2 3 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
I also tried to supply the whole file as a argument to the sql script while calling it but the same limitation appears.
I'll mention the original issue in case you or someone else can suggest another workaround. The contents of the aux.txt file are originally the results of another SQL query spooled to a text file, which are ordered by a date field.
My first idea was to do this:
Code:
select myfield1 from mytable
where myfield2 IN
(
[SQL query that returns only one field (myfield2 in this example) - the contents of the aux.txt file in table format.
)
I also thought about writing a shell script that would query the database for each value in the aux.txt file, but as I mentioned earlier, this file can contain up to 12,000 records and you'll understand that I don't want to run such a number of queries.
That being said, all suggestions and hints will be more than welcome.
I need to connect my Oracle 11g DB from shell script with 'sysdba' permissions. To do this I have to switch user from 'root' to 'oracle'.
I've tried the following with no success.
su - oracle -c "<< EOF1
sqlplus -s "/ as sysdba" << EOF2
whenever sqlerror exit sql.sqlcode;... (2 Replies)
Hi All,
Now we are migrating oracle 11G from Solaris Sparc 10 to RHEL 7. We have 1000+ ksh scripts..
Could you please let em know what would be the best way to use exiting scripts in RHEL with minimal changes,.
my concern was "Is it all Solaris command work in RHEL". (3 Replies)
01-10-2014