OraFAQ Forum: Client Tools » Nightly utility to generate table for report

Home » Other » Client Tools » Nightly utility to generate table for report (Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Nightly utility to generate table for report [message #641429]

Fri, 14 August 2015 12:18

tgatkins
Messages: 4
Registered: August 2015
Location: Huntsville

Junior Member

Hi All-

It's been almost 20 years since I administered a database, so I need a kick-start.

I'm looking to develop a simple utility to run nightly at a specified time. It will select columns from tables (I know how to write SQL) and place them in a temporary table for reports.

I have a remote ODBC connection, and am using Windows.

What is the most straightforward tool / approach for this?

Thanks,

Tim Atkins

Report message to a moderator

Re: Nightly utility to generate table for report [message #641431 is a reply to message #641429]

Fri, 14 August 2015 12:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags.

You can have a look at DBMS_SCHEDULER to schedule a job. Executing a sql script through DBMS_SCHEDULER in Windows is a bit tricky. See the following example as a start:

BEGIN  
  dbms_scheduler.create_job('MY_JOB',  
  job_action=>'C:\WINDOWS\SYSTEM32\CMD.EXE',  
  number_of_arguments=>3,  
  job_type=>'executable',  
  start_date => SYSTIMESTAMP,  
  repeat_interval => 'freq=hourly; byminute=0,30; bysecond=0;',  
  end_date => NULL,  
  enabled=> false);  
  dbms_scheduler.set_job_argument_value('MY_JOB',1,'/q');  
  dbms_scheduler.set_job_argument_value('MY_JOB',2,'/c');  
  dbms_scheduler.set_job_argument_value('MY_JOB',3,  
  'D:\SCRIPTS\my_sql.bat');  
  dbms_scheduler.enable('MY_JOB');  
END;  
/

Now your my_sql.bat would look like:

sqlplus user@sid/password @D:\scripts\script.sql  
exit

Regards,
Lalit

Report message to a moderator

Re: Nightly utility to generate table for report [message #641433 is a reply to message #641429]

Fri, 14 August 2015 13:57

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I think, in your case, as your purpose is only reporting, you should use Excel with ODBC, OLEDB or OO4O and VBA if you need something more complex than pure SQL.

You can find an example of such Excel worksheet of what you can do in this topic "Oracle User Management" (not reporting but an Excel sheet to manage account).

Report message to a moderator

Re: Nightly utility to generate table for report [message #641452 is a reply to message #641433]

Sat, 15 August 2015 02:29

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yet another option is to create an Apex (Application Express) application for the reporting purposes. As you already know how to use SQL, here's a short walkthrough:

download and install Oracle 11g Express Edition database. It already contains Apex 4.0 (unless I'm wrong); the most recent version is 5.0, but it doesn't really matter in this case. The above link contains version 5.0 so - if you want to bother, go on and install it AFTER you install 11g XE
Both 11g XE and Apex are fre to use, even in production. You don't have to pay anything for any of those. Of course, there are some restrictions but I presume that - currently - they aren't significant.
Apex is a GUI tool, used via internet browser, and lets you create a nice application in a matter of a few clicks. The Apex link also contains documentation. True, you'll need some time to create a workspace, developer (as an Apex "user") but it is all described in there. If you need assistance, say so.
In Apex, you can create reports, where its "Interactive Reports" offer quite a lot of flexibility, even for end users.
Furthermore, you can create graphs in a matter of a few clicks along with a simple SQL SELECT statement
As it is Oracle, there will be no problem of connecting your XE with the database that contains data. If you forgot, it is the database link that you'll need
It means that perhaps you don't even need a "job" which will populate some "temporary tables" (as your initial message suggests) - Apex can fetch those data directly.

That's - more or less - all I meant to say. Although it might sound complicated, it is a rather interesting idea. Don't just discard it, thing about Apex a minute or two.

Report message to a moderator

Re: Nightly utility to generate table for report [message #641463 is a reply to message #641429]

Sat, 15 August 2015 09:28

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Another approach, if you decide you really do need a 'reporting table', is a materialized view. Unlike a normal view, which is just a pre-compiled SELECT statement, a materialized view is actually a table (consumes storage to hold actual data) but it is defined like a view in that the definition contains a SELECT statement that can be as simple or complex as needed, and the defintion also includes rules on how and when to refresh the 'view'. This approach gets you out of the business of job scheduling and writing batch/shell and sql scripts. And it is really the thing that was designed specifically for what it appears you want to do.

One other approach is to challenge your fundamental assumption.

Why do you think you need to populate a table for reporting purposes? I'm assuming the data is already in the database and you are talking about simply copying it to your 'reporting' table. Why not simply access the data from its source at the time you generate the report.

Mind you, there may very well be valid reasons for copying the data to a 'reporting' table. Performance is usually the case, if gathering up the data from its source is a very expensive and long process. And that's where the materialized view comes into play. But the preferred approach is always to get data from its source at the time it is needed.

--
So what database platforms did you work on 20 years ago, and what have you been doing since?

Report message to a moderator

Re: Nightly utility to generate table for report [message #641540 is a reply to message #641429]

Mon, 17 August 2015 10:59

tgatkins
Messages: 4
Registered: August 2015
Location: Huntsville

Junior Member

Hi all-

Thanks to each of you for your prompt insights.

Ed, I developed and managed Plant Engineering Data Management solutions (SmartPlant) and GIS on Oracle a while back. Now, I'm an engineering consultant to NASA, developing the next heavy lift rocket: www.nasa.gov/sls (integrating avionics and software to fly the machine). I never administered a DB, but developed several tools for DBAs and end users.

This DBA support task is a separate effort for a utility company.

Thanks again,

Tim Atkins

Report message to a moderator

Re: Nightly utility to generate table for report [message #641881 is a reply to message #641540]

Wed, 26 August 2015 12:29

tgatkins
Messages: 4
Registered: August 2015
Location: Huntsville

Junior Member

OK, back from vacation so now I can start.

I found out that I'll be pulling data from an Informix DB and populating a predefined Oracle table (to support a dispatching app). Any pointers on setting up an Informix connection in a stored procedure, or otherwise?

Thanks again.

Tim Atkins

Report message to a moderator

Re: Nightly utility to generate table for report [message #641882 is a reply to message #641881]

Wed, 26 August 2015 12:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

tgatkins wrote on Wed, 26 August 2015 10:29

OK, back from vacation so now I can start.

I found out that I'll be pulling data from an Informix DB and populating a predefined Oracle table (to support a dispatching app). Any pointers on setting up an Informix connection in a stored procedure, or otherwise?

Thanks again.

Tim Atkins

Learn to use GOOGLE yourself

https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=connect+oracle+to+informix

Report message to a moderator

Re: Nightly utility to generate table for report [message #641883 is a reply to message #641881]

Wed, 26 August 2015 14:15

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

As BlackSwan hinted, you probably want to use the Oracle Transparent Gateway for ODBC. You install the gateway on any server and configure it along with an ODBC driver to point to your 'non-oracle' database. Next, in your oracle database, you create a database link to point to the reference in the gateway. Let's say you name that database link 'informix'. Then you can simply 'insert into report_table values (select ... from some_table@informix);

The setup could be done by an experienced person in about an hour if there were no unexpected roadblocks, but if you are not familiar with db links and installing/configuring the gateway it could take a few days to figure out how to put the pieces together.

There is a link to a very helpful Gateway configuration utility on MOS at Doc ID 1351616.1

Report message to a moderator

Re: Nightly utility to generate table for report [message #641984 is a reply to message #641883]

Fri, 28 August 2015 13:14

tgatkins
Messages: 4
Registered: August 2015
Location: Huntsville

Junior Member

Thanks, Ed!

Report message to a moderator

Re: Nightly utility to generate table for report [message #641994 is a reply to message #641984]

Fri, 28 August 2015 14:52

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

tgatkins wrote on Fri, 28 August 2015 13:14

Thanks, Ed!

BTW, some more thoughts on the OTG subject.

As I said, the gateway can be installed on any server. It is just a switchboard. I have multiple oracle databases on multiple servers needing gateway services to connect to multiple non-oracle databases on multiple servers. So I really preferred that the GW be on a 'neutral' site. Since we are in all cases using the gateway for ODBC (rather than any of the db-specific gateways) it made sense to put it on a Windows server where ODBC was already configured. I chose to put it on the server that hosts most of our prod MSSQL databases, but again, that was for convenience, not any techinical requirement.

Second, you need to put some thought into what you name your db links when you create them in a specific oracle database. Suppose the target database was name sybprod (sybase, production) You might be tempted to name the link 'sybprod'. But if you were to refresh a test database with a backup of this, that test database would have the db link 'sybprod'. Is that really what you want? No, I prefer to give the db links a more generic name, specific enough to identify the db, but not committing to 'prod' vs 'test' (vs. 'dev', etc). That way the link and any code the references it remains environment neutral.

Third, and a continuation of the last point, when you create the db link you have a choice of hard-coding the entire address of the target db, or simply referencing a net service name (tnsnames.ora entry). For the same reasons mentioned above, it can be a pain to have that info replicated to another (test) database, carrying with it a 'commitment' to connect to a specific (production) database. To that end, I choose to reference a net service name that, in itself is not 'commiting' to prod/test/etc, and let that final resolution/commitment be made in the tnsnames.ora file. That way code/names/references inside the database (and thus replicated when the database is used as a base for refreshing other databases) remain 'neutral' and get resolved by a configuration file (tnsnames.ora) on whatever server the db is running on.

Of course, the above won't work if you have your prod and test databases running out of the same ORACL_HOME on the same server.

Report message to a moderator

Previous Topic:	ERD on views
Next Topic:	CE_RECONCILED_TRANSACTIONS_V returns no rows in R12

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 23:00:55 CST 2024