Problem running plsql using printf command on bash shell


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem running plsql using printf command on bash shell
# 1  
Old 09-27-2018
Problem running plsql using printf command on bash shell

I am running plsql using printf on a shell, but i am getting some strange error, can someone point what exactly am i missing,



Code:
$ echo $SHELL
/bin/bash  
$ printf "
> SET serveroutput ON trimspool on feed off echo off
> declare
> p_val number;
> d_val varchar2(10);
> begin
> SELECT ROUND((1- (free_mb / total_mb))*100) into p_val from v\$asm_diskgroup where name in (select substr(value,2,8) from v\$parameter where name = 'standby_archive_dest');
> select trim(value) into d_val from v\$parameter where name = 'db_create_file_dest';
> if p_val >= 95 then
> execute immediate 'alter system set standby_archive_dest = ''||d_val||'' scope=both';
> execute immediate 'alter system set log_archive_dest_2 = ''location = ''||d_val||'' valid_for=(''STANDBY_LOGFILE'',''ALL_ROLES'')'' scope=both';
> DBMS_OUTPUT.PUT_LINE('DISKGROUP_MODIFIED');
> else
> DBMS_OUTPUT.PUT_LINE('NO_ACTION');
> end if;
> end;
> /
> " | sqlplus -s / as sysdba
  declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5



I am getting correct values for p_val & d_val as well. Thanks !

Regards
Kannan
# 2  
Old 09-27-2018
Hi,

The first parameter of printf is a format parameter where certain characters have special meanings. Therefore it is best to not use data as the first parameter. So instead of

Code:
printf " ....
"

Use
Code:
printf '%s\n' " ...
"

Note that the double quotes still allow things like variable expansion (for example $var).

If you do not want that then use single quotes..
Code:
Use 
printf '%s\n' ' ...
'

That said a better option would be to use a so-called "here document"

Code:
sqlplus -s / as sysdba << EOF
SET serveroutput ON trimspool on feed off echo off
declare
...
EOF

Where EOF is an arbitrary word where the second occurrence needs to be on a line containing only that word.

If you want to avoid variable expansion, use quotes (single or double) around the first occurrence of the arbitrary word :
Code:
sqlplus -s / as sysdba << "EOF"
SET serveroutput ON trimspool on feed off echo off
declare
...
EOF

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Printf command , a little more dynamic..

A big hello to everyone tagged to this site of knowledge . This is the first post of mine and I am looking forward to an enjoyable stint in this forum where I get to know a lot of new ideas and share whatever knowledge (its not much though :) ) I have acquired throughout my career so far with... (4 Replies)
Discussion started by: kumarjt
4 Replies

2. UNIX for Dummies Questions & Answers

Running set options from the command line and bash command

I'm reading about debugging aids in bash and have come across the set command. It says in my little book that an addition to typing set you can also use them "on the command line when running a script..." and it lists this in a small table: set -o option Command Line... (5 Replies)
Discussion started by: Straitsfan
5 Replies

3. Shell Programming and Scripting

Problem with printf in UNIX KSH shell

Hi ALL, I am using SunOS 5.9 and KSH(bin/ksh) The problem am facing is error message diaplyed on screen printf: 12099415.79 not completely converted printf: + expected numeric value printf: 11898578.29 not completely converted When i try printing with The output is... (6 Replies)
Discussion started by: selvankj
6 Replies

4. Shell Programming and Scripting

problem with printf in shell script

i have written small script as follows: name="hi hello" printf "%-20s" $name This gives me strange output. -20s format is applied on both word of string. i.e it displays both word hi and hello in space of 20 length. I want to display entire string "hi hello" in length of 20 space. plz... (2 Replies)
Discussion started by: admc123
2 Replies

5. Shell Programming and Scripting

How to pass value from plsql script to unix bash?

Hi This is my bash script.i am calling validation.sql and passing a value to it using ${flds}. i want the cnt variable in plsql script to be passed to unix. LOADREC=`sqlplus -s $ORACLE_USR <<-EOF spool $ORACLE_LOG_FILE; echo "barani" @validation.sql #calling the plsql script ${flds}... (6 Replies)
Discussion started by: barani75
6 Replies

6. Programming

running PLSQL scripts through shell script

I am running the following ealth checks on my server there are two databases in my server . MODEL1 and MODEL2 i connect with the first database as sqlplus model1/password Then i exceute a query select x from table (4 Replies)
Discussion started by: asalman.qazi
4 Replies

7. Shell Programming and Scripting

printf in bash shell not printing negative values

hi i am using printf in a script and it is not printing negative values..i have to use printf to get rid of the newline..here is my code: fin=`echo $a - $b | bc` printf "${fin}," >> test these statements are in a loop. here is what i get when i try to subtract 4 from 8: ./scr1: line... (2 Replies)
Discussion started by: npatwardhan
2 Replies

8. UNIX for Advanced & Expert Users

Problem in running bash shell commands on HP-UX machine

Hello All, After login to the server we are explicitly calling /usr/local/bin/bash to activate bash shell properly. But since commands are not executing properly so I think it is not initialized well. I am facing following problems: 1) If I want to have a look on a particular file using tail... (6 Replies)
Discussion started by: abhishek0071
6 Replies

9. Shell Programming and Scripting

Shell - PLSQL multiple process problem.

I have a Shell Script which runs a PLSQL stored proc. Here is the summary: - create LOCK file - check if LOCK exist, if exists, abort. - run Stored Proc and insert/update DB - remove LOCK file The problem is in random cases, some records are inserted 2x (with exactly the same data,date... (1 Reply)
Discussion started by: deepsweech
1 Replies

10. UNIX for Dummies Questions & Answers

Problem running plsql & unix commands in 1 script

Hi, I need help again. When I run this shell script, it only runs the unld_date.sql piece and exits. How can I structure this to run all the way to the end? When I don't have the unld_date.sql piece in here, everything runs fine from the date compare piece all the way to the end. Thanks in... (5 Replies)
Discussion started by: siog
5 Replies
Login or Register to Ask a Question