Solved

Load/Deploy Queries In Oracle

Posted on 2014-12-07
3
232 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 36

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
percentage remaining 6 40
Value of 0's not appearing. 9 50
null value 15 69
Oracle -- identify blocking session 24 22
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

746 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

12 Experts available now in Live!

Get 1:1 Help Now