Home » SQL & PL/SQL » Client Tools » How to pass a parameter from PL/Sql block to SQL*Plus (Oracle Database 19c)
How to pass a parameter from PL/Sql block to SQL*Plus [message #683690] Fri, 12 February 2021 20:14 Go to next message
Andrey_R
Messages: 372
Registered: January 2012
Location: Israel
Senior Member

Hi everyone,
I am trying to see how I can pass a parameter from PL/Sql block to SQL*Plus.

I can do it fine with SQL:


Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL> COLUMN PROCEED_STATUS NEW_VALUE PROCEED_STATUS NOPRINT
SQL>
SQL> SELECT 1 PROCEED_STATUS FROM DUAL;




SQL>
SQL> SELECT &PROCEED_STATUS FROM DUAL;
old   1: SELECT &PROCEED_STATUS FROM DUAL
new   1: SELECT          1 FROM DUAL

         1
----------
         1

SQL>
When I try to do the equivalent in a PL/Sql anonymous block, it doesn't seem to recognize it:

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL>
SQL>
SQL> COLUMN PROCEED_STATUS NEW_VALUE PROCEED_STATUS NOPRINT
SQL>
SQL> BEGIN
  2  execute immediate 'SELECT 1 PROCEED_STATUS FROM DUAL';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT &PROCEED_STATUS FROM DUAL;
Enter value for proceed_status:
old   1: SELECT &PROCEED_STATUS FROM DUAL
new   1: SELECT  FROM DUAL
SELECT  FROM DUAL
        *
ERROR at line 1:
ORA-00936: missing expression


SQL>


I guess I can turn my anonymous block into a function and integrate it with the select statement:

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL>
SQL>
SQL> create function f1 return number
  2  is
  3  begin
  4  return 1;
  5  end;
  6  /

Function created.

SQL>
SQL>
SQL> COLUMN PROCEED_STATUS NEW_VALUE PROCEED_STATUS NOPRINT
SQL>
SQL> SELECT f1 PROCEED_STATUS FROM DUAL;




SQL>
SQL> SELECT &PROCEED_STATUS FROM DUAL;
old   1: SELECT &PROCEED_STATUS FROM DUAL
new   1: SELECT          1 FROM DUAL

         1
----------
         1

SQL>
But just wondering, is there a way to directly pass a parameter from PL/Sql block to SQL*Plus?

Thank you,
Andrey
Re: How to pass a parameter from PL/Sql block to SQL*Plus [message #683691 is a reply to message #683690] Sat, 13 February 2021 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67951
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As all PL/SQL blocks start with DECLARE/BEGIN and end with END; you can do:
SQL> COLUMN PROCEED_STATUS NEW_VALUE PROCEED_STATUS NOPRINT
SQL> select q'[execute immediate 'SELECT 1 FROM DUAL';]' PROCEED_STATUS from dual;



1 row selected.

SQL> set verify on
SQL> begin &PROCEED_STATUS end;
  2  /
old   1: begin &PROCEED_STATUS end;
new   1: begin execute immediate 'SELECT 1 FROM DUAL'; end;

PL/SQL procedure successfully completed.

[Updated on: Sat, 13 February 2021 09:24]

Report message to a moderator

Re: How to pass a parameter from PL/Sql block to SQL*Plus [message #683692 is a reply to message #683691] Sat, 13 February 2021 06:40 Go to previous message
Andrey_R
Messages: 372
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Sat, 13 February 2021 08:10

As all PL/SQL blocks start with DECLARE/BEGIN and ends with END; you can do:
SQL> COLUMN PROCEED_STATUS NEW_VALUE PROCEED_STATUS NOPRINT
SQL> select q'[execute immediate 'SELECT 1 FROM DUAL';]' PROCEED_STATUS from dual;



1 row selected.

SQL> set verify on
SQL> begin &PROCEED_STATUS end;
  2  /
old   1: begin &PROCEED_STATUS end;
new   1: begin execute immediate 'SELECT 1 FROM DUAL'; end;

PL/SQL procedure successfully completed.
Cool trick!

Thank you.
Andrey
Previous Topic: SQL*Plus: How to check if a bind variable is already declared?
Next Topic: Toad debug strange behavior
Goto Forum:
  


Current Time: Mon Sep 27 05:57:13 CDT 2021