OraFAQ Forum: Client Tools » How to pass a parameter from PL/Sql block to SQL*Plus

Home » Other » Client Tools » How to pass a parameter from PL/Sql block to SQL*Plus (Oracle Database 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to pass a parameter from PL/Sql block to SQL*Plus [message #683690]

Fri, 12 February 2021 20:14

Andrey_R
Messages: 441
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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As all PL/SQL blocks start with DECLARE/BEGIN and end with END; you can do:

Code: [Select all] [Show/ hide]

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

Andrey_R
Messages: 441
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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Previous Topic:	SQL*Plus: How to check if a bind variable is already declared?
Next Topic:	Toad debug strange behavior

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 04 04:15:55 CDT 2025