Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I am using unix shell KSH scripting to do some table cleanup. I have a file "partner.txt" with 5000 line like this

>cat partner.txt

aaa0000
aaa0001
aaa0002
...
...
aaa5000

Using this file, I am supposed to clean few tables with matching, say agreements of the partners. So i am constructing a partner list string in the format that i can use in the sql statement with 'IN' clause (select * from tab where partner IN partner_list)

('aaa0000',
'aaa0001','aaa0002',...,'aaa0010',
'aaa0011','aaa0012',...,'aaa0020',
...
'aaa4990','aaa4991',...,'aaa5000')

I am assigning the string to partner_list variable like this.

export BO="("
export BC=")"
export BQ="('"
export QC="','"
export QB="')"
export  C=","
export CE=","'\n'
export QCE="',"'\n'"'"

partnerListLine=${BO}
while read partnerline;
do
    if [ `expr ${counter} % 10` -eq 0 ]
    then
        partnerListLine=${partnerListLine}${partnerline}${CE}

    elif [ ${counter} -lt ${numOfObsoletePartner} ]
    then
        partnerListLine=${partnerListLine}${partnerline}${C}
    fi 
    counter=`expr ${counter} + 1`
done < partner.txt
partnerListLine=${partnerListLine}${partnerline}${BC}

Then I am using this partner list to fetch my agreement list like

SQL_agreement='select distinct a.agreement from partner_agreement_map a where a.partner in ${partner_list} order by agreement asc;'

I needed the newline character in my partner list since i was using sqlplus and was encountering SP2-0027: Input is too long (> 2499 characters) I am adding the newline character by appending the below to my partner list string after N partners

CE=","'\n'

This worked fine when i was using sqlplus directly in the script.

But when i try to pass this partner_list string as parameter to a sql script, it shows '\n' in the query.

This is how i call my sql script and pass the parameter

sqlplus -s ${REFERENCE_DB_USER}/${REFERENCE_DB_PASS}@${DATABASE_INSTANCE} << !!
set serveroutput on size 10000;
set feedback off;
set verify off;
set echo off;
set term off;
set pagesize 0;
SET linesize 1000;
SET TRIMSPOOL ON;

spool  1_del_agreement_spool_$$.lst;

@1_del_agreement.sql ${partner_list};

spool off;

exit;
/
!!

this is my spooled file

>cat 1_del_agreement_spool_18165.lst                                   <

select distinct a.agreement from partner_agreement_map a where a.partner in ('aaa0000',\n'aaa0001','aaa0002','aaa0003',...'aaa0010',\n'aaa0011'...) order by agreement asc
                                                                                    *
ERROR at line 1:
ORA-00907: missing right parenthesis

How can i maintain the newline character when i pass the parameter to the sql script and not have it replaced to '\n'? I have tried ANSI-C quoting but failed.

Please let me know if you would need more details of the shell or sql script

share|improve this question
    
Note: You shouldn't use !! for the here document because there is a shell command with the same name (recall the last command). Use EOF instead. –  Aaron Digulla Feb 24 at 11:07
    
Edit the question and show us the code which assigns a value to partner_list. –  Aaron Digulla Feb 24 at 11:07
    
i have added the snippet. also i added the single quotes to the partner list. I am looping on the file, and adding commas, qoutes and newline chars as needed. –  pradipti Feb 24 at 11:28
    
After trying all night, i have given up. Thanks Aaron and mplf for your inputs. I have decided to change my solution from file based to table based. I will be reading the partner.txt file and inserting the partners in a dummy temporary table. Then I can formulate queries with ease. In fact, i think this should have been my first design :) I wish my team lead ever reviews design rather than formatting in code issues :P –  pradipti Feb 25 at 7:08

3 Answers 3

up vote 1 down vote accepted

UPDATED MY ENTIRE SOLUTION DESIGN

After trying all night, i have given up. Thanks Aaron and mplf for your inputs.

I have decided to change my solution from file based to table based. I will be reading the partner.txt file and inserting the partners in a dummy temporary table. Then I can formulate queries with ease on other tables.

In fact, i think this should have been my first design :) There may be something very minor that i was missing in the previous design. But anyways, this will be much easier I wish my team lead ever reviews design rather than code formatting issues :P

share|improve this answer
    
Another solution would be to use a real programming language instead where you can build strings without surprises and a database driver that allows you to avoid sqlplus with all it's quirks and security holes (like publishing your passwords to anyone on the same computer). stackoverflow.com/questions/13476419/… –  Aaron Digulla Feb 25 at 9:11

I don't have a working solution but a hint: '\n' means "insert the literal backslash followed by n". So you tell the shell to leave this string alone.

Try NL=$(echo -e '\n') or similar to get a string variable which actually contains a newline. Then you can define CE=",$NL"

The shell might preserve this new line character as it processes the string.

Or use a tool like awk to create a string value with newlines which you assign to partner_list with partner_list=$(awk ...) to prevent the shell from doing any kind of processing of the value.

If that doesn't work, you may have to write the data to a file (with new lines).

share|improve this answer

If you are using bash you can use $'\n' to print a newline character which would make your example

if [ `expr ${counter} % 10` -eq 0 ]
then
    partnerListLine=${partnerListLine}${partnerline}"',"$'\n'"'"
else
    ...

Example:

$ echo hello"',"$'\n'"'"
hello',
'
share|improve this answer
    
I m sorry, i missed mentioning i am using KSH. Edited the question. thank you though. –  pradipti Feb 24 at 11:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.