Learning is not a spectator sport

5 responses to “Quick 23ai Tip – Simple list to SQL”

1. 

   Rajeshwaran Jeyabal

   September 30, 2024 at 11:27 pm

   A bit of SQL could do that in the latest generation of databases..

   demo@FREEPDB1> variable x varchar2(2000)
   demo@FREEPDB1> begin
   2 😡 := q’# Advanced Security Administrator’s Guide
   3 Application Developer’s Guide – Advanced Queuing (AQ)
   4 Application Developer’s Guide – Fundamentals
   5 Application Developer’s Guide – Large Objects (LOBs)
   6 Application Developer’s Guide – Object-Relational Features
   7 Application Developer’s Guide – XML
   8 Applications InterConnect User’s Guide
   9 Backup and Recovery Guide
   10 Concepts
   11 CORBA Developer’s Guide and Reference
   12 Data Cartridge Developer’s Guide
   13 Data Warehousing Guide
   14 Designing and Tuning for Performance
   15 Distributed Database Systems
   16 Enterprise JavaBeans Developer’s Guide and Reference
   17 Enterprise Manager Administrator’s Guide
   18 Enterprise Manager Concepts Guide
   19 Enterprise Manager Configuration Guide
   20 Enterprise Manager Intelligent Agent User’s Guide
   21 Enterprise Manager Messages Manual
   22 Enterprise Manager SNMP Support Reference Guide
   23 Error Messages
   24 Getting to Know Oracle8i
   25 Integration Server Overview
   26 interMedia Audio, Image, and Video Java Classes User’s Guide and Reference #’;
   27 end;
   28 /

   PL/SQL procedure successfully completed.

   demo@FREEPDB1>
   demo@FREEPDB1> with rws(x) as (
   2 select case when substr(:x,-1,1) = chr(10) then 😡
   3 else 😡 || chr(10) end result_output )
   4 select t2.*
   5 from rws cross apply ( select regexp_substr( x,’.*’||chr(10),1,level,’m’) c2
   6 from dual
   7 connect by level <= regexp_count(x,chr(10)) ) t2;

   C2

   Advanced Security Administrator’s Guide
   Application Developer’s Guide – Advanced Queuing (AQ)
   Application Developer’s Guide – Fundamentals
   Application Developer’s Guide – Large Objects (LOBs)
   Application Developer’s Guide – Object-Relational Features
   Application Developer’s Guide – XML
   Applications InterConnect User’s Guide
   Backup and Recovery Guide
   Concepts
   CORBA Developer’s Guide and Reference
   Data Cartridge Developer’s Guide
   Data Warehousing Guide
   Designing and Tuning for Performance
   Distributed Database Systems
   Enterprise JavaBeans Developer’s Guide and Reference
   Enterprise Manager Administrator’s Guide
   Enterprise Manager Concepts Guide
   Enterprise Manager Configuration Guide
   Enterprise Manager Intelligent Agent User’s Guide
   Enterprise Manager Messages Manual
   Enterprise Manager SNMP Support Reference Guide
   Error Messages
   Getting to Know Oracle8i
   Integration Server Overview
   interMedia Audio, Image, and Video Java Classes User’s Guide and Reference

   25 rows selected.

   demo@FREEPDB1>

   Reply
2. 

   AndyP

   October 8, 2024 at 9:25 pm

   Or, for those like me who are a bit daunted by the new-fangled cross apply and the use of the regexp, we could use a technique popularised by a bloke called Connor something-or-other

   Populate the variable in the same way Rajeshwaran did above (maybe call it comething other than x because colon+x seems to render as an angry emoji) and then

   with data as
   (
   select instr(:list,chr(10),1,level) pos
   from dual
   connect by level <= length(:list) – nvl(length(replace(:list,chr(10),”)),0) + 1
   )
   select substr(:list,pos + 1,lead(pos,1,4000) over(order by pos) – pos – 1) subs
   from data

   Also, as someone who started at Oracle when it was version 6, it’s nice to see the “Getting to Know Oracle8i” doc getting a mention

   Reply
3. 

   AndyP

   October 8, 2024 at 9:28 pm

   Sorry about the terrible attempt at posting a comment – please feel free to fix the formatting!

   Reply
4. 

   science gecko

   September 30, 2025 at 4:04 pm

   Of course, I must add an APEX related option into the mix

   select column_value
   from apex_string.split_clobs(q’~Advanced Security Administrator’s Guide
   Application Developer’s Guide – Advanced Queuing (AQ)
   Application Developer’s Guide – Fundamentals
   Application Developer’s Guide – Large Objects (LOBs)
   Application Developer’s Guide – Object-Relational Features
   Application Developer’s Guide – XML
   Applications InterConnect User’s Guide
   Backup and Recovery Guide
   Concepts
   …~’,chr(10));

   But I would also like to ask Connor the related technology/documentation that enables the “t(c)” syntax, as I’m wondering how this might be expanded upon.

   Reply
5. 

   science gecko

   September 30, 2025 at 4:10 pm

   I failed to read my own notes further down – the table values constructor.

   with t(c, a) as (values (‘x’, 1), (‘y’, 2))

   select * from t;

   https://oracle-base.com/articles/23/table-values-constructor-23

   Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.