asked on Procedure to run existing SQL queries
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.
Oracle Database
Last Comment
jose11au
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for your information. I hav been doing some reading on cursors and will start using cursors very soon.
Cheers.
Cheers.
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.