Hi Everyone,
I am trying to figure out how to enable "serveroutput" after I run "execute immediate" of "alter session set container".
I have below code, but it did not give me the output I want. I cannot see the lines of "'sql_stmt is" and "'current checking for pluggable database" from the out put. This is Oracle 12.1.0.2.
Thank you.
$ORACLE_HOME/bin/sqlplus -s '/as sysdba' << EOF >> $logfile
set serveroutput on size 1000000;
set lines 150 pages 1000;
col sql_text format a100
col username format a20
exec dbms_output.put_line('checking DATABASE $ORACLE_SID .................');
show con_name
show user
declare
v_container varchar2(20);
v_con2 varchar2(20);
sql_stmt varchar2(100);
begin
for current_container in (SELECT * FROM V\$CONTAINERS WHERE NAME NOT IN ('CDB\$ROOT','PDB\$SEED')) loop
v_container := current_container.name;
if v_container is NOT NULL then
sql_stmt := 'alter session set container='||v_container;
dbms_output.put_line('sql_stmt is ... '||sql_stmt);
execute immediate sql_stmt;
select name into v_con2 from v\$containers;
dbms_output.enable();
dbms_output.put_line('current checking for pluggable database .. '||v_con2);
end if;
end loop;
end;
/
show con_name
exit;
EOF