ora 01882 [message #457601] |
Tue, 25 May 2010 06:12 |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
|
|
Hi,
I got following error while executing the procedure
CREATE OR REPLACE PROCEDURE TZ(P_TIMEZONE VARCHAR2) IS
BEGIN
Execute immediate 'ALTER SESSION SET TIME_ZONE = ''P_TIMEZONE''';
END;
Executing it
ORA-01882 timezone region not found
Can anyone please tell the cause of this error
Regards,
Ritesh
|
|
|
Re: ora 01882 [message #457605 is a reply to message #457601] |
Tue, 25 May 2010 06:40 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
> Execute immediate 'ALTER SESSION SET TIME_ZONE = ''P_TIMEZONE''';
This will try to set timezone to P_TIMEZONE literally and will obviously fail. Discover concatenation operator.
SY.
|
|
|
Re: ora 01882 [message #457606 is a reply to message #457601] |
Tue, 25 May 2010 06:40 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You're setting the timezone to the word 'p_timezone' rather than the contents of the parameter.
Wrong number of quotes. And you're going to need some || operators.
|
|
|
|
|
|
Re: ora 01882 [message #457612 is a reply to message #457609] |
Tue, 25 May 2010 06:48 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
No it isn't. You need to two quotes to get a quote in the string, but then you need a third quote to terminate the string so you can concatenate the variable in. Without that the variable name will go into the string along with the ||.
|
|
|
|
Re: ora 01882 [message #457616 is a reply to message #457614] |
Tue, 25 May 2010 07:06 |
getritesh
Messages: 235 Registered: July 2006 Location: INDIA
|
Senior Member |
|
|
I put concatination mark and extra quotes, now my procedure looks
CREATE OR REPLACE PROCEDURE TZ(P_TIMEZONE VARCHAR2) IS
BEGIN
execute immediate 'ALTER SESSION SET TIME_ZONE = '||''||P_TIMEZONE||''||'';
END;
Executing it
When i say
i get error saying
ora-02248- Invalid option for Alter session
Time Zone can be set in 4 ways
Quote:
O/S Local Time Zone :
ALTER SESSION SET TIME_ZONE = local;
Database Time Zone :
ALTER SESSION SET TIME_ZONE = dbtime zone;
An absolute offset :
ALTER SESSION SET TIME_ZONE = '-05:00';
A named region :
ALTER SESSION SET TIME_ZONE = 'America/New_York';
[Updated on: Tue, 25 May 2010 07:10] Report message to a moderator
|
|
|
Re: ora 01882 [message #457618 is a reply to message #457612] |
Tue, 25 May 2010 07:10 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Tue, 25 May 2010 07:48No it isn't.
Number of quotes is correct for a posted dynamic SQL. Dynamic SQL itself is not.
SY.
|
|
|
Re: ora 01882 [message #457619 is a reply to message #457616] |
Tue, 25 May 2010 07:11 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Your procedure isn't going to cope with all of those options, since two of them don't have quotes and your procedure always adds quotes.
I suspect the simplest approach would be to have 4 procedures.
Oh and you don't need the outer || in your procedure, just the ones around the parameter.
|
|
|
Re: ora 01882 [message #457620 is a reply to message #457618] |
Tue, 25 May 2010 07:12 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
syakobson wrote on Tue, 25 May 2010 13:10cookiemonster wrote on Tue, 25 May 2010 07:48No it isn't.
Number of quotes is correct for a posted dynamic SQL. Dynamic SQL itself is not.
SY.
What does that mean?
|
|
|
Re: ora 01882 [message #457622 is a reply to message #457620] |
Tue, 25 May 2010 07:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Currently your procedure isn't actually wrapping quotes around the parameter.
Which is why it works for local and not -05:00.
First off learn how to debug dynamic sql:
Declare a varchar variable.
Assign the string you want to execute to the variable.
Use dbms_output or some other method to display the contents of the variable.
That way you can see if the string you've generated is actually correct.
Once you've sorted that out, think about the fact that you are going to need more than one procedure to do this as I pointed out already.
|
|
|
|
|
Re: ora 01882 [message #457637 is a reply to message #457636] |
Tue, 25 May 2010 08:17 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Sigh.
I've pointed this problem out twice already.
If the parameter is local then you need to not wrap it in quotes in the sql string.
You'll need to build two different strings to execute in the procedure. One with added quotes and one without.
|
|
|
|
|
Re: ora 01882 [message #457669 is a reply to message #457620] |
Tue, 25 May 2010 10:29 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
cookiemonster wrote on Tue, 25 May 2010 08:12What does that mean?
1. Extra/missing quote in a statement presents syntax issue. There are no syntax issues with posted statement.
2. OP issue is not quote related. So comparing number of quotes in logically wrong statement to number of quotes in logically correct statement brings you nowhere.
3. Number of quotes in logically correct statement can differ, so even comparing it to number of quotes in posted statement can produce different results. There are 6 quotes in posted statement. If you construct correct statement as:
> execute immediate 'ALTER SESSION SET TIME_ZONE = '||''''||P_TIMEZONE||'''';
You get 10 quotes. But if you use:
> execute immediate Q'[ALTER SESSION SET TIME_ZONE = ']' || P_TIMEZONE || Q'[']'
you get same 6 quotes.
SY.
|
|
|
Re: ora 01882 [message #457673 is a reply to message #457636] |
Tue, 25 May 2010 10:49 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
getritesh wrote on Tue, 25 May 2010 09:12Any method for both to work in asingle one?
Yes, it is called checking P_TIMEZONE for values LOCAL or DBTIMEZONE (case insensitive) and based on that building dynamic SQL with/without quotes around P_TIMEZONE.
SY.
|
|
|
Re: ora 01882 [message #457682 is a reply to message #457669] |
Tue, 25 May 2010 11:22 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
syakobson wrote on Tue, 25 May 2010 16:29cookiemonster wrote on Tue, 25 May 2010 08:12What does that mean?
1. Extra/missing quote in a statement presents syntax issue. There are no syntax issues with posted statement.
Well it compiles, but since this is dynamic sql that fact does not prove there are no syntax issues.
syakobson wrote on Tue, 25 May 2010 16:29
2. OP issue is not quote related. So comparing number of quotes in logically wrong statement to number of quotes in logically correct statement brings you nowhere.
Since just adding || to the OPs original code wouldn't have made it actually work the number of quotes is relevant.
syakobson wrote on Tue, 25 May 2010 16:29
3. Number of quotes in logically correct statement can differ, so even comparing it to number of quotes in posted statement can produce different results. There are 6 quotes in posted statement. If you construct correct statement as:
> execute immediate 'ALTER SESSION SET TIME_ZONE = '||''''||P_TIMEZONE||'''';
You get 10 quotes. But if you use:
> execute immediate Q'[ALTER SESSION SET TIME_ZONE = ']' || P_TIMEZONE || Q'[']'
you get same 6 quotes.
SY.
Yes, and that proves me wrong how?
|
|
|
Re: ora 01882 [message #457697 is a reply to message #457682] |
Tue, 25 May 2010 12:00 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
> Yes, and that proves me wrong how?
OK. Last attempt. You said: Wrong number of quotes and you need to use ||. I just showed you can use same number of quotes and || to get right statement.
SY.
|
|
|
Re: ora 01882 [message #457702 is a reply to message #457697] |
Tue, 25 May 2010 12:15 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Only by using the Q mechanism which you've only just mentioned.
And I have no problem with you pointing that approach out.
But all you originally said was that the quotes were right and that || was all that was needed, which is obviously not the case.
|
|
|
|
Re: ora 01882 [message #666656 is a reply to message #457601] |
Sun, 19 November 2017 14:23 |
|
ashisheck
Messages: 2 Registered: November 2017
|
Junior Member |
|
|
execute immediate Q'[ALTER SESSION SET TIME_ZONE = ']' || P_TIMEZONE || Q'[']'
Its a fantastic way. This solves all the problem and accepts all formats.
Thank you so much for mentioning.
|
|
|