Home » SQL & PL/SQL » SQL & PL/SQL » Polymorphic Table Function Issue (Oracle, 19.2, Linux)
| Polymorphic Table Function Issue [message #690473] |
Wed, 08 April 2026 11:14  |
Duane
Messages: 591
Registered: December 2002
|
Senior Member |
|
|
I have a working example but I need some clarification on a few items I'm not understanding.
ISSUE: I want "column1" to be part of the select statement but not the row being parsed. I want the first parsed element of the data.
SOLUTION: "column1" should contain the value of "one" and not "one, two, three, four, five". All other columns should contain the correct value.
"column1" should contain the value of "six" and not "six, seven, eight, nine, ten". All other columns should contain the correct value.
Row 1
column1 = one
column2 = two
column3 = three
column4 = four
column5 = five
Row 2
column1 = six
column2 = seven
column3 = eight
column4 = nine
column5 = ten
Package Spec
create or replace package CSVData as
function Describe (TableName in out dbms_tf.table_t,
ColumnNumber in number default 100,
Delimiter in varchar2 default ',') return dbms_tf.describe_t;
procedure fetch_rows (ColumnNumber in number default 100,
Delimiter in varchar2 default ',');
end CSVData;
create or replace package body CSVData as
function Describe (TableName in out dbms_tf.table_t,
ColumnNumber in number default 100,
Delimiter in varchar2 default ',') return dbms_tf.describe_t as
ColumnNew dbms_tf.columns_new_t;
begin
TableName.column(1).pass_through := false;
TableName.column(1).for_read := true;
for i in 1..ColumnNumber
loop
ColumnNew(i) := dbms_tf.column_metadata_t(name => 'column'||i, type => dbms_tf.type_varchar2);
end loop;
return dbms_tf.describe_t(new_columns => ColumnNew);
end;
procedure fetch_rows (ColumnNumber in number default 100,
Delimiter in varchar2 default ',') as
RowCount pls_integer;
RowSet dbms_tf.row_set_t;
begin
dbms_tf.get_row_set(RowSet, RowCount);
for i in 1..RowCount
loop
for j in 2..ColumnNumber
loop
RowSet(j).tab_varchar2(i) := regexp_substr(RowSet(1).tab_varchar2(i), '[^'||Delimiter||']+', 1, j - 1);
end loop;
end loop;
dbms_tf.put_row_set(RowSet);
end;
end CSVData;
create or replace function CSVColumn(TableName in table,
ColumnNumber in number default 100,
Delimiter in varchar2 default ',') return table pipelined row polymorphic using CSVData;
with
csvs as (
select 'one, two, three, four, five' str from dual
union
select 'six, seven, eight, nine, ten' str from dual
)
select *
from CSVColumn(csvs, 10);
for i in 1..RowCount
loop
for j in 2..ColumnNumber
loop
RowSet(j).tab_varchar2(i) := regexp_substr(RowSet(1).tab_varchar2(i), '[^'||Delimiter||']+', 1, j - 1);
end loop;
end loop;
If I replace 2 with 1 and remove -1 from J then I get column1 = "one" or column1 = "six" but all the other columns are null (for j in 1..ColumnNumber j ).
for i in 1..RowCount
loop
for j in 1..ColumnNumber
loop
RowSet(j).tab_varchar2(i) := regexp_substr(RowSet(1).tab_varchar2(i), '[^'||Delimiter||']+', 1, j);
end loop;
end loop;
[Updated on: Wed, 08 April 2026 11:18] Report message to a moderator |
|
|
|
| Re: Polymorphic Table Function Issue [message #690474 is a reply to message #690473] |
Thu, 09 April 2026 10:56  |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
create or replace package body CSVData as
function Describe (TableName in out dbms_tf.table_t,
ColumnNumber in number default 100,
Delimiter in varchar2 default ',') return dbms_tf.describe_t as
ColumnNew dbms_tf.columns_new_t;
begin
TableName.column(1).pass_through := false;
TableName.column(1).for_read := true;
for i in 1..ColumnNumber
loop
ColumnNew(i) := dbms_tf.column_metadata_t(name => 'column'||i, type => dbms_tf.type_varchar2);
end loop;
return dbms_tf.describe_t(new_columns => ColumnNew);
end;
procedure fetch_rows (ColumnNumber in number default 100,
Delimiter in varchar2 default ',') as
RowCount pls_integer;
RowSet dbms_tf.row_set_t;
OriginalValue varchar2(4000);
begin
dbms_tf.get_row_set(RowSet, RowCount);
for i in 1..RowCount
loop
OriginalValue := RowSet(1).tab_varchar2(i);
for j in 1..ColumnNumber
loop
RowSet(j).tab_varchar2(i) := regexp_substr(OriginalValue, '[^'||Delimiter||']+', 1, j);
end loop;
end loop;
dbms_tf.put_row_set(RowSet);
end;
end CSVData;
/
SQL> create or replace package body CSVData as
2 function Describe (TableName in out dbms_tf.table_t,
3 ColumnNumber in number default 100,
4 Delimiter in varchar2 default ',') return dbms_tf.describe_t as
5 ColumnNew dbms_tf.columns_new_t;
6 begin
7 TableName.column(1).pass_through := false;
8 TableName.column(1).for_read := true;
9 for i in 1..ColumnNumber
10 loop
11 ColumnNew(i) := dbms_tf.column_metadata_t(name => 'column'||i, type => dbms_tf.type_varchar2);
12 end loop;
13 return dbms_tf.describe_t(new_columns => ColumnNew);
14 end;
15 procedure fetch_rows (ColumnNumber in number default 100,
16 Delimiter in varchar2 default ',') as
17 RowCount pls_integer;
18 RowSet dbms_tf.row_set_t;
19 OriginalValue varchar2(4000);
20 begin
21 dbms_tf.get_row_set(RowSet, RowCount);
22 for i in 1..RowCount
23 loop
24 OriginalValue := RowSet(1).tab_varchar2(i);
25 for j in 1..ColumnNumber
26 loop
27 RowSet(j).tab_varchar2(i) := regexp_substr(OriginalValue, '[^'||Delimiter||']+', 1, j);
28 end loop;
29 end loop;
30 dbms_tf.put_row_set(RowSet);
31 end;
32 end CSVData;
33 /
Package body created.
SQL> with
2 csvs as (
3 select 'one, two, three, four, five' str from dual
4 union
5 select 'six, seven, eight, nine, ten' str from dual
6 )
7 select *
8 from CSVColumn(csvs, 10);
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8 COLUMN9 COLUMN10
------- ------- ------- ------- ------- ------- ------- ------- ------- --------
one two three four five
six seven eight nine ten
SQL>
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690475 is a reply to message #690474] |
Thu, 09 April 2026 19:30 |
Duane
Messages: 591
Registered: December 2002
|
Senior Member |
|
|
I thought for sure I had responded to this post. Must have not hit the post button.
Anyway, thanks for clearing up my issue. I had something very similar to what you posted but I never could get it to work like I wanted. Doesn't matter now as you fixed it.
Thanks again. I was wondering if you were going to take a look at what I was trying to accomplish.
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690476 is a reply to message #690475] |
Fri, 10 April 2026 06:29 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Actually, there is no need for Original Value. We just need to assign COLUMN1 last:
create or replace package body CSVData as
function Describe (TableName in out dbms_tf.table_t,
ColumnNumber in number default 100,
Delimiter in varchar2 default ',') return dbms_tf.describe_t as
ColumnNew dbms_tf.columns_new_t;
begin
TableName.column(1).pass_through := false;
TableName.column(1).for_read := true;
for i in 1..ColumnNumber
loop
ColumnNew(i) := dbms_tf.column_metadata_t(name => 'column'||i, type => dbms_tf.type_varchar2);
end loop;
return dbms_tf.describe_t(new_columns => ColumnNew);
end;
procedure fetch_rows (ColumnNumber in number default 100,
Delimiter in varchar2 default ',') as
RowCount pls_integer;
RowSet dbms_tf.row_set_t;
begin
dbms_tf.get_row_set(RowSet, RowCount);
for i in 1..RowCount
loop
for j in 2..ColumnNumber
loop
RowSet(j).tab_varchar2(i) := regexp_substr(RowSet(1).tab_varchar2(i), '[^'||Delimiter||']+', 1, j);
end loop;
RowSet(1).tab_varchar2(i) := regexp_substr(RowSet(1).tab_varchar2(i), '[^'||Delimiter||']+');
end loop;
dbms_tf.put_row_set(RowSet);
end;
end CSVData;
/
Package body created.
SQL> with
2 csvs as (
3 select 'one, two, three, four, five' str from dual
4 union
5 select 'six, seven, eight, nine, ten' str from dual
6 )
7 select *
8 from CSVColumn(csvs, 10);
COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8 COLUMN9 COLUMN10
-------- -------- -------- -------- -------- -------- -------- -------- -------- --------
one two three four five
six seven eight nine ten
SQL>
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690477 is a reply to message #690476] |
Fri, 10 April 2026 09:35 |
Duane
Messages: 591
Registered: December 2002
|
Senior Member |
|
|
Ah, so that's why I never could get this to return correctly. I kept getting my delimited data as a column.
By chance, do you know of a regular expression that will parse data correctly even when that data contains a delimiter with the text?
What I have if some columns are null:
Not based on your updated code.
RowSet(j).tab_varchar2(i) := regexp_substr(DelmitedData, '([^'||Delimiter||']*)('||Delimiter||'|$)', 1, j, null, 1);
with
csvs as (
select 'one, two, three,, four, five' str from dual
union
select 'six, seven, eight, x, nine, ten' str from dual
)
select *
from CSVColumn(csvs, 10);
I did find this regular expression but it returns all nulls
,(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)
I'm trying to get this to work with a delimiter within text without it breaking on the delimiter
with
csvs as (
select '"one", "two", "three", "this has a comma, in the text", "four", "five"' str from dual
union
select '"six", "seven", "eight", "x", "nine", "ten"' str from dual
)
select *
from CSVColumn(csvs, 10);
"one" "two" "three" "this has a comma in the text" "four" "five"
"six" "seven" "eight" "x" "nine" "ten"
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690478 is a reply to message #690477] |
Fri, 10 April 2026 15:11 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t as (select 'one, two, three,, four, five' str from dual)
select rownum,
regexp_substr(str,'([^,]*)(,|$)',1,level,null,1) x
from t
connect by level <= regexp_count(str,',') + 1
/
ROWNUM X
---------- ----------
1 one
2 two
3 three
4
5 four
6 five
6 rows selected.
SQL>
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690479 is a reply to message #690478] |
Fri, 10 April 2026 15:47 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And if you want to get rid of leading space:
with t as (select 'one, two, three,, four, five' str from dual)
select rownum,
regexp_substr(str,' ?([^,]*)(,|$)',1,level,null,1) x
from t
connect by level <= regexp_count(str,',') + 1
/
ROWNUM X
---------- ----------
1 one
2 two
3 three
4
5 four
6 five
6 rows selected.
SQL>
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690480 is a reply to message #690479] |
Fri, 10 April 2026 17:15 |
Duane
Messages: 591
Registered: December 2002
|
Senior Member |
|
|
I'm trying to parse something with an embedded comma.
As such:
[code]
with t as (select '"one", "two", "three","some comma, in the middle", "four", "five"' str from dual)
select rownum,
regexp_substr(str,' ?([^,]*)(,|$)',1,level,null,1) x
from t
connect by level <= regexp_count(str,',') + 1
Output: Should only be six lines of output and not seven. This text should be within one column "some comma, in the middle" and not broken into two columns.
"one"
"two"
"three"
"some comma
in the middle"
"four"
"five"
I’m trying to anticipate someone sending me data that has a delimiter within the data.
Like this:
Data:
“Account Number”, “Total”, “Full Name”, “Balance”
1234, 10000, “Presley, Elivs”, 20000
5678, 1000, “Dean, James”, 10000
9102, 5000, “Monroe, Marilyn”, 50000
Output:
column1 column2 column3 column4
1234 10000 Presley, Elivs 20000
5678 1000 Dean, James 10000
9102 5000 Monroe, Marilyn 50000
[Updated on: Fri, 10 April 2026 17:34] Report message to a moderator |
|
|
|
| Re: Polymorphic Table Function Issue [message #690481 is a reply to message #690480] |
Sat, 11 April 2026 05:47 |
Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t as (select '"one", "two", "three","some comma, in the middle",, "four","", "five"' str from dual)
select rownum,
regexp_substr(str,' ?(("[^"]*")|([^,]*))(,|$)',1,level,null,1) x
from t
connect by level <= regexp_count(regexp_replace(str,'"[^"]*"'),',') + 1
/
ROWNUM X
---------- ----------------------------------------
1 "one"
2 "two"
3 "three"
4 "some comma, in the middle"
5
6 "four"
7 ""
8 "five"
8 rows selected.
SQL>
with data as (
select 1 rn,'"Account Number", "Total", "Full Name", "Balance"' str from dual union all
select 2,'1234, 10000, "Presley, Elivs", 20000' from dual union all
select 3,'5678, 1000, "Dean, James", 10000' from dual union all
select 4,'9102, 5000, "Monroe, Marilyn", 50000' from dual
)
select regexp_substr(str,' ?(("[^"]*")|([^,]*))(,|$)',1,1,null,1) column1,
regexp_substr(str,' ?(("[^"]*")|([^,]*))(,|$)',1,2,null,1) column2,
regexp_substr(str,' ?(("[^"]*")|([^,]*))(,|$)',1,3,null,1) column3,
regexp_substr(str,' ?(("[^"]*")|([^,]*))(,|$)',1,4,null,1) column4
from data
where rn > 1
order by rn
/
COLUMN1 COLUMN2 COLUMN3 COLUMN4
-------------------- -------------------- -------------------- --------------------
1234 10000 "Presley, Elivs" 20000
5678 1000 "Dean, James" 10000
9102 5000 "Monroe, Marilyn" 50000
SQL>
|
|
|
|
| Re: Polymorphic Table Function Issue [message #690482 is a reply to message #690481] |
Sat, 11 April 2026 11:20 |
Duane
Messages: 591
Registered: December 2002
|
Senior Member |
|
|
Thank you.
I don't need quotations around the data in Column3 so I just used an Oracle replace function to remove them. I tried doing it in the regular expression but couldn't figure out what I needed to remove.
Thanks again for your help.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 16 20:37:34 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
