I am running the following query to get list of all PDBs using SQLPLUS and calling each PDB NAME using for loop in shell script. Unfortunately, few PDB NAMEs are split/broken in the output(for example, P DB1P016, PD B1P032 etc) so the variable is not assigning proper/correct value. Any idea to fix this?

SQL> select LISTAGG(a.name, ' ') WITHIN GROUP (ORDER BY name) as pdbs from v$pdbs a;

IOC1P002 PDB$SEED PDB1P001 PDB1P002 PDB1P003 PDB1P004 PDB1P005 PDB1P006 PDB1P007 PDB1P008 PDB1P009 PDB1P010 PDB1P012 PDB1P013 PDB1P014 PDB1P015 P
DB1P016 PDB1P017 PDB1P018 PDB1P019 PDB1P020 PDB1P021 PDB1P022 PDB1P023 PDB1P024 PDB1P025 PDB1P026 PDB1P027 PDB1P028 PDB1P029 PDB1P030 PDB1P031 PD
B1P032 PDB1P033 PDB1P034 PDB1P035 PDB1P036 PDB1P037 PDB1P038 PDB1P039 PDB1P040 PDB1P041 PDB1P042 PDB1P043 PDB1P044 PDB1P045 PDB1P046 PDB1P047 PDB
1P048 PDB1P049 PDB1P050 PDB1P051 PDB1P052 PDB1P053 PDB1P054 PDB1P055 PDB1P056 PDB1P057 PDB1P058 PDB1P059 PDB1P060 PDB1P061 PDB1P062 PDB1P063 PDB1
P064 PDB1P065 PDB1P066 PDB1P067 PDB1P068 PDB1P069 PDB1P070 PDB1P071 PDB1P072 PDB1P073 PDB1P074 PDB1P075 PDB1P076 PDB1P077 PDB1P078 PDB1P079 PDB1P
080 PDBCUS12
  • A shell script for loop can iterate over words separated by new lines, so just ditch the listagg in your query and do a straight select – Philᵀᴹ Sep 9 '19 at 6:39

As with many things database-y, it's probably easier to extract them as rows and then reformat using your favourite shell-based text processor or, if you still want to try and get them all on one line, make sqlplus's "line" as big as it will go:

set linesize 32767

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.