Storing SQL queries in a data table

[Welshbird]

My company are shortly going to move premises, and (hurrah! - at long last!) we are going to have a more structured plan for storing documents, queries, etc, and even a back-up plan!

I've been asked to try and store my sql queries that I run on the remote Oracle 10g server (which is in mainland Europe, and I am in the UK) within the server itself, rather than locally.

For all kinds of reasons I don't really want to only use stored procedures, not least because some of my SQL*Plus scripts output data in a specified format, and partly because I cam just too lazy to re-write everything. (Honesty being the best policy...)

So, I wondered if anyone had any suggestions. My network guru has told me I should be able to store my queries within a table and call them from the table: I'm not sure about this, both in terms of

1. Is it a good idea?
2. How on earth do I get to call them?
3. How difficult will it be to alter queries run in this way?

Any suggestions you may have would be gratefully received.

Oh, Another use will need to run these in my absence too, assuming they ever let me take some holiday grumble grumble




Fee

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

 

[carp]

You could store the files as CLOBs, and there is probably a way to run them from within the table, but it would require a trick that I don't know (the formatting, etc in the scripts just wouldn't lend itself well to dynamic runs, methinks!).
As for the "goodness" of the idea - it sounds like an effort to centralize the company's software and processes (which sounds like a pretty good idea), but it strikes me as an odd way to do it.

One of the exercises in the APEX tutorial is building a web page that allows you to upload and download files. The file contents are stored in a table. This might satisfy both your company's requirements and make the files available to you when you need them. You might want to take a look at this approach.

 

[Welshbird]

Thanks Carp - I'll look into that.

I agree - I thnk us finding a way of centralising processes is good. My only issue is that my company has traditionally sold data, and this data source that we store in Oracle is not intended to be marketed in this way: Rather this will be ad-hoc reporting which will be heavily analysed prior to being sold, so it doens't lend itself to our usual production environment.

I guess we all have some learning to do. And that does mean me as well as the company!

I wondered whether we would be better to use something like SourceSafe; and also whether there is something like that that which could be provided by Oracle.

Maybe time to 'Ask Tom' as well as ask TT!

Fee

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

 

[Thargy]

willif,

is the requirement to store the scripts on the server, or in oracle database tables.

If it's the former, why not have a drive map from your PC to the server, and just dump them in OS storage, ready to rock n roll?

Centralisation is an obviously good idea, but have your company really thought it through. If your scripts are stored on the server, are the backup scripts stored there too? If they are, then someone has boobed. Centralisation is fine, but having all your eggs in one basket is just plain dumb.

Regards

T

Grinding away at things Oracular

 

[Welshbird]

Thanks Tharg.

The server is in Paris and I have no access to a mapped drive there, nor will I ever.

I'm trying to get the company to look at a 'centralised' solution: Which to me would mean the oracle scripts are stored with the mainframe scripts, and the bits of VB and Java we use in a central code store.

You know the phrase "it's like running up the down escalator?"

Thanks for suggestions though. I think I may take them into a some sort of code-store for everything yet...

Fee

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

 

[Thargy]

willif,

That sounds much better. I think that there was a sudden rush of blood to someone's head in all of this. It has the feel of managers telling I.T. specialists what to do.

A general purpose script store seems much more sensible, as you take the database out of the way. Suppose the scripts were to be stored in tables. If there was a problem with the database, you could end up trying to run a standard recovery script, only problem is, the script is in the db needing recovery. I suggest you make it plain to the managers involved that having database management tools reliant upon the database which they manage, is absolutley barking.

Keep on taking the tablets Fee

Regards

Tharg

Grinding away at things Oracular

 

[Welshbird]

Hee. Ta Tharg. I will.

Fee

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