I am trying to execute PL/SQL block in shellscript. But while executing the script the out variable throws DB result along with Junk values

user_4983550 0
Viewed 144
if [ $returncode -eq 0 ]

then

 query_msg=`$ISQL -S $USERNAME/$PASSWD@$SERVICENAME <<EOJ

        set serveroutput on;

        set heading off;

        set feedback off;

        set linesize 150;

declare

        out_value varchar2(32767);
BEGIN

        SELECT MESSAGE into out_value FROM RED.ERROR_LOG WHERE PROC = 'colour' and 

    to_char(to_date(DT,'DD-MON-YY')) = to_char(to_date(sysdate,'DD-MON-YY'));

dbms_output.put_line(out_value);

END;

/

EOJ`

        echo $query_msg > $DATADIR/colour_DB.log

in that log im getting query result along junk values ? i am missing something while declaring variable in plsql block? Can some help me on this?

query result : - 

+query_msg=$'declare\n*\nERROR at line 1:\nORA-01403: no data found\nORA-06512: at line 5'
+ echo declare 0 1 221.log 132.log 321.log 456.log --> these are the files in the server path(unwanted result).
sqlplus plsql shell oracle unix
3 Answers

In the shell, * expands to the list of files in the current directory, so echo * will produce the file list you are seeing.

The output from your PL/SQL block from SQL*Plus is something like:

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

Therefore echo $query_msg will print the word declare followed by the list of files in the current directory, followed by the rest of the message.

To prevent this, put it in soft-quotes:

echo "$query_msg"

or else use the SQL*Plus spool command to capture the original text in a file, which will also preserve the formatting.

user_2304710

I can reproduce but cannot explain this issue.

A possible workaround is to replace echo $query_msg > $DATADIR/colour_DB.log by a SPOOL command at the beginning of SQL statements:

SPOOL colour_DB.log
user_107268500

The error ORA-01403: no data found is thrown because your query finds no data. You should catch it with something like:

DECLARE
  out_value varchar2(32767);
BEGIN
  SELECT MESSAGE into out_value 
   FROM RED.ERROR_LOG 
  WHERE PROC = 'colour' 
    AND to_char(to_date(DT,'DD-MON-YY')) = to_char(to_date(sysdate,'DD-MON-YY'));
  dbms_output.put_line(out_value);
EXCEPTION WHEN NO_DATA_FOUND THEN
  dbms_output.put_line('Nothing found');
END;

EDIT: I think I understand now better what you want. I would use

query_msg=`$ISQL -s $USERNAME/$PASSWD@$SERVICENAME <<EOJ

SET HEADING OFF
SET FEEDBACK OFF
SET LINE 150

SELECT message
  FROM error_log
 WHERE proc = 'colour'
   AND dt BETWEEN TRUNC(sysdate) AND TRUNC(sysdate)+1
EOJ`

I haven't found a good way to show in SQL that no data is present (only in PL/SQL, but then spooling the output is more messy). So I'd suggest to handle this case in the bash script. Sadly my shell scripting is not good enough, but something like

if query_message is empty then query_message = 'No data found' fi
echo "$query_message"
user_18640290
Related