Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Oracle 11g script read from file in where clause (RHEL 5.7)
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
Old 01-10-2014
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.
 

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 Replies)
Discussion started by: NetBear
2 Replies

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". (3 Replies)
Discussion started by: mssprince
3 Replies

LEARN ABOUT DEBIAN

bup-margin

bup-margin(1)						      General Commands Manual						     bup-margin(1)

NAME

       bup-margin - figure out your deduplication safety margin

SYNOPSIS

       bup margin [options...]

DESCRIPTION

       bup margin  iterates  through  all  objects  in	your  bup repository, calculating the largest number of prefix bits shared between any two
       entries.  This number, n, identifies the longest subset of SHA-1 you could use and still encounter a collision between your object ids.

       For example, one system that was tested had a collection of 11 million objects (70 GB), and bup margin returned 45.  That  means  a  46-bit
       hash  would be sufficient to avoid all collisions among that set of objects; each object in that repository could be uniquely identified by
       its first 46 bits.

       The number of bits needed seems to increase by about 1 or 2 for every doubling of the number of objects.  Since SHA-1 hashes have 160 bits,
       that  leaves 115 bits of margin.  Of course, because SHA-1 hashes are essentially random, it's theoretically possible to use many more bits
       with far fewer objects.

       If you're paranoid about the possibility of SHA-1 collisions, you can monitor your repository by running bup margin occasionally to see	if
       you're getting dangerously close to 160 bits.

OPTIONS

       --predict
	      Guess  the offset into each index file where a particular object will appear, and report the maximum deviation of the correct answer
	      from the guess.  This is potentially useful for tuning an interpolation search algorithm.

       --ignore-midx
	      don't use .midx files, use only .idx files.  This is only really useful when used with --predict.

EXAMPLE

	      $ bup margin
	      Reading indexes: 100.00% (1612581/1612581), done.
	      40
	      40 matching prefix bits
	      1.94 bits per doubling
	      120 bits (61.86 doublings) remaining
	      4.19338e+18 times larger is possible

	      Everyone on earth could have 625878182 data sets
	      like yours, all in one repository, and we would
	      expect 1 object collision.

	      $ bup margin --predict
	      PackIdxList: using 1 index.
	      Reading indexes: 100.00% (1612581/1612581), done.
	      915 of 1612581 (0.057%)

SEE ALSO

       bup-midx(1), bup-save(1)

BUP

       Part of the bup(1) suite.

AUTHORS

       Avery Pennarun <apenwarr@gmail.com>.

Bup unknown-															     bup-margin(1)
All times are GMT -4. The time now is 01:28 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy