Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have an oracle database with lots of tables and views but not all have data in them.
is there a simple way of looping through the tables and creating a new table with table name as one dimension and records in another.
I don't know oracle very well but you could do something like this I think, I've written it on the fly so you might need to tweak it a bit & add the code for views
In later versions of Oracle, google suggests that this will work:
SQL select table_name, column_value cnt
from user_tables, xmltable (('count(ora:view("'||table_name||'"))'))
Otherwise something like:
//load list of tables
tables:
load *;
SQL SELECT table_name
FROM dba_tables;
LET CKNumRows=NoOfRows('tables');
FOR p=0 to $(CKNumRows) -1 // start a loop, peek is zero based so if only one row need to do 0 to 0 hence the -1
LET vTable=Peek('table_name',p,'tables'); //get the next table namefrom our table
//load the fact file
fact:
LOAD '$(vTable)' as filename, count;
SQL SELECT count(*)
FROM '$(vTable)';
Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.
I don't know oracle very well but you could do something like this I think, I've written it on the fly so you might need to tweak it a bit & add the code for views
In later versions of Oracle, google suggests that this will work:
SQL select table_name, column_value cnt
from user_tables, xmltable (('count(ora:view("'||table_name||'"))'))
Otherwise something like:
//load list of tables
tables:
load *;
SQL SELECT table_name
FROM dba_tables;
LET CKNumRows=NoOfRows('tables');
FOR p=0 to $(CKNumRows) -1 // start a loop, peek is zero based so if only one row need to do 0 to 0 hence the -1
LET vTable=Peek('table_name',p,'tables'); //get the next table namefrom our table
//load the fact file
fact:
LOAD '$(vTable)' as filename, count;
SQL SELECT count(*)
FROM '$(vTable)';
Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.