Does your no errors rule apply to the common ‘cant drop object cause it
doesn’t exist’ type ORA-s?
Hi Bert,
Oracle provides a command for this CREATE SCHEMA. Basically
you can wrap a collection of DDL commands and treat them as
one transaction (all or one). It's been in Oracle since at
least Oracle 8.i.
It is a good call and indeed, I have used CREATE SCHEMA myself recently.
It's a very nice way of making sure that everything works, however, it
still causes an implicit commit when it all works. It's this commit my
standards try to avoid - because there's invariably some DML hanging
around from earlier on in the script!
Cheers,
Norm. [TeamT]
PS. On a quick vote of the DBAs here, I'm the only one who has used it.
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Your standards look very good. I have one quibble however. It is your “WHENEVER is not permitted”.
I believe it is necessary in certain situations.
We have a requirement that every script has a “WHENEVER SQLERROR &&exit” at the beginning. This is so when the script is being modified and tested the programmer can say continue, but when it is run by our scheduler (which is AppWorx) the scheduler makes it EXIT so that the job will bomb and the scheduler will know it bombed because it gets a UNIX error.
However there are times when we have a script that does things that might fail such as drop table or drop index we put “WHENEVER SQLERROR CONTINUE” and “WHENEVER SQLERROR &&exit” around the statement that may fail so that the script can be rerun if the table create fails on space. The DBAs can increase the tablespace and then the job can be rerun. Without programmer intervention.
I guess however that violates your no DDL and DML mix requirement.
Hi Jeff,
Does your no errors rule apply to the common 'cant drop
object cause it doesn't exist' type ORA-s?
Well, in an ideal world the table dropping would be done in a little bit
of PL/SQL that checks USER_TABLES to see if there is one and drops it
(execute immediate) if so, else, do nothing.
However, we accept that some vendors don't know how to do this, and we
check the log file for any ORA- errors before we commit or rollback. We
also have standards for the Release Notes and install instructions that
make sure that we are warned of potential errors like these.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Norm!
Once a couple of scripts have been rejected and thrown back for
correction, they soon wise up.
Really? How do you wield such power? I once balked at an ERP vendor script
that granted SELECT on SYS.USER$ to all of it's generic users. When I told
them that this action was tantamount to granting DBA privs (thanks to Pete
Finnigan), I was harshly rebuked without evidence and labeled as an
obstructionist -- of course, not directly, but cowardly via our project
manager. Offers to educate them with examples of granting DBA privs with
access to USER$ were met by impatient arguments that I didn't know what I
was talking about. Unfortunately, I was the only one who seemed to care.
Nevermind those silly plans for enabling customers and suppliers DB access
via web interface...
But I'm feeling muuuuuch better now.
Rich -- [TeamT]
Disclaimer: AND (SELECT ssn FROM employees WHERE username = 'NOTME') = 'X'
Erwin,
Your standards look very good. I have one quibble however.
It is your "WHENEVER is not permitted".
Thanks. Quibbles are good.
I believe it is necessary in certain situations.
I disagree, but I'm not unwilling to be convinced otherwise.
We have a requirement that every script has a "WHENEVER
SQLERROR &&exit" at the beginning.
This is so when the
script is being modified and tested the programmer can say
continue, but when it is run by our scheduler (which is
AppWorx) the scheduler makes it EXIT so that the job will
bomb and the scheduler will know it bombed because it gets a
UNIX error.
We run our maintenance scripts by hand, as it were. No schedulers, cron,
etc are used. Simply because we don't allow commit/rollback, a DBA has
to be on hand to make sure the script finished correctly and to enter
the commit/rollback command.
However there are times when we have a script that does
things that might fail such as drop table or drop index we
put "WHENEVER SQLERROR CONTINUE" and "WHENEVER SQLERROR
&&exit" around the statement that may fail so that the
script can be rerun if the table create fails on space.
Good idea. We simply check the log and commit or rollback as
appropriate. Obvioulsy, dropping a table that doesn't exists, for
example, is a no-brainer and we can ignore that error.
The DBAs can increase the tablespace and then the job can be
rerun. Without programmer intervention.I guess however that violates your no DDL and DML mix requirement.
Yes, the auto commit in a DDL statement is forbidden!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Hi Rich,
Really? How do you wield such power?
It all started when I worked in a big UK bank. I can't tell you the
name, but it begins with "B" and ends with "arclays"
They were adamant that all scripts be QAd before applying to any system.
Anything wrong would result in a failure of the script and a return to
vendor.
When I started here in February 2005 (for a two week contract!) there
was no QA process. Scripts were applied as supplied and if they failed,
the DBA spent as long as it took to clean up the mess.
I proposed a QA process based on what I had done previously at Barclays,
it was accepted and now, all code for any release or maintenance script
is QAd by the DBA team prior top the request for change being raised.
(It saves on paperwork if we fail a release prior to the RFC!).
We get a bit of flak from our suppliers occasionally, but our managers
are pretty happy to back us up. Occasionally though, we get a JFDI come
through and we have to put it in regardless.
Basically, it costs less to QA and reject the code that to not bother
and simply apply it. We don't like those times!
The other advantage, if it can be called that, when vendors phone up to
rant and rave about "these have never been rejected in the past" is
having a DBA who is also a "stroppy jock" who knows a little bit about
Oracle and how it works and can shoot down the vendor's objections with
accurate technical advice.
Having a thick skin also helps when the vendors get stroppy.
In the end, it's for our benefit and so far, the fact that prevention is
better then cure (and much cheaper!) seems to be working.
Cheers,
Norm. [TeamStroppyJock]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
I have been running it in debug and yes, I am 100% sure the SQL string is built
to be what I am expecting. That is not the issue at all. Also, the steps execute
exactly as expected. Tracing in, line by line, goes exactly as is expected. It
just returns no rows. It is as if it is selected from a different table than
when I run it in the SQL window.
Bert,
That sounds reasonable. Would it be possible for you to briefly tell me how to
do that?
Thanks,
Eva
I realize I was sort of comparing apples and oranges. You were talking about standards for DBA run scripts and I was trying to apply them to operations run production jobs. Do you by the way have a set of script standards for that?
Do you have more than 1 table/view/syn for that name? If it were a priv
issue, I’d think you’d get a table not found ORA message.
Hi Eva;
Odds are Bert is correct. You are accessing two different objects.
I’ll wager you’ll find a private object, most likely a synonym,
owned by the package owner pointing at one object owned by schema A (not the
package owner) and you’ll also find a public synonym pointing at the same
named object owned by schema B (also not the package owner).
What makes most sense is that your stand-alone SQL is accessing your private
synonym and running the code is accessing the public synonym.
The rights granted on the object the public synonym is pointing to –
Schema B - is most likely granted to public. This is the same as being directly
granted to the package owner. On the other hand, the rights granted to the
object in schema A would be granted to the package owner through a role rather
than directly.
This is a scenario that fits what Bert was describing and all the clues
you’ve provided to your puzzle.
Roger S.
I wonder how many people out there in Toad Land actually turn autocommit on?
Heh, one of the things I look for on a database is to see what the default
behavior is and if commit is on, I set up a SOP to ensure it’s turned off
for any session I’m working on.
Thankfully I’ve never had to have the pleasure of a DBA who has ever set
the default database behavior as commit.
Roger S.
Hi Erwin,
I realize I was sort of comparing apples and oranges.
No worries.
You were talking about standards for DBA run scripts and I was
trying to apply them to operations run production jobs. Do
you by the way have a set of script standards for that?
At the moment, no. Simply because we don't have regularly scheduled
tasks that run against a database. However, if we did have, then:
- they would run under the control of DBMS_JOB or DBMS_SHEDULER as
appropriate and not externally from cron/windows scheduler. - they would have to pass through the normal QA process as well.
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk