I want to write something like this:
exec SHOW CON_NAME into :=connection_name
but this doesnt work.
I know I can do this but I want to know for future reference if there is any way to do this for show
EXEC SELECT SYS_CONTEXT('USERENV','CON_NAME') into :connection_name from DUAL;
exec is just a wrapper for an anonymous block, so
exec SHOW CON_NAME into :=connection_name
is the same as
begin SHOW CON_NAME into :=connection_name end
Even with other issues fixed, show is a SQL*Plus client command, and doesn't mean anything inside a SQL or PL/SQL context.
You could do:
exec :connection_name := SYS_CONTEXT('USERENV','CON_NAME');
to avoid the context switch of selecting from dual within the PL/SQL.
Looking at the statement log in SQL Developer, show con_name is doing something similar, with a checkone bind variable it uses internally; though it also puts it through a local PL/SQL variable and trims that to 30 chars.
You could also use column ... new_value ... and query from dual, without using PL/SQL, and then use a substitution variable to refer to the value later instead of a bind variable:
column con_name new_value connection_name
select SYS_CONTEXT('USERENV','CON_NAME') as con_name from dual;
-- then later...
select '&connection_name' from dual;
Not sure how useful that would be though. Depends what you want to use it for I suppose.