In my project we have an extensive PL/SQL in Prod-Replica Envt to generate scripts.
Huh? I have NO idea what that even means. Can you explain it and give an example of what you are talking about? Generate scripts to do what? Why do you have 'extensive PL/SQL' to begin with? What PROBLEM is this solving for you?
However the duration to replicate Prod to this Prod-Replica is long and which is causing a delay in reacting to change requests.
What do you mean by 'replicating Prod'? The objects (tables, code, etc)? The data? Both?
Why are you replicating Prod to that environment? After the initial replication why do you need to keep replicating prod to that environment? No objects or code mods should be done in prod except through a change control process. You can just apply that same process to the replica environment rather than copy prod all over again.,
We have READ-ONLY access to Prod DB but we cannot deploy the PL/SQL Package there. We are thinking of converting the PLSQL into an Anonymous PLSQL block and running it in PROD DB to get the Live data. Either we can run it directly or via Java and then read the dbms output later. as mentioned here
Why do you need 'live data' EVERY time? New data should NOT be needed for change requests.
And why are you using DBMS_OUTPUT?
I don't see how that link is even relevant. You do NOT need to use PL/SQL code, anonymous or otherwise, to execute queries in Java. The example in that article could easily be done in Java by just executing the query (as the poster said they could do successfully) and processing the result set.
But why would you want to use Java to replicate data? That is likely a VERY wrong solution for whatever problem you are trying to solve.
However, not being an expert in PL/SQL i wanted to know, whether this will leave any residues in the PROD server apart from the extra temporary load for read queries. We only have select queries in this PL/SQL and hence I donot believe we set off any triggers or amend the DB in any way.
You likely should NOT be using a solution that does not 'leave any residues' in the system. Well designed production systems should monitor/log ALL activity in order to ensure the system is used properly and performs as expected.
If you are issueing your own queries to 'replicate' data you are potentially having a MAJOR effect on the system. A simple 'SELECT * FROM TABLE' can flush critically needed application data from the buffer cache and impact the performance of your critical applications. Worse, you suggest that you want to be able to do this 'undetected' by not leaving any 'residue'.
That is known as a SECURITY risk.
Tell us what PROBLEM this replication code is supposed to be solving and why you have chosen to use code for replication rather than one of the many Oracle provided functionalities (e.g. Log Miner, Dataguard, streams, etc).
There will be an impact on the server, when converting PL/SQL unit code (procedures and functions) to be encapsulated into a single anonymous PL/SQL code block.
This anonymous PL/SQL code block requires to be parsed and compiled - burning a lot of CPU (as oppose to using already compiled PL/SQL unit code).
This anonymous PL/SQL block is likely to be very large in size as it contains the source code of numerous PL/SQL units too. This needs server memory as the entire code block will be stored (for parsing) in the Shared Pool.
Another performance impact would be network wise. It requires more bandwidth to transfer that large block of code (created by the client) to the Oracle server. This increases latency. And is not a scalable approach.
So no, there will not be "residue" of the code inside the actual physical database as the code is never stored in the database. However, this approach and code will impact server and network resources negatively.
You need to look at the problem and determine if this solution you have in mind, justifies this impact.
For emergency change requests, we have created a multiple Stored Procedures which can create sql scripts, which can then be reviewed and executed. This is preferable to writing sql scripts everytime when a change request comes.
Replica of Prod => Test Database which should have same content as Prod DB. Because Prod DB access cannot be given to all. And Test Teams need a DB to test. Maybe not 'Relication' in the technical sense. Just a Copy I meant.
Live Business Data is needed if the Change Request Involves that Live Data. e.g. A Change Request to Update Status of a Row, that was mistakenly inserted by User.
And why are you using DBMS_OUTPUT? - To create the above mentioned sql script.
We already have a PLSQL, Stored Precedure, which is quite complex. converting the entire thing into Java code, would be complex and would require further Testing. Just Running it as an Anonymous SQL via JDBC is a much easier approach.
Running Anonymous SQL via Java allows us to Point it to Production DB and also manipulate results for further modification in Java It Self.
"You likely should NOT be using a solution that does not 'leave any residues' in the system. Well designed production systems should monitor/log ALL activity in order to ensure the system is used properly and performs as expected." - You are right. But we donot want log of every select query run. Since this Store Prodecure is just a complex series of SELECT queries. It is OK if we donot leave a residue.
If you are issuing your own queries to 'replicate' data you are potentially having a MAJOR effect on the system. A simple 'SELECT * FROM TABLE' can flush critically needed application data from the buffer cache and impact the performance of your critical applications. Worse, you suggest that you want to be able to do this 'undetected' by not leaving any 'residue'. - Yes, I can understand why developers and DBAs are not the best of friends. .... But multiple support personell already have read access to database and they do run hundreds of SELECT without any detection. Faulty design ?? - Not the first one in my career and not under my control. Security Risk ??? - Definitely not the first one in my career and definitely not under my control either. I am just a lowly developer. If they Managers and the DBA's wanted security those heavenly beings should have put up better systems and not just ask us to DO Things. Only Nike can 'Just Do It'.
I AM NOT USING JAVA TO REPLICATE ANYTHING. Just using it to run an Anonymous SP Block containing ONLY SELECT queries in a DB which is a COPY of Prod DB, because we donot have access to Deploy that in PROD. (which means it requires Vogonian (Vogon - Wikipedia, the free encyclopedia ) levels of Approval.)
Peace ... Thanks for your interest in my query. Hope that helps. I hope Moazzam is right on this.