sqlplus returning value - remove carriage return '\r' - Please help

Guys - Simple code, i am trying to get a number back from sqlplus call to a query. After that, i need to use that number in a loop.

---------------------------------

#!/bin/ksh
VALUE=`sqlplus -silent sh/password@sh <<END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit;
END`

$VALUE=$VALUE + 1

---------------------------------

Problem is, as soon as i use the variable returned from the sqlplus call, it gives me the following error:

bash: $'184\r=': command not found

I have gone through any number of code syntax and i still need help getting this \r guy out of the variable into a new one so i can do some operations on it.

Any help is "GREATLY" appreciated

---------- Post updated at 04:51 AM ---------- Previous update was at 04:35 AM ----------

I am guessing this problem will not be resolved if i spool the output to a file and read it back in?

Any help is appreciated.

Thanks,

this will not work:

$VALUE=$VALUE + 1

assuming, that "\r" are two printable characters \ and r:

VALUE='5\r'
VALUE=$(($(echo $VALUE | sed 's/\\r//') +1))
echo $VALUE
6

should work for ksh and bash

cheers

first of all
you specify KSH but the error states you're working in bash

this wotks for me

@ecarh0u:> value=""
@ecarh0u:> value=`sqlplus -s $user/$pwd@osdp << EOF
more>set pages 0 feed off hea off;
more>select max(sequence#) from v\\\$log_history;
more>exit;
more>EOF
more>`
@ecarh0u:> echo $value
42765
@ecarh0u:> ((value=$value + 1))
@ecarh0u:> echo $value         
42766

Is there a dollar sign before your first occurence of value ($VALUE=$VALUE + 1) or is this your prompt ? If it is a dollar, remove that one.

Thanks, will look into this and post my feedback. Regards and thank you very much for helping out. You guys are great.

Best regards,

 
#!/bin/ksh
VALUE=`sqlplus -silent "/ as sysdba" <<END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit;
END`
 
VALUE=$VALUE + 1

Replace
$VALUE=$VALUE + 1
with
VALUE=$VALUE + 1
to increment archive log number.

Urs

are you sure? I don't think so...

funksen is right . Both cases are syntax errors and do not do arithmetic.

Also, are you sure about

Assuming that the table is called v$log_history , we only need one backslash to protect the $ from shell.

Hi all,
Sorry for late reply.

Not one \ two \\ is enough( if we use one \ we will get ORA- errors)

I know only two \\ before $ (I am observed the same before) but three \\\ also working fine for me

Thanks for pointing that out scripter_online.

The table v$log_history is a standard table and I was able to try it.

Within the backticks we need three backslashes.
Without the backticks we need one backslash.

I was testing without the backticks !

Comment: It is much easier to put the whole sql program in a file and execute that. That way you do not have to protect shell characters.

Re-reading the posts, "plelie2" fixed the o/p issue yesterday.

Hi methyl,

Without the backticks we need one backslash.
Within the backticks we can use two or three backslashes.

Please check the example below

$ VALUE=`sqlplus -S "/ as sysdba" <<END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\$log_history;
exit;
END`
$ echo $VALUE
1216
$ VALUE1=`sqlplus -S "/ as sysdba" <<END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit;
END`
$ echo $VALUE1
1216

Both are working

I use(required) three \\\ at one suituation to connect oracle !!!!! Sorry I forgot the suituation

To know how to connect to oracle from unix shell script
Connecting to oracle database from unix shell script | Scripter World

Interesting scenario scripter.online. I have been writing unix shell scripts to run Oracle processes for too many years and long ago stopped using unix "here documents" for Oracle programs because of the clashes with unix shell. I can explain the multiple backslash scenario, but continue to recommend storing Oracle programs in ".sql" text files.

BTW: The link provided leads to a website with many typos. View with caution.
P.S. forget to "quit" an Oracle session.