How to save the Oracle queries like we can save in sql database?

by manav saikia
(tezpur)

How to save the Oracle queries like we can save in sql database?


Depending on what you want, there are a few ways to save queries in an Oracle database. If you have a query that is run often and particularly if it's used in several different places in your application you could create a view or even a PL/SQL function or procedure.

A view in Oracle is literally just a stored, compiled query. This is especially useful for complex queries when you want to hide the complexity. Also if this query will be used in several places in your application, you will only have to tune it once and whenever you change the view the new query will be picked up automatically by your application.

PL/SQL procedures and functions are more suited to situations where you are doing something more than just querying the database but again when you change the queries inside a function or procedure they are automatically propagated to all callers of the procedure/function and functions have the advantage that they can be embedded in a query.

Materialized views take the concept of stored queries one step further - not only is the query stored in the database but also the results. These are especially useful for data warehouses where aggregations are often performed but the data is only changing once per day or per week or per month and so the query only needs to be run once per day/week/month. After that the results will always be the same until the next refresh of the data.

Materialized views in Oracle can also be set to be refreshed automatically when the underlying data changes. Refer to Oracle Database Concepts for an overview of materialized views.

A slightly more esoteric (and less useful) way of storing SQL queries in the database would be to save the query as a text file and then save the text file in the database as a CLOB (character large object). For more information about CLOBs see the Oracle Database SQL Language Reference manual

Up 'till now we've assumed you want to store a specific (known) query in the database for future use. But what if you want to store the queries that are run by the database? To do this you would have to query the SQL area in the SGA (the System Global Area) and spool the results to a file. You can do this quite easily and you could schedule a procedure to run periodically to do this if you so desired, however this would add an extra overhead to the database.

Alternatively you might want to record queries run by specific users or on specific tables or at certain times of the day etc. For this sort of activity Oracle provides auditing (see Oracle Database Security Guide for more details). Auditing can also be used to monitor Data Manipulation Statements (DML) on specific objects.

For more details on how (normal) views can be used in Oracle see The Role Of Views In High Performance Oracle Databases

Comments for How to save the Oracle queries like we can save in sql database?

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 13, 2015
Rating
starstarstarstarstar
Huh
by: Anonymous

Where did you actually tell us how to save the query? Did I miss something?

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.