Avoid $ symbol while calling sqlplus in shellscript.

Shell Programming and Scripting
1

Hi All,

we have requirement, i am created a shell script , inside i am connecting sqlplus and execute the query. below my code for your reference.

get_sqlid ()
{
sqlid=$(
  sqlplus -s $PBDW_USERID/$PBDW_PW@$PBDW_SID <<EOF
  DEFINE TBLNAME=$1
  set feedback off
  set serverout on size 1000000
  set verify off

  SELECT MAX(SQL.SQL_ID)
    FROM V$SQL SQL, V$SESSION SES
   WHERE SQL.SQL_ID = SES.SQL_ID
     AND SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT('userenv', 'CURRENT_SCHEMA')
     AND SES.SCHEMANAME = SYS_CONTEXT('userenv', 'CURRENT_SCHEMA')
     AND SES.STATUS = 'ACTIVE'
     AND UPPER(SQL.SQL_FULLTEXT) LIKE '%' || '&&TBLNAME' || '%'
     AND SQL.SQL_FULLTEXT NOT LIKE '%V$SESSION%'
     AND TO_DATE(SQL.LAST_LOAD_TIME, 'YYYY/MM/DD HH24:MI:SS') BETWEEN
         TO_DATE(SQL.LAST_LOAD_TIME, 'YYYY/MM/DD HH24:MI:SS') - 0.15 / 48 AND
         TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                 'YYYY/MM/DD HH24:MI:SS');

EOF
)
wlog "${sqlid}"
                
}

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

if i execute the script, V$SQL , V$SESSION table not available since it looks variable of $..

below error

20170929 06:34:17, ---- parsing table name : T_PBAMLGLOBUSSPRING
20170929 06:34:18,     FROM V SQL, V SES
                *

Kindly advise how to avoide $ symbol while connecting sqlplus.

2

In "here documents", the input lines are subject to expansion unless (part of) the word is quoted, so try again quoting something.
man bash :

1 Like
3

Another approach is you can put the SQL in a .sql file and call it:-

  sqlplus -s $PBDW_USERID/$PBDW_PW@$PBDW_SID <<EOF
  @/path/sqlid.sql $1
EOF

Or you can simply escape the dollar sign:-

  SELECT MAX(SQL.SQL_ID)
    FROM V\$SQL SQL, V\$SESSION SES
   WHERE SQL.SQL_ID = SES.SQL_ID
     AND SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT('userenv', 'CURRENT_SCHEMA')
     AND SES.SCHEMANAME = SYS_CONTEXT('userenv', 'CURRENT_SCHEMA')
     AND SES.STATUS = 'ACTIVE'
     AND UPPER(SQL.SQL_FULLTEXT) LIKE '%' || '&&TBLNAME' || '%'
     AND SQL.SQL_FULLTEXT NOT LIKE '%V$SESSION%'
     AND TO_DATE(SQL.LAST_LOAD_TIME, 'YYYY/MM/DD HH24:MI:SS') BETWEEN
         TO_DATE(SQL.LAST_LOAD_TIME, 'YYYY/MM/DD HH24:MI:SS') - 0.15 / 48 AND
         TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
                 'YYYY/MM/DD HH24:MI:SS');
4

If i use in sql file ,we cant get the result and assign in sqlid variable

5

That is not a true statement. Here is an example:-

$ cat script.ksh 
#!/bin/ksh

conn_str="user@passwd@dbinstance"

sqlid=$(
        sqlplus -s $conn_str << EOF
        set echo off head off feed off pagesize 0 trimspool on linesize 1000 num 20
        @sqlid.sql
EOF
)

print $sqlid

$ cat sqlid.sql
select sql_id from v$session where sql_id is not null and rownum < 2;

Here is the output I got:-

$ ./script.ksh
0akaxw65f1nym
1 Like