2 Replies Latest reply: Jul 11, 2012 5:09 PM by Tommyreynolds-Oracle RSS

    URL ampersand in shell causing nightmare.  Please help!

    sreese
      Gurus,

      I've got an alert setup as a shell script. They've asked me to include a URL which contains & (ampersands). Unix ampersands must be surrounded in double quotes. This will screw up my output since SQL is outputing the literal double quote value. Can this link be added to the email body? And do you have syntax recommendations to do so?

      Thanks so much for your help.

      Scott

      runtime=`date`
      machine=`hostname`
      LISTFILE="$SPL_TOP/sql/overpick.doc"
      UNPW="$1"
      email="$5"
      numdays="$6"
      sqlplus -s $1@$ORACLE_SID  <<EOF > "${LISTFILE}"
      ttitle 'Over Pick Report'
      col Instructions heading "SAME LPN SHOWING SHIPPED TWICE" format a4000
      select 'For Serial Control ...reference http://gaalpsvr05a2.us.com:86f=141"&"t=2057"&"p=2334"&"hilit=over+pick"&"sid=56b38c529a41a3b#p2334'  Instructions
      from dual;
      col item_name format a18
      col building format a10
      set linesize 80
      set pagesize 80
      set newpage 0
      set arraysize 10
      break on date_created skip1
      col req_qty heading "Req Qty" format 999
      col qty_pkd heading "Pkd Qty" format 999
      col warehouse heading Whse format a7
      col acct heading Acct format a6 
      col ord_num heading Order format 999999999
      select  sum(b.quantity) qty_pkd, 
               (spl_get_requested_qty(a.ims_order_number, a.item_id)-nvl(c.cancelled_qty,0)) req_qty, 
               substr(a.warehouse,1,7) warehouse, 
               a.ims_order_number ord_num, 
               spl_get_item_name(a.item_id) item_name, 
               (select mtl.segment2 from inv.mtl_system_items_b mtl where mtl.inventory_item_id = a.item_id and mtl.organization_id = 543) acct,
               trunc(a.creation_date) date_created
      from    wms_order_line        a,
               wms_order_line_detail b,
               spl_request_lines_ext c
      where a.line_id = b.line_id 
       and  a.ims_order_number = c.header_id
      and  a.ims_order_line_id = c.line_id
      and   b.detail_status in ('SHIPPING','PACKOUT','SHIPPED','SORTING','AT STATION','IN TRANSIT TO PACKOUT') 
       and  b.creation_date >= sysdate - '$numdays'
      group by  
      (spl_get_requested_qty(a.ims_order_number, a.item_id)-nvl(c.cancelled_qty,0)), 
      a.ims_order_number, 
       a.warehouse , 
      a.item_id, trunc(a.creation_date)
      having sum(b.quantity) > (spl_get_requested_qty(a.ims_order_number, a.item_id)-nvl(c.cancelled_qty,0))
      order by 
      trunc(a.creation_date),
      a.ims_order_number ,a.warehouse;
      EOF
      
      export GOT_ROWS=""
      export GOT_ROWS=`grep "no rows selected" "${LISTFILE}"`
      echo "GOT_ROWS: ${GOT_ROWS}"
      
      if [ -n "${GOT_ROWS}" ]
      then
         echo "No email will be sent" 
      else
         {
         echo "Subject: Over_Pick_Notification"
         echo "To: ${email}"
         echo "From: Over_pick_notification"
         /bin/uuencode ${LISTFILE} ${LISTFILE} 
         }|/usr/sbin/sendmail ${email}
      fi
      
      exit 0
        • 1. Re: URL ampersand in shell causing nightmare.  Please help!
          Dude!
          The command-line interpreter and sqlplus will both parse the content of the script. The ampersand is no problem for the shell since you have it between quotes, but the & sign in sqlplus has special meaning.

          I think there are 2 ways you can solve your problem,
          a) Disables sqlplus parsing
          b) Or use the %HEX format in the URL string. %26=&

          Consider the following example:
          #!/bin/bash
          sqlplus -s /nolog << EOF > output.txt
          set define off
          connect scott/tiger
          select 'http://www.google.com/somestuff/%26more&stuff\$0' Instructions
          from dual;
          exit;
          EOF
          $ ./sqltitle; cat output.txt
          
          INSTRUCTIONS
          --------------------------------------------
          http://www.google.com/somestuff/%26more&stuff$0
          Btw, you should not provide username/password at the sqlplus command line because the connect string including the password will visible with "ps" command. Better use /nolog and "connect" statement.
          • 2. Re: URL ampersand in shell causing nightmare.  Please help!
            Tommyreynolds-Oracle
            Unix ampersands must be surrounded in double quotes.
            Or use single quotes like this: '%'

            Or use backslash like this: \%

            HTH