Oracle 11g script read from file in where clause (RHEL 5.7) Post: 302882981

Sponsored Content

Top Forums Shell Programming and Scripting Oracle 11g script read from file in where clause (RHEL 5.7) Post 302882981 by gacanepa on Friday 10th of January 2014 08:50:50 AM

01-10-2014

Registered User

Quote:

Originally Posted by Yoda

You could do something like this:

Code:

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.

gacanepa

View Public Profile for gacanepa

Find all posts by gacanepa

2 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Switching user to oracle to connect Oracle 11g DB with 'sysdba'

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. UNIX for Beginners Questions & Answers

ksh scripts migrating(Oracle 11G) from Solaris Sparc 10 to RHEL 7

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".

LEARN ABOUT V7

tr

TR(1)							      General Commands Manual							     TR(1)

NAME

       tr - translate characters

SYNOPSIS

       tr [ -cds ] [ string1 [ string2 ] ]

DESCRIPTION

       Tr  copies  the	standard  input  to  the  standard output with substitution or deletion of selected characters.  Input characters found in
       string1 are mapped into the corresponding characters of string2.  When string2 is short it is padded to the length of string1 by  duplicat-
       ing  its  last character.  Any combination of the options -cds may be used: -c complements the set of characters in string1 with respect to
       the universe of characters whose ASCII codes are 01 through 0377 octal; -d deletes all input characters in string1; -s squeezes all strings
       of repeated output characters that are in string2 to single characters.

       In either string the notation a-b means a range of characters from a to b in increasing ASCII order.  The character `' followed by 1, 2 or
       3 octal digits stands for the character whose ASCII code is given by those digits.  A `' followed by any other character stands  for  that
       character.

       The  following  example creates a list of all the words in `file1' one per line in `file2', where a word is taken to be a maximal string of
       alphabetics.  The second string is quoted to protect `' from the Shell.  012 is the ASCII code for newline.

	      tr -cs A-Za-z '12' <file1 >file2

SEE ALSO

       ed(1), ascii(7)

BUGS

       Won't handle ASCII NUL in string1 or string2; always deletes NUL from input.

																	     TR(1)

2 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Switching user to oracle to connect Oracle 11g DB with 'sysdba'

Discussion started by: NetBear

2. UNIX for Beginners Questions & Answers

ksh scripts migrating(Oracle 11G) from Solaris Sparc 10 to RHEL 7

Discussion started by: mssprince

LEARN ABOUT V7

tr