Solved

Load/Deploy Queries In Oracle

Posted on 2014-12-07
3
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 77

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

730 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