(Image by Andrew Martin from Pixabay – modifications by Sam)
When you work with SQL, you usually want to have data that is nicely arranged into columns and rows. That’s where SQL shines and where it can feel like Magic.
However, sometimes we have data that is not split into columns and rows, for example when we deal with some legacy code input or call dbms_metadata.get_ddl on a table.
(Yes, we can select most of the information
dbms_metadata provides by querying the data dictionary, but there are some edge cases where we can’t, for example whether an identity column is
GENERATED ALWAYS or
GENERATED BY DEFAULT)
Thanks to recursive queries, we can solve nearly every programming problem in pure SQL, so let’s implement a split into rows by delimiter query.
Our input is the result of
dbms_metadata.get_ddl for a table that holds information about Wookies:
create table wookies ( id int generated always as identity, name varchar2(200), height number(5,2) ); select dbms_metadata.get_ddl('TABLE', 'WOOKIES') from dual; /* Output: CREATE TABLE "SITHDB"."WOOKIES" ( "ID" NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE, "NAME" VARCHAR2(200), "HEIGHT" NUMBER(5,2) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING TABLESPACE "USERS" */
Wouldn’t it be great if we could get that DDL output in separate lines, so we can easily query for different columns?
with /* Let's have a config-subquery so we can easily change the delimiter if we want */ config as ( select chr(10) as delimiter from dual ), /* This is our input, the DDL of the WOOKIES table Of course, this could be any other input */ ddl as ( select dbms_metadata.get_ddl('TABLE', 'WOOKIES') as text from dual ), /* Here is where the magic happens: a recursive query */ lines(start_pos, end_pos, line, line_no, text) as ( /* We start with the first line, which also is our starting point and anchor for the recursion */ select -- Start position inside the text is always 1 1 as start_pos, -- End position: first occurence of delimiter instr(text, config.delimiter) as end_pos, -- Line: all text between start and end position substr(text, 1, instr(text, config.delimiter)-1) as line, -- Line-Number: obvious 1 as line_no, -- We pass the text along so we don't need to -- select it every time from the DDL query text from ddl, config /* Now the recursion starts, defining all fruther lines. What we do here is pretty much the same as before, it's just more text, because we need to give some additional boundaries, like starting point for INSTR search */ union all select prev.end_pos+1 as start_pos, instr(prev.text, config.delimiter, prev.end_pos+1) as end_pos, substr(prev.text, prev.end_pos+1, instr(prev.text, config.delimiter, prev.end_pos+1)-1) as line, prev.line_no+1, prev.text from lines prev, config -- We add lines as long as there are more delimiters where instr(prev.text, config.delimiter, prev.end_pos+1) > 0 ) select line_no, line from lines;
Of course, you can do this with any text and any delimiter (if you have multi-char delimiters, you will need to replace the +1/-1 with +length(delimiter) etc.)
You can try it out on Oracle LiveSQL and find the whole example as always on my repository.