What is the impact 2

[sfb]

Please help,

I need to write a VB application using ADO to connect to a Oracle server. We will be doing insert, update and select statements on the Oracle database.

The database is allready in production, and i need to know what impact a update / insert statement can have on a oracle database ( i know about triggers that can fire, but i'm sure there is more ).

Can anyone tell me all the stuff that can happen and i can not see when i execute my SQL statement with ADO?

Does anyone also have any idea how i can predict what Oracle will do on the background if i want to execute a ADO SQL command?

Any help is welcome.

 

[Turkbear]

Hi,
You need to get together with the DBA of the database you wil be accessing and/or you need to learn about Server Concepts and Sql Tuning..

The effect your query will have is not determinable without much more information about your environment.
What version Of Oracle, what are its load factors, what indexes exist, what Sql code are you going to write - etc. etc. etc.

A full explanation of the architecture and process handling of an Oracle RDBMS is beyond the scope of these forums..That's why you have DBAs ( I knew there had to be a reason for us )
and Documentation.

 

[sfb]

Thanks for responding,

The problem is that we need to give some departments direct access to the Oracle databases without our DBA's knowing about it. ( maybe not the best way, but it was instructed by the persons that pay my salary ).

The tool must confirm to some requirements. It has to log all information about the person that executed the SQL command, it can only execute simple SELECT, INSERT, UPDATE and DELETE statements ( no DROP, ALTER, INSER INTO etc ) , and it has to log a before / after situation, specifying exactly what impact the statement had on the database.

So we now need to know exactly what a query does to the database, also considering the changes the database itself could make ( by means of triggers, rules, etc ), next we show the user all impact the disired query has, so he / she can diside not to run the query after all.

It will be a difficult task, but hopefully not impossible.

 

[gherries]

Dangerous and generally foolish, but if you wanna do it then... Dont forget about Views, stored procedures and every single other SQL statement that accesses the database. Sounds like a big impact list to me!

 

[sfb]

Can you tell me why views would have any impact? Why would a view be any different then a query executed directly on a table?

And can stored procedures be triggered directly from a sql command or can they only be activated by triggers? Someone told me about bussines rules, but i'm not sure what Oracle means by that. I only have experience with SQL Server, so i would really like to know what other things Oracle could fire without me knowing about it.

Thanks again.

 

[Turkbear]

Hi, What we are saying is that what you want to do and track is very complex and, without the DBA being invloved, almost certain to fail and possibly make the data in the entire database unreliable at best and unreachable at worst..
If you want to do all that you say you do, then find a way to learn what a Oracle DBA knows very rapidly..Even then, what you want to do may be practically impossible ( the logging alone could take up most of the resources of your system) - Furthermore, if a user can insert,update and delete then the user does not need DROP to really screw thing up..( BTW, Insert INTO and Insert are the same right)
This is not Sqlserver and practically nothing you learned about that will be of help with this system.

My basic advice: run away rapidly...

 

[rcurva]

I am quite confused why you need to develop such a tool. Because, from my point of view, this tool can only be used on ad-hoc transactions like DMLs that is executed independently, or procedures executed independently and manually on sqlplus or worksheet.

Business rules means, if you know and follow these rules properly then there is no need of such a tool bec all actions by your users are valid (within these rules), that I agree is in the Functional Specs of the system in the first place.



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[rcurva]

If these rules are properly in placed, you can just ask your DBA to turn on Auditing if you want to monitor the transactions.

And if you are still in doubt, make sure that your backup/recovery procedure is very reliable, and it looks like you also need to turn on Archiving too.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sfb]

Thank you all for the reply's.

I need to build the tool because the owners of the data within the database ( we only manage it for them ) desire to do some minor updates themself, change some values with update SQL, or insert a forgotten record at some point.

They generally know the structure of the database itself, but don't have a clue about any triggers, etc. So if a trigger responses to a insert query and sends away e-mail messages, my application would need to know.

I now found out that it would really be almost impossible to "read" out any triggers etc from the database ( i was trying with the scema of ADO ). I think the only way is to setup a database that holds all triggers and bussiness rules from the tables, so i can read them out from there.

I dont intent to run away yet, but i really need to become more formiliar with Oracle. At the moment i know business rules, triggers and constraints could be a problem. I hope this is all that comes on my way.

