Suppress Output of Variables Substitution in SQLplus

Suppress output of variables substitution in sqlplus

SET VERIFY OFF is the answer.

How to disable old and new value option in output when I run PL/SQL program

simply set this (see the sqlplus user guide):

set verify off

at the top of your script.

SQL> set verify off
SQL> DECLARE
  2          inputData VARCHAR2(1024);
  3  BEGIN
  4          inputData :='&&inputData' ;
  5          Dbms_Output.put_line('Value entered is:' || inputData);
  6  END;
  7  /
Enter value for inputdata: sdf

PL/SQL procedure successfully completed.

SQL>

How to avoid variable substitution in Oracle SQL Developer

Call this before the query:

set define off;

Alternatively, hacky:

update t set country = 'Trinidad and Tobago' where country = 'trinidad &' || ' tobago';

---

From Tuning SQL*Plus:

SET DEFINE OFF disables the parsing of commands to replace
substitution variables with their values.

Suppress CURSOR message from sqlplus output

That looks like a function that returns a ref cursor; is it?

SQL> create or replace function f_my (par_select in varchar2)
  2  return sys_refcursor
  3  is
  4    l_rc sys_refcursor;
  5  begin
  6    open l_rc for par_select;
  7    return l_rc;
  8  end;
  9  /

Function created.

Here's what you currently have:

SQL> select f_my('select * From dept') as f from dual;

F
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

So, how to get rid of those CURSOR STATEMENT : 1 lines? It is SQLPlus that's displaying them (some other tool might not do it). But, as you use SQLPlus, here's how:

SQL> var v_rc refcursor
SQL> exec :v_rc := f_my('select * from dept')

PL/SQL procedure successfully completed.

SQL> print v_rc

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

When or Why to use a SET DEFINE OFF in Oracle Database

By default, SQL Plus treats '&' as a special character that begins a substitution string. This can cause problems when running scripts that happen to include '&' for other reasons:

SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
Enter value for spencers: 
old   1: insert into customers (customer_name) values ('Marks & Spencers Ltd')
new   1: insert into customers (customer_name) values ('Marks  Ltd')

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks  Ltd

If you know your script includes (or may include) data containing '&' characters, and you do not want the substitution behaviour as above, then use set define off to switch off the behaviour while running the script:

SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd

You might want to add set define on at the end of the script to restore the default behaviour.

How do I ignore ampersands in a SQL script running from SQL Plus?

This may work for you:

set define off

Otherwise the ampersand needs to be at the end of a string,

'StackOverflow &' || ' you'

EDIT: I was click-happy when saving... This was referenced from a blog.

Passing variables from bash script into Oracle SQL*Plus

You are passing positional parameters, so refer to them as &1 and &2. If you want more friendly names in the query body then just change the definition of those:

define asnid = &1
define date = "to_date('&2', 'DD-MM-RR')"

I've included (a) enclosing the second argument in quotes as it needs to be treated as a string, and (b) converting that string to a an actual date. Because it has a comma, the whole expression has to also be in double-quotes. So then you don't need the quotes in the SQL statement:

update table
set date = &date
where asnid = &asnid;

The whole to_date(...) expression will then be substituted into your query, with the passed-in value embedded; you'll see that happen if you set verify on, but you probably want it off except for testing. With it on, the script shows:

What is the ID? 42
ID, continuing to Date
What is the Arrival Date? (Answer in DD-MM-YY format please!) 01-09-21 Date 01-09-21 is in valid format (DD-MM-YY)

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 25 18:12:50 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 25 2021 18:11:54 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

old   2: set some_date = &date
new   2: set some_date = to_date('01-09-21', 'DD-MM-RR')
old   3: where asnid = &asnid
new   3: where asnid = 42

1 row updated.

Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

(You might also want to add the -s and maybe -l flags to your sqlplus call, to suppress the banners and make it exit immediately if the credentials are wrong.)

