'Productionised' queries for non-Oracle bodies

[Welshbird]

I'd appreciate any advice from anyone here as to the best way to 'productionise' queries that are run from our Oracle 10g server.

When I currently produce data for clients I have set queries that I run in which I simply change the date ranges.

I have to plan for a 5 week absence (hurrah!) and so need someone else to run these whilst I am away. The company is not all that keen on training someone else to do this (which would be my preferred option - gaining knowledge or understanding must be good!) and instead think it would be best if I could build some sort of 'front end' to do this.

I could presumably set up some way that the data range could be entered (in a similar way as a substitution variable in an SQL*Plus script), although apparently we son't want people to use SQL*Plus (grrr) but I have no idea even where to start if I first need to run a query to find the codes which will be used in the major 'select query'.

(To be clear, we are talking about medical data here, so often the first step is to check the drug table to find which products are relevant then to refine this list and use it as the here clause in the main query).

I know I'm asking a big question here, but any suggestions as to on-line resources that might be helpful, or any platform choices that would be good would be really appreciated.

Oh, we are running Oracle 10g on a Linux box on a different continent (no really) and I cannot alter the set up of this box in any way..

Answers on a postcode!

Thanks chaps.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LKBrwnDBA]

From what environment/how are you connecting?

You could create scripts and use "scheduled" jobs or "crontab" jobs to execute these scripts which would be connecting through sqlplus to the remote DB.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Welshbird]

I only have 'select' granted to me on the major tables and I cannot access anything else on the server. I connect via SQL*Plus, but that apparently doesn't have enough 'pictures' for my team!

What they really want me to end up with is a screen with a load of buttons they can push, and data comes out the end.

Doh!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

So, Fee, are you saying that the "No-SQL*Plus" restriction results from your users being "hooked" on buttons and they are too afraid to use the keyboard? If this is the case, then perhaps someone needs to have a "Come to Jesus" visit with them.

There are probably plenty of GUI-development packages out there that can entertain your "child" co-workers with pretty colours, fancy-shaped buttons, and even animation to dazzle them <grin>. But if you are simply looking for the quickest, easiest method of allowing them to enter data to run queries while you are out of the office, then certainly we can put together some (fancy looking) SQL*Plus scripts that use "ACCEPT...PROMPT" commands for them to enter data.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[LKBrwnDBA]

-- Or --
You could also build a simple M$ Access front-end interface.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Welshbird]

Thanks Chaps.

I'm going to attempt to get people to run some scripts and see ifthey can do that without looking like rabbits in the headlights.

*sigh*

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Thargy]

Willif,

I have to say that running these scripts with suitable prompts should be a priority for at least two reasons.

First, if you're going to do a 'transat' then you've got to sort it anyway.

Second, your organisation should not be dependent on one specialist's knowledge for successful operation. This should have been scripted and documented long ago. If your co-workers can't even respond to on screen prompts from a CLI, then I have to wonder about their competence. Also, by doing it now, you can train your colleagues to do the task (and take load off yourself for evermore) and also find any bugs in your scripts.

I have found that once GUI Geeks are introduced to the sheer power of scripts, they tend to become keen advocates, once they realise how much time they save.

I use windows bat files to offer on-screen prompts to users, and then have the bat file invoke sql plus with the correct parameters "behind the scenes". My production scripts produce logs, confirming success or failure, which are automatically opened with notepad once the bat file has finished.

I'll gladly send you a sample if you're interested, but I confess to knowing nothing of Linux.

T

Grinding away at things Oracular

 

[Welshbird]

I use scripts myself and I agree - best plan in the world...

What I would hope for is for some backup for myself in my office. Everything I have done is documented and I believe could be followed in my absence, but the powers that be seem to want the idea of a 'button' to just push.

So, I thought I would test it out, so have signed up for a java course (doing ok so far!) and I'll give it a go. I agree though, the more worrying thing is that the is no resource to take over from me should something happen (either planned or not!).

So, learning Java is not too bad an idea I guess. And the scripts are in place for people to play with as well.

(I'll sit back and wait for the fireworks!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[LKBrwnDBA]

You should consider using Oracle Discoverer.

You can setup (store) parametrized queries and export the result to any format (html, xml, excel, pdf, csv, text...)





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Welshbird]

Thanks - I'll look into that.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]