Show only input from user at runtime with sql developer and not the whole code-CodePudding

This is an example and i know this post too:

When i run this code in sql developer with pl/sql:

DECLARE
  a NUMBER;
BEGIN
  a:=&a;
  DBMS_OUTPUT.PUT_LINE(a);
end;

I get a prompt for my user input value and i see my input value on the script output.

Ok this works.

But i see the whole code and my input value.

A solution for exactly this example, no sqlplus, prompt or accept and not this post:

Provide a message prompt for user input in SQL Developer 3.1.07

Is it possible to deactivate the whole code output and only see my input value, is there a sql developer setting?

I'm using:

Oracle SQL Developer

Version 21.4.1.349
Build 349.1822
On a Windows 10 PRO

Oracle Database

21c Express(21.0.0.0.0)

CodePudding user response：

Substitution variables, like your &a, are exclusively an SQL*Plus concept. The only reason SQL Developer knows what to do with it is that SQL Developer supports most of the SQL*Plus scripting language. So your "no sqlplus" requirement (in your question) is nonsensical. Any solution must be a SQL*Plus solution in one way or another.

What you are asking for is very simple. SQL*Plus supports a feature called verify, which does exactly what you don't want it to do: showing you the before and after versions of your code. By default it is on - all you need to do is to turn it off before you run your code.

Another thing is the displaying of just your value in the output. By default the serveroutput feature is off; to see the output from dbms_output.put_line() you must turn it on. It is not clear if you have done that.

So, before you run your code, you must run these two SQL*Plus commands; the first one is the one you were asking about, the second one is to see the output from put_line(). Note that both are SQL*Plus commands; they are not terminated with semicolon (although SQL Developer will not throw an error if they are).

set verify off
set serveroutput on

As an aside, SQL*Plus tolerates some syntax errors, so your code will run as written (unless it's surrounded by other code); but technically, you are missing a forward slash on a line by itself at the end of your code, to indicate the end of a PL/SQL block.

In the editor window:

set verify off
set serveroutput on

DECLARE
  a NUMBER;
BEGIN
  a:=&a;
  DBMS_OUTPUT.PUT_LINE(a);
end;
/

Pop-up window asks for value for a; I enter the value 5 and hit the OK button

Script output (lower pane):

5


PL/SQL procedure successfully completed.

If your next question is "how do I make it so that PL/SQL procedure successfully completed does not appear in the output window" - the answer is similar:

set feedback off

(also a SQL*Plus command, understood by SQL Developer, and not terminated with a semicolon).