Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Course Of The Month
4 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Procedure to run existing SQL queries
Posted on 2012-12-20
Hi All,
I need your advice. We have a number of SQL queries that consist of select statement, case statement, NVLs, left and inner joins, where clauses, parameters and so on. These queries extract thousand of rows, well up to 300,000 rows then the data is save in CSV files.
I was thinking of running these SQL queries from Oracle procedures that gets trigger by someone else’s procedure. Is it possible to create Oracle procedures to execute existing SQL queries without recreating a procedure from scratch? Unfortunately we don’t have time or resources or justification to PM to spend time recreating existing SQL queries.
And just another question while I am at it, are procedures the best approach to accomplish the mentioned task?
Many Thanks.
I need your advice. We have a number of SQL queries that consist of select statement, case statement, NVLs, left and inner joins, where clauses, parameters and so on. These queries extract thousand of rows, well up to 300,000 rows then the data is save in CSV files.
I was thinking of running these SQL queries from Oracle procedures that gets trigger by someone else’s procedure. Is it possible to create Oracle procedures to execute existing SQL queries without recreating a procedure from scratch? Unfortunately we don’t have time or resources or justification to PM to spend time recreating existing SQL queries.
And just another question while I am at it, are procedures the best approach to accomplish the mentioned task?
Many Thanks.
[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
2-
2
5 Comments
Active today
You can execute queries inside procedures but I'm not following what you are asking.
What is currently executing the queries?
What procedures will execute the other procedures?
I'll answer the second question once I understand a little more about what you are asking in the first.
What is currently executing the queries?
What procedures will execute the other procedures?
I'll answer the second question once I understand a little more about what you are asking in the first.
Can you please let us the reason you want to put these queries in procedure? Are you trying to group the relative queries or performance tuning is the intent?
Hi
I hope the info below makes more sense….Let me know if you need further clarification.
What is currently executing the queries?
The existing queries are being executed manually, that is, the developers enter the parameters needed and run the queries from Oracle SQL developer. The data gets exported to a CSV file and save to a shared folder.
What procedures will execute the other procedures?
There is a procedure that has been developed by a different department, when their procedure has been completed then the procedures we create will run.
My aim is for these SQL queries to run unattended with predefined set of parameters then export the result to CSV file and save the CSV file to a shared folder.
But first I would like to know if we can just copy and paste the SQL queries to a procedure without recreating the SQL queries because (a) they work and (b) these queries are complex.
This is the legacy of project that was suspended but some of the work they did to extract data from the databases is useful to teams.
Cheers.
I hope the info below makes more sense….Let me know if you need further clarification.
What is currently executing the queries?
The existing queries are being executed manually, that is, the developers enter the parameters needed and run the queries from Oracle SQL developer. The data gets exported to a CSV file and save to a shared folder.
What procedures will execute the other procedures?
There is a procedure that has been developed by a different department, when their procedure has been completed then the procedures we create will run.
My aim is for these SQL queries to run unattended with predefined set of parameters then export the result to CSV file and save the CSV file to a shared folder.
But first I would like to know if we can just copy and paste the SQL queries to a procedure without recreating the SQL queries because (a) they work and (b) these queries are complex.
This is the legacy of project that was suspended but some of the work they did to extract data from the databases is useful to teams.
Cheers.
Active today
>>But first I would like to know if we can just copy and paste the SQL queries to a procedure without recreating the SQL queries because (a) they work and (b) these queries are complex.
Yes and no. You can use the existing queries inside a procedure but the results of them need to be dealt with. Likely by using a cursor.
>>when their procedure has been completed then the procedures we create will run.
Once you get your procedure tested, then you can add a line to their procedure to call yours.
or
You can create a new procedure that calls both.
I don't know of any way to set up an end-of-procedure trigger.
>> The data gets exported to a CSV file and save to a shared folder.
You can use UTL_FILE to write the output from the procedure to a file system the database server can see.
There are a ton of examples out there on how to do this.
This is the first one I found. There might be better ones but this gives the basics:
http://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html
An alternative is keep the sql you have in a sql script file and schedule a task using sqplus and the spool commands.
Still won't trigger at the end of the other procedure but is likely simpler.
Yes and no. You can use the existing queries inside a procedure but the results of them need to be dealt with. Likely by using a cursor.
>>when their procedure has been completed then the procedures we create will run.
Once you get your procedure tested, then you can add a line to their procedure to call yours.
or
You can create a new procedure that calls both.
I don't know of any way to set up an end-of-procedure trigger.
>> The data gets exported to a CSV file and save to a shared folder.
You can use UTL_FILE to write the output from the procedure to a file system the database server can see.
There are a ton of examples out there on how to do this.
This is the first one I found. There might be better ones but this gives the basics:
http://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html
An alternative is keep the sql you have in a sql script file and schedule a task using sqplus and the spool commands.
Still won't trigger at the end of the other procedure but is likely simpler.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Configuring and using Oracle Database Gateway for ODBC
Introduction
First, a brief summary of what a Database Gateway is. A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.
Suggested Courses
Course of the Month4 days, 19 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.