Alternatively you can keep the define simple, and apply the to_date() in the statement:

define asnid = &1
define date = &2

update some_table
set some_date = to_date('&date', 'DD-MM-RR') where asnid = &asnid;

and the output then appears as:

old   2: set some_date = to_date('&date', 'DD-MM-RR')
new   2: set some_date = to_date('01-09-21', 'DD-MM-RR')
old   3: where asnid = &asnid
new   3: where asnid = 42

but if you're doing that you might as well skip the define and just refer to &1 and &2 directly in the statement.

In both cases I've left &1 alone on the assumption that will be a number. If that is actually a string then enclose that in quotes too, either in the statement or the define.

If you have the option you should probably prompt for dates with 4-digit years; possibly in ISO format - and have a matching format in the to_date().

---

You could also skip (or minimise) the shell script by having SQL*Plus prompt for the values via accept:

accept asnid number format 99999999 prompt "What is the ID? "
accept date date format 'DD-MM-RR' prompt "What is the Arrival Date? (Answer in DD-MM-YY format please!) "

update some_table
set some_date = to_date('&date', 'DD-MM-RR')
where asnid = &asnid;

Then call it without arguments:

sqlplus data/base@srvc @/go/to/path/sql.sql

and you'll see something like this, where I've used a few non-values and invalid values just to demonstrate:

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 25 18:24:28 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Aug 25 2021 18:24:02 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

What is the ID?
SP2-0598: "" does not match input format "99999999"
What is the ID? 42
What is the Arrival Date? (Answer in DD-MM-YY format please!)
SP2-0685: The date "" is invalid or format mismatched "DD-MM-RR"
What is the Arrival Date? (Answer in DD-MM-YY format please!) 31-09-21
SP2-0685: The date "31-09-21" is invalid or format mismatched "DD-MM-RR"
What is the Arrival Date? (Answer in DD-MM-YY format please!) 30-09-21

old   2: set some_date = to_date('&date', 'DD-MM-RR')
new   2: set some_date = to_date('30-09-21', 'DD-MM-RR')
old   3: where asnid = &asnid
new   3: where asnid =         42

1 row updated.

Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics, Real Application Testing and Unified Auditing options

That will allow date strings that Oracle is willing to convert using that format mask, so it would allow you to enter '01-Sep-21'. If you don't want that then make the format mask 'FXDD-MM-RR'. (But, again, think about using 4-digit years and an unambiguous format...)

Is it possible to programmatically construct the name of a substitution variable in Oracle SQL*plus scripts?

You can trick this with new_value and defaulting SQL*Plus parameters.

Create a script like that, say for example test.sql:

-- hide output
set termout off

-- define parameter variables to be set with new_value
col par1 new_value 1 noprint
col par2 new_value 2 noprint
col par3 new_value 3 noprint

-- initialize parameter variables
select 1 par1, 2 par2, 3 par3 from dual where 1=2;

-- append comma to parameter variables, not needed for first parameter
select nullif(','||'&2',',') par2, nullif(','||'&3',',') par3 from dual;

-- show output
set termout on

-- you actual script starts here
prompt calling procedure my_proc(&1 &2 &3)

-- for next run
undef 1
undef 2
undef 3

Now call with @test 3 4

Output:

calling procedure my_proc(3 ,4 )

Or call with @test 1 2 3

Output:

calling procedure my_proc(1 ,2 ,3 )

Now you need to extend to this to the maximum expected number of your parameters.

(Note you have to be logged on to make this work, otherwise the select from dual will fail silently.)

Remove query from spool output in SQL Developer with variables

Assuming SQL_Developer is sqlplus compliant, first, try adding "SET VERIFY OFF" . If that doesn't work you can also try putting the spool after the query as in the following example:

set verify off
define x = 'X'
select * from dual where '&x' = 'X'

spool x.out

/

spool off

Note the blank line after the SELECT, and the absence of the semi-colon after the SELECT statement.

---

Related Topics