How to check if an oracle database has some PDB's and then print out the data to screen-CodePudding

My end goal is to query a database, check if v$pdb's exists and then if it does, query it. If it does not, move on and do something else. Basically I want a script that works with 11g and later versions too. I'm falling at the first fence really. I simply want this to output to screen. All it outputs though is "v_str".

SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
declare
v_str varchar2(200);
v_str1 varchar2(200);
begin
v_str := 'select dbid, con_id, name into v_str1 from v$pdbs';
v_str1 := q'!begin dbms_output.put_line('v_str'); end;!';
Execute immediate v_str;
begin dbms_output.put_line(v_str1);
end;
/

Can anyone help me to get the output to prompt to screen...? Thanks!

CodePudding user response：

A little conditional compilation should help out here

SQL> declare
  2    has_container varchar2(1);
  3    in_container  varchar2(1);
  4  begin
  5  $IF DBMS_DB_VERSION.VER_LE_11_2
  6  $THEN
  7    has_container := 'N';
  8    in_container  := 'N';
  9  $ELSE
 10      has_container := case when to_number(sys_context('USERENV','CON_ID')) = 0 then 'N' else 'Y' end;
 11      in_container  := case when to_number(sys_context('USERENV','CON_ID')) > 1 then 'Y' else 'N' end;
 12  $END
 13    dbms_output.put_line('has_container='||has_container);
 14    dbms_output.put_line('in_container='||in_container);
 15
 16  end;
 17  /
has_container=Y
in_container=N

In this code

has_container = is the database multitenant (Y/N)
is_container = if the database IS multitenant, am I currently in the root or a pluggable

Then if you want a list, you can use a cursor loop

SQL> declare
  2    has_container varchar2(1);
  3    in_container  varchar2(1);
  4  begin
  5  $IF DBMS_DB_VERSION.VER_LE_11_2
  6  $THEN
  7    has_container := 'N';
  8    in_container  := 'N';
  9  $ELSE
 10      has_container := case when to_number(sys_context('USERENV','CON_ID')) = 0 then 'N' else 'Y' end;
 11      in_container  := case when to_number(sys_context('USERENV','CON_ID')) > 1 then 'Y' else 'N' end;
 12      if has_container = 'Y' and in_container = 'N' then
 13          for i in ( select name from v$pdbs )
 14          loop
 15            dbms_output.put_line(i.name);
 16          end loop;
 17      end if;
 18  $END
 19    dbms_output.put_line('has_container='||has_container);
 20    dbms_output.put_line('in_container='||in_container);
 21
 22  end;
 23  /
PDB$SEED
PDB1
PDB2
has_container=Y
in_container=N
SQL>