Load/Deploy Queries In Oracle

I want to get more familiar with the different ways to load/deploy a query into Oracle(10) on the database server so that it can be utilized by the different machine clients. I use sql developer to develop and test my queries.
Benki CanosoAsked:
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I don't like storing straight SQL inside the database in 'native' format.

Typically I see people storing the select in a table then using something like 'execute immediate' or selecting it and running in inside some 3GL language as a string.

I am against that approach since there is no way to track dependencies and it will only lead to problems.

What I suggest is creating a stored procedure that accepts parameters and returns a known data type like a cursor or XML in the form of a CLOB.

Then the SQL can remain as an actual select and dependencies remain intact as long as you stay away from 'execute immediate'.
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
Benki, your question is pretty generic, and thus vague -- kind of like asking a search engine to retrieve all matches on "economics".  Taking the statement literally, means to me that the SQL data manipulation statement is meant to be shared -- such as SELECT SYSDATE FROM dual;.  If so, common sense suggests the SQL statement must be stored in a common, readable directory.

There must be an execution engine available to the user running the query, be it SQLPlus SQLForms, SQLReports, APEX, or some GUI tool such as SQLDeveloper.

The statement may be viewed interactively, and also sent to one or more destinations -- email attachment, O/S text file, and formatted for CSV, HTML, XML, whatever.  Traditionally the SPOOL output is written to an O/S directory such as /tmp or \TEMP.

A statement or set of SQL statements may parsed into a batch file, or for more advanced use, parsed with PL/SQL into a package or procedure.

So, are you concerned with the shared statement or the shared output?
Geert GruwezOracle dbaCommented:
how are the machine clients executing the sql ?
from an sql stored inside an exe ?

if the exe needs to use a different sql, it will probably need to be recompiled with that different sql
All Courses

From novice to tech pro — start learning today.