topic Re: How to load all tables in a database in oracle? in QlikView

How to load all tables in a database in oracle?

Thu, 23 Apr 2015 23:02:57 GMT

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

Thank you.

Re: How to load all tables in a database in oracle?

maxgro — Thu, 23 Apr 2015 23:23:48 GMT

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_...

Re: How to load all tables in a database in oracle?

Fri, 24 Apr 2015 15:48:52 GMT

Thanks for your answer,

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

Re: How to load all tables in a database in oracle?

buzzy996 — Fri, 24 Apr 2015 16:05:43 GMT

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

Re: How to load all tables in a database in oracle?

maxgro — Fri, 24 Apr 2015 16:13:14 GMT

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;

Re: How to load all tables in a database in oracle?

maxgro — Fri, 24 Apr 2015 17:01:19 GMT

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.

Re: How to load all tables in a database in oracle?

Dmitry-Vereschagin — Wed, 05 Aug 2020 18:30:02 GMT

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'), ' ', '_'); ...