Skip to content

Instantly share code, notes, and snippets.

@joesavak

joesavak/gist:649730

Created Oct 27, 2010
Embed
What would you like to do?
get delimited row
create or replace function getDelimRow(i_table_name varchar2, i_delim varchar2, i_rowNumber number) return varchar2 is
Result varchar2(2000); --delimited string that will be returned
execSql varchar2(2000); --sql to execute to get delimited string
l_colName varchar2(2000); --column name from user_tab_cols for the table passed in
concatCols varchar2(2000); --all cols concated together to be part of the execsql var.
cursor l_cur is
select column_name
from user_tab_cols
where table_name = upper(i_table_name)
order by column_name; --i know, cursors are bad. Got a suggestion?
begin
execSql := 'select ';
open l_cur;
loop
--create the concatCols by looping through all cols returned user_tab_cols - include the delim
fetch l_cur into l_colName;
exit when L_CUR%NOTFOUND;
concatCols := concatCols || l_colName || ' || ''' || i_delim || ''' || ';
END LOOP;
--take away that last delim
concatCols := substr(concatCols,0,length(concatCols) - length(i_delim) - 9) || ' catData';
--create the full select
execSQL := execSQL || ' catData from ( select rownum rownumber,' || concatCols || ' from ' || i_table_name || ' where rownum <= ' || i_rowNumber || ') where rownumber = ' || i_rowNumber;
execute immediate execSQL into Result;
return(Result);
end getDelimRow;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment