Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to load all tables in a database in oracle?

Hello, I wonder if there is a possibility to load all tables by owner, showing the names of fields in tables.

Thank you.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

in the link I posted there is a table

"INFORMATION_SCHEMA".COLUMNS

with a fields containing the name of the tables

The link is for sql server. In an Oracle database you have to replace INFORMATION_SCHEMA with all_tables because in Oracle table all_tables you can find all the tables you can view in the Oracle database


the script is, I have just tried now on Oracle



ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx);

OracleTableList:

SQL

select TABLE_NAME, OWNER from all_tables

where owner='DWHCONT';     // change the owner/schema

Let vTableCount = NoOfRows('OracleTableList');

For i = 0 To $(vTableCount) -1

     LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');

     LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');

     $(vMyTableNameQVD):

     SQL SELECT * FROM $(vMyTableName) ;

     STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

     DROP Table $(vMyTableNameQVD);

Next i;


View solution in original post

6 Replies
maxgro
MVP
MVP

yes

this is for sql server

Load all tables in database dynamically

for oracle replace the "table of table" table with all_tables or dba_tables and filter by owner

if you want to make the select statement wth the fields of every table, use all_tab_columns or dba_...

Not applicable
Author

Thanks for your answer,

i have a question, what is the "table of table" table? Sorry i'm new in Qlikview world.

buzzy996
Master II
Master II

if i'm not wrong,that's oracle syntax to fetch all table names.

maxgro
MVP
MVP

in the link I posted there is a table

"INFORMATION_SCHEMA".COLUMNS

with a fields containing the name of the tables

The link is for sql server. In an Oracle database you have to replace INFORMATION_SCHEMA with all_tables because in Oracle table all_tables you can find all the tables you can view in the Oracle database


the script is, I have just tried now on Oracle



ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx);

OracleTableList:

SQL

select TABLE_NAME, OWNER from all_tables

where owner='DWHCONT';     // change the owner/schema

Let vTableCount = NoOfRows('OracleTableList');

For i = 0 To $(vTableCount) -1

     LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');

     LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');

     $(vMyTableNameQVD):

     SQL SELECT * FROM $(vMyTableName) ;

     STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

     DROP Table $(vMyTableNameQVD);

Next i;


View solution in original post

maxgro
MVP
MVP

another one is SQLTABLES, this is from Qlikview help

The sqltables statement returns a set of fields describing the tables of an ODBC data source, to which a connect has been made. These fields can be combined with the fields generated by the SQLColumns and SQLTypes commands in order to give a good overview of a given database. The five standard fields are:

TABLE_QUALIFIER

TABLE_OWNER

TABLE_NAME

TABLE_TYPE

REMARKS

For a detailed description of these fields, see an ODBC reference handbook.

The syntax is:

sqltables

Example:

connect to 'MS Access 7.0 Database; DBQ=C:\Course3\DataSrc\QWT.mbd';

sqltables;

Note!
Some ODBC drivers may not support this command.
Some ODBC drivers may produce additional fields.

Dmitry-Vereschagin
Contributor
Contributor

It might be a better way to use user_tables instead of all_tables Oracle system view.
Then the procedure looks like 

Let owner='DWHCONT';
SQL
select TABLE_NAME from user_tables;
Let vTableCount = NoOfRows('OracleTableList');
For i = 0 To $(vTableCount) -1
     LET vMyTableName = '$(owner)' & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
     LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');
...