Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Load/Deploy Queries In Oracle

Posted on 2014-12-07
3
Medium Priority
?
251 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 1000 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 1000 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 38

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

597 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