|
|
|
|
No values in Debug, values when executed alone
Posted:
Feb 10, 2010 5:34 AM
in response to: bscalzo
|
|
|
I have a stored procedure that runs the following dynamic sql. When the sql is
run in the procedure it returns 0 rows. When the exact same sql string is run in
a standard editor window it returns 366 rows. I can see that the fetch is
working and the sql is being built to be correct. The value of v_SQL is exactly
what is expected, which is why I can run it in a standard editor window. It just
returns 0 rows inside the procedure. Any ideas? All of this is run on Toad 10.1
in debug mode.
This is just the beginning of the loop. The end loop exists later but there is
much more in the routine than this and this is the part that is failing again
and again.
OPEN Edits_Tab;
Loop
Fetch Edits_Tab into v_FieldName, v_Condition, v_Result, v_DataEditName;
Exit when Edits_Tab%NOTFOUND;
v_SQL := '(select count(*) ';
v_SQL := v_SQL||'from '||UPPER(p_TableName)||' t ';
v_SQL := v_SQL||'where t.ERRFLAG=''0''';
if p_DataVrsn 'ALL' then
v_SQL := v_SQL||'and t.DATAVRSN = '''||p_DataVrsn||''' ';
end if;
v_SQL := v_SQL||' and ('||UPPER(v_Condition)||')) ';
execute immediate v_SQL into v_RowCount;
Thank you!!!
Eva
|
|
|
Posts:
304
Registered:
5/19/06
|
|
|
|
RE: No values in Debug, values when executed alone
Posted:
Feb 10, 2010 5:44 AM
in response to: Eva
|
|
|
When you run code in PL/SQL you cannot inherit privs granted to roles within
that code – the grants must be made directly. It’s possible that
when you run as script it works because roles grants function as expected and it
does not when done as PL/SQL because you loose the roles grants ….
Not 100% sure this is your issue – but a common thing to check ….
|
|
|
Posts:
476
Registered:
8/10/06
|
|
|
|
RE: No values in Debug, values when executed alone
Posted:
Feb 10, 2010 5:48 AM
in response to: Eva
|
|
|
Did you compile with debug?
If so, you should be able to see the value of v_SQL inside the loop as it
iterates by just putting your mouse over the text.
I’m wondering if your EXIT WHEN is getting fired prematurely…as you
step through the loop does it make it through the entire construct? You could
also see this if you ran the Profiler – it records each line of code
executed.
|
Follow me on twitter @hillbillyToad
Check out my blog on ToadWorld
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:06 AM
in response to: wimdelange_062
|
|
|
Hi Wim,
>> Hmmm. Sounds very nice. Never give this a thought. Sounds
>> good. Are those standards for the scripts available for the public?
http://qdosmsq.dunbar-it.co.uk/blog/?p=236
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
|
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:08 AM
in response to: Peter van Rijn
|
|
|
Peter,
>> We're drifting a bit too off-topic I'm afraid, but talking about
>> spooling, I don't know if your standards mention spooling as well.
Yes, if the script is "myFirstScript.sql" then the spool must be to
"myFirstScript.log" (or .txt or something else). Common spool files are
not permitted.
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
|
|
|
Posts:
476
Registered:
8/10/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:11 AM
in response to: Norm [TeamT]
|
|
|
Does your no errors rule apply to the common ‘cant drop object cause it
doesn’t exist’ type ORA-s?
|
Follow me on twitter @hillbillyToad
Check out my blog on ToadWorld
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:14 AM
in response to: bscalzo
|
|
|
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
|
|
|
Posts:
194
Registered:
12/18/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:23 AM
in response to: Norm [TeamT]
|
|
|
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.
|
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:39 AM
in response to: HillbillyToad
|
|
|
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
|
|
|
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:46 AM
in response to: Norm [TeamT]
|
|
|
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'
|
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 7:53 AM
in response to: erwin.rollauer@...
|
|
|
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
|
|
|
Posts:
343
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 8:02 AM
in response to: Rich Jesse
|
|
|
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
|
|
|
|
|
|
|
Re: No values in Debug, values when executed alone
Posted:
Feb 10, 2010 9:18 AM
in response to: HillbillyToad
|
|
|
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.
|
|
|
|
|
|
|
Re: No values in Debug, values when executed alone
Posted:
Feb 10, 2010 9:19 AM
in response to: bscalzo
|
|
|
Bert,
That sounds reasonable. Would it be possible for you to briefly tell me how to
do that?
Thanks,
Eva
|
|
|
Posts:
194
Registered:
12/18/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 9:33 AM
in response to: Norm [TeamT]
|
|
|
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?
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
