Help in pulling a list of values from a text file into a where clause

[tarn]

Hi all and happy holidays ....

I have a menu driven script (that is run from the Unix command line) with a number of functions one of which queries the database for a large number of identifiers, and spools the output into a file for further processing.

The list of identifiers is initially pulled into a variable (at line 15) which (if it contains a very large number of items breaches the constraints of string length in Oracle as its a variable containing one long string) this results in either an error of a inconsistent result.
I added a small fix to break the identifiers across new lines,(within line 15) this removed the string length constraint errors but now appears to inter-fear with the where cause (line 43 - 49) with sqlplus reporting missing parentheses even though they do indeed match ...

$ cat -n theFunction,txt
     1  #####################################################################
     2  #
     3  ## function Lookup20c_access_sets, Checks for existance of ACCESSSET
     4  #  and builds a new list
     5  #####################################################################
     6
     7  function Lookup20c_access_sets {
     8  cd $INDEX_DIR
     9  if [ -s duf20cEBIp.list ]; then
    10
    11  echo "Now finding any lines that failed init lookup but have accesssets in the dB....... "
    12
    13  ## Pulls in a long list of identifiers wrapped in single quoted and trailing commas (until the last line where comma is removed)
    14
    15  EBIP_LIST=`cat $INDEX_DIR/duf20cEBIp.list| awk '{print "'\''"$1"'\','"}'| awk '{line=line "\n\r"$1}  END {sub(/,$/, "", line) ;print line}'`
    16
    17  sqlplus -S $user/$pword@$ORACLE_SID <<EOF>>$LOG_DIR/get-$CONTROLLER-Query20c-EBEUs.log
    18
    19  set line 500
    20  set pages 0
    21  set echo off
    22  set verify off
    23  set termout off
    24  set heading off
    25  set feedback off
    26  set serveroutput off
    27  set newpage none
    28
    29  spool $INDEX_DIR/$CONTROLLER-SRB20c-ACCESS-EBEUs.out
    30
    31  SELECT
    32  bas.bas_ip_address
    33  ||','||eu.access_id_p1
    34  ||','||con.controller_name
    35  ||','||eu.vc_id
    36  FROM srb_bas bas,
    37         srb_accessset eu,
    38         srb_vc_topology vc,
    39         srb_vp_topology vp,
    40         srb_bas_itfs itf,
    41         srb_controllers con
    42  WHERE (
    43  (eu.vc_id = vc.vc_id)
    44  AND (vc.vp_id = vp.vp_id)
    45  AND (vp.bas_itf_id = itf.bas_itf_id)
    46  AND (itf.bas_id = bas.bas_id)
    47  AND (eu.access_id_type='1')
    48  AND (bas.controller_id=con.controller_id)
    49  AND (eu.access_id_p1 in ($EBIP_LIST))
    50  )
    51  group by bas.bas_ip_address, eu.access_id_p1,con.controller_name,eu.vc_id
    52  /
    53  spool off
    54  EOF
    55  echo "Faulty Line List built ...."
    56  else
    57  echo "No lines with faults found"
    58  fi
    59  echo ""
    60  echo ""
    61  }

I'm sure there is a better way to read in the list of identifiers ($EBIP_LIST)

All comments appreciated,

(sorry I'm not at work so don't have access to the exact error reported back but its reporting the error is related around the area of (eu.vc_id = vc.vc_id) and is highlighting with a star below the = sign of line 43... Though I think its rather related more towards the reading in of the huge ($EBIP_LIST) and due to its length has malformed the query in some way.

I will test this function in isolation once back in work to try and focus on the error but as I say any comments on how better to handle that "list of identifiers" in the where clause would be appreciated. NOTE: this cannot involve any changes to the dB only locally to the script running on the calling server.

Thanks in advance Laurie.

 

[tarn]

Doh!!

Just tested the function in isolation and first used a new/different set of data (identifiers) in the test file that gets cat'd into the variable "EBIP_LIST" and I got no error but no output using the -S (silent) flag, I then removed the -s and the output of the query looked clean with nothing out of place or any missing identifiers ......

So I moved across to the cluster node with my original test "Identifier" text file, then ran the function in isolation and got the error !!

Took a look at my "identifier" file within VI and guess what? The data that is normally expected to be clean "Alpha Char and Numbers only" had some dirty data (reserved characters embedded by the test teams too long ago for me to go kick-butt) !! so I will add a new function to groom the input source file to remove characters such as " ' > < & ..... bla bla bla

So I can now say I'm not really going mad although it was beginning to feel that way, and in future I will study my source data in detail and not assume its clean.

Thanks for looking

Laurie,

 

[SantaMufasa]

So I can now say I'm not really going mad...

Whoa...just because you were able to resolve a problem with your data is no proof that you are not going mad. <grin>


Happy New Year !

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[tarn]

OK, I thought I had this sorted but ignoring the corrupt (dirty) data issue I now find despite my code to pull the list of identifiers into the IN clause from a variable constructed from a text file:

(Line 15) EBIP_LIST=`cat $INDEX_DIR/duf20cEBIp.list| awk '{print "'\''"$1"'\','"}'| awk '{line=line "\n\r"$1} END {sub(/,$/, "", line) ;print line}'`

(Line 49) AND (eu.access_id_p1 in ($EBIP_LIST))

I run into a constraint of the $EBIP_LIST being too long and have to trim the source file down to around 500 Identifiers + the ' ', (quotes and comas) or I hit what I think is an oracle constraint ... Again away from the source so cannot quote the error.

But does anyone have any ideas on how to overcome this constraint of the size of the IN clause string length ?

Alternatively I will code around it as I will for the dirty data by splitting the source file into smaller chunks and loop through (as we did today to manually to workaround the problem).

Any help appreciated.

Laurie.

 

[LKBrwnDBA]

You could create external table to access your EBIP_LIST file, then just:

SELECT ... Etc ...
FROM ...
WHERE ...
AND (eu.access_id_p1 in (SELECT ACC_ID FROM EBIP_LIST_EXT))

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tarn]

Thanks @LKBrwnDBA ... I had considered this but my concern is limiting the impact to "select" query of the proprietary dB only. Unless I'm mistaken I have to set-up external table configuration within Oracle first ?

Thanks for the help anyway.

Laurie.