How to add new line in sqlplus within a bash script?

Question

I have a bash script (.sh) that executed the below sqlplus code:

sqlplus -s /nolog <<EOF > /logs/sql.log
   CONNECT db_user/db_password@db_name
   $SQL_STATEMENT
   EXIT
EOF

The $SQL_STATEMENT needs to get the below statement:

DECLARE V_EXIST NUMBER(1); BEGIN SELECT COUNT(1) INTO  V_EXIST FROM USER_DB_LINKS  WHERE DB_LINK = 'MY_DBLINK'; IF V_EXIST = 1 THEN EXECUTE IMMEDIATE 'DROP DATABASE LINK MY_DBLINK';END IF; END;
  /

The '/' at the end has to come in a new line for the sqlplus code to work.

I tried multiple ways and it doesn't work, it keep showing it in the same line when executing the .sh script.

steps I tried are in this link: https://www.baeldung.com/linux/add-newline-variable-bash

Will really appreciate any help with this.

Thank you.

Answer 1

With printf:

printf -v SQL_STATEMENT '%b' "DECLARE V_EXIST NUMBER(1); BEGIN SELECT COUNT(1) INTO  V_EXIST FROM USER_DB_LINKS  WHERE DB_LINK = 'MY_DBLINK'; IF V_EXIST = 1 THEN EXECUTE IMMEDIATE 'DROP DATABASE LINK MY_DBLINK';END IF; END;\n  \\"

echo "$SQL_STATEMENT"

Output:

DECLARE V_EXIST NUMBER(1); BEGIN SELECT COUNT(1) INTO  V_EXIST FROM USER_DB_LINKS  WHERE DB_LINK = 'MY_DBLINK'; IF V_EXIST = 1 THEN EXECUTE IMMEDIATE 'DROP DATABASE LINK MY_DBLINK';END IF; END;
  \

See: help printf

Answer 2

Try this to see if cat prints with right format:

SQL_STATEMENT="DECLARE V_EXIST NUMBER(1); BEGIN SELECT COUNT(1) INTO  V_EXIST FROM USER_DB_LINKS  WHERE DB_LINK = 'MY_DBLINK'; IF V_EXIST = 1 THEN EXECUTE IMMEDIATE 'DROP DATABASE LINK MY_DBLINK';END IF; END;
  /"
cat << EOF
   CONNECT db_user/db_password@db_name
   $SQL_STATEMENT
   EXIT
EOF