Any other tips are still really welcomed.

Thanks.

 

[gherries]

Everybody else has given you good sound advice, especially turkbear.

What I was meaning by views and stored procedures being impacted is that if you are going in at a low level and updating, inserting and deleting data then every other SQL ststement that is issued against the database can have its results affected. i.e. if its a financial database and your person in marketing (i'll bet its marketing that want this feature ) dont like the figures that have been entered via the order system and they decide to add a couple of extra thousand sales into the system then your accounts arent gonna balance.

I dont see any valid and/or honest requirement for adhoc addition or deletion of data other than via an official route. If your an employee or consultant I'd get a nice legal cover-your-ass statement signed by the people who want this, so that you cant be held accountable.

 

[sfb]

I just got a little further with my investigation. I found out that it is possible to select stuff from a Oracle database like Select * from user_constraints ( get me the defined constraints ) or select * from user_objects where object type = 'PROCEDURE' ( i think to get all stored procedures ).

No i would like to know how to get the real procedure statement ( sql ). I can do a select on user_objects, but the actual procedure doesn't seem to be available. For triggers i did find it:

Select trigger_body from user_triggers ;

I hope you can help.

 

[rcurva]

For the procedures, try;

USER_SOURCE
ALL_SOURCE
DBA_SOURCE
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sfb]

I investigated some more during the weekend, and found out that i can use select statements within Oracle to find out more about triggers, procedures, constraints etc.

For example :

Select trigger_body from user_triggers ;
Will select all triggers defined within the database.

I can also see the views
Select * from user_views ;

But i dont seem to be able to see defined stored procedures ( need some help there ).

I can do something like :
Select * from user_source where type = 'PROCEDURE';

And then the field "type" will give me all defined procedures, but how do i link them back to where the stored procedure is defined.

For example, how do i figure out what stored procedures are defined within the table tbl_Marketing.

Thanks for any help.

 

[rcurva]

If you can access, check sys.dependency$ and dba_objects, you can get the dependencies of objects.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sfb]

Thanks!

I just heared the plan changed a bit. I now have to confince people why we should not create this tool. I need to specify what makes a tool like this practicly impossible to create. I hope you can help me with your Oracle experience with the following :

- I know triggers, constraints and packages could make changes to the database when a SQL statement fires. I dont know enough about packages, can anyone explain them to me?

- I read that Oracle could also use DLL's to execute from triggers and stored procedures. If that is true, nobody can predict what would happen. Can anyone confirm this for me?

- Cascading deletes will not be shown to the user, if i delete one row on a table, a complete other table can be deleted by a cascading delete. I also heared about a cascading insert, but i cant imagine somnething with that. Also i would really like to know if cascading delete and insert are the only "cascading" things within Oracle.

- Drop, alter and rename table. This should not be made possible because other tables could depend on them. I think the above specifies about everything, or is there more i need to think of?

I hope you can help out, i will continue to investigate myself. Thanks for helping out!

 

[Turkbear]

Ok some info on some of your questions,
Packages are collections of Procedures and/or Functions that can use some common elements ( very loose definition) so can exhibit the behavior of procedures or functions or both depending on what is in the package.

There are other cascades like 'Drop user username cascade'
which will eliminate the user and all that user's objects.

Even if you do not allow drop, alter or rename a table can be made unusable with delete, insert rights only.

The bigger problem is the resource use that will be caused by trying to track and log everything that a connection is 'doing' in the database.

( BTW, the DBA that you are trying to exclude from this whole process will be able to monitor everything as well ( and can do so now) and , if so inclined, can totally block your tool's access )

 

[rcurva]

I agree with Turkbear, running this tool of yours as a regular utility in your database will not be a secret as you might think it will be to your DBA. The DBA if experienced enough, has eyes and ears on your database.

Regarding your question about DLL's, yes this is true, you can run DLL's in your stored procedure, they call this External Procedures.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sfb]

I thank all of you a lot. It helps to hear this information from the experts.

I think i now know enough to write a document with enough of reasons why not to build such a tool.

I hope to learn more about Oracle, so maybe i could be of some help to you in the future also.

Thanks!

 

[Turkbear]

Hi,
A willingness to learn is the most important attribute of a true professional, good luck in your endeavor.