CODEWITHSUNDEEP
oracle-databaseshellplsqlline-numbers
puneet
puneet

Reputation: 89

How to use oracle $$PLSQL_LINE in a shell script?

I want to log the line number of a stand alone block within a shell script.

I am trying to use the below code but its not working. Please let me know if there is a working solution for this.

#!/bin/ksh

result=`sqlplus -s $DATABASE <<EOF
SET SET HEAD OFF;
SET PAGES 0;
SET FEEDBACK OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;
SET DEFINE OFF;

BEGIN
    DBMS_OUTPUT.put_line ('Line number: '|| $$plsql_line);
END;
/
EOF`
echo $result

I get the following error:

PLS-00103: Encountered the symbol "PLSQL_LINE" when expecting one of the following: 
) = - + < / > at in is mod remainder not rem => <an exponent (**)> <>
 or != or ~= >= <= <> and or like like2 like4 likec as between from using 
|| member submultiset The symbol "," was substituted for "PLSQL_LINE" to continue

Upvotes: 0

Views: 438

Answers (2)

Thomas Dickey
Thomas Dickey

Reputation: 54563

The problem is that the here-document (starting with the first <<EOF) is expanding shell variables (anything beginning with a $). You can suppress that behavior by

  • putting a backslash before each $,

  • or noting that the here document has no useful shell variables, by quoting the first EOF, e.g.,

    result=`sqlplus -s $DATABASE <<"EOF"

or 

result=`sqlplus -s $DATABASE <<'EOF'

Further reading:

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36902

The $ needs to be escaped:

DBMS_OUTPUT.put_line ('Line number: '|| \\$\\$plsql_line);

Upvotes: 1

Related Questions