Solved

Load/Deploy Queries In Oracle

Posted on 2014-12-07
3
235 Views
Last Modified: 2014-12-13
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.
0
Comment
Question by:Benki Canoso
3 Comments
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 40485850
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?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40485893
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'.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40486314
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Outer Query not returning data - SQL HELP 16 50
sql for Oracle views 8 47
Oracle - Stored Procedure Privilge access 3 39
Create table from select - oracle 6 19
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now