Home » SQL & PL/SQL » SQL & PL/SQL » ora 01882 (Sql Developer, 1.5.1.54.40, Windows Server 2003)
ora 01882 [message #457601] Tue, 25 May 2010 06:12 Go to next message
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

BEGIN
 TZ('local');
END;

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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #457608 is a reply to message #457606] Tue, 25 May 2010 06:42 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Plus I don't believe there is a timezone region called local anyway.
Re: ora 01882 [message #457609 is a reply to message #457606] Tue, 25 May 2010 06:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 25 May 2010 07:40
Wrong number of quotes.


Number of quotes is correct - single quote within a quoted string must be escaped by a second single quote.

SY.

[Updated on: Tue, 25 May 2010 06:44]

Report message to a moderator

Re: ora 01882 [message #457611 is a reply to message #457609] Tue, 25 May 2010 06:48 Go to previous messageGo to next message
John Watson
Messages: 7186
Registered: January 2010
Location: Global Village
Senior Member
My 11.2 database has 2137 rows in v$timezone_names, but none of them is 'local'.
Re: ora 01882 [message #457612 is a reply to message #457609] Tue, 25 May 2010 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #457614 is a reply to message #457609] Tue, 25 May 2010 06:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2110
Registered: October 2003
Location: Hyderabad, India
Senior Member
Quote:
Number of quotes is correct
Execute immediate 'ALTER SESSION SET TIME_ZONE = ''P_TIMEZONE''';
which yields
ALTER SESSION SET TIME_ZONE = 'P_TIMEZONE';
But I don't think OP need this. It needs to be replaced with the value there (or) bind it.
ALTER SESSION SET TIME_ZONE = 'XXXXXX'
By
Vamsi
Re: ora 01882 [message #457616 is a reply to message #457614] Tue, 25 May 2010 07:06 Go to previous messageGo to next message
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

BEGIN
 TZ('local');
END;


When i say

BEGIN
 TZ('-05:00');
END;


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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 25 May 2010 07:48
No 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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
syakobson wrote on Tue, 25 May 2010 13:10
cookiemonster wrote on Tue, 25 May 2010 07:48
No 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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #457630 is a reply to message #457616] Tue, 25 May 2010 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First be sure the string you pass to execute immediate contains a valid statement.
For this replace "execute immediate" by "dbms_put.put_line" and try to manually execute the statement you see.

Regards
Michel
Re: ora 01882 [message #457636 is a reply to message #457630] Tue, 25 May 2010 08:12 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Changed procedure to

CREATE OR REPLACE PROCEDURE TZ(P_TIMEZONE VARCHAR2) IS
BEGIN
  execute immediate 'ALTER SESSION SET TIME_ZONE = '||''''||P_TIMEZONE||'''';

END;

Now

BEGIN
 TZ('-05:00');
END;

is working

BEGIN
 TZ('local');
END;

is not working


Any method for both to work in asingle one?
Re: ora 01882 [message #457637 is a reply to message #457636] Tue, 25 May 2010 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #457641 is a reply to message #457636] Tue, 25 May 2010 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/

Michel Cadot wrote on Tue, 25 May 2010 14:54
First be sure the string you pass to execute immediate contains a valid statement.
For this replace "execute immediate" by "dbms_put.put_line" and try to manually execute the statement you see.


2/

"Is not working" is NOT an Oracle message.

Use SQL*Plus and copy and paste your session.
Almost 4 years and 150 posts and you don't know that!

Regards
Michel
Re: ora 01882 [message #457654 is a reply to message #457637] Tue, 25 May 2010 09:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2110
Registered: October 2003
Location: Hyderabad, India
Senior Member
cookiemonster wrote on Tue, 25 May 2010 18:47
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.
And you yourselves posted asQuote:
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';
Then why are you setting this?Quote:
ALTER SESSION SET TIME_ZONE = 'local'
By
Vamsi
Re: ora 01882 [message #457669 is a reply to message #457620] Tue, 25 May 2010 10:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
cookiemonster wrote on Tue, 25 May 2010 08:12
What 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
getritesh wrote on Tue, 25 May 2010 09:12
Any 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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
syakobson wrote on Tue, 25 May 2010 16:29
cookiemonster wrote on Tue, 25 May 2010 08:12
What 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
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 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 #666655 is a reply to message #457601] Sun, 19 November 2017 14:00 Go to previous messageGo to next message
ashisheck
Messages: 2
Registered: November 2017
Junior Member
Hi All,

The discussion came very useful for me.
I understand that :

Case 1)
CREATE OR REPLACE PROCEDURE TZ(P_TIMEZONE VARCHAR2) IS
BEGIN
execute immediate 'ALTER SESSION SET TIME_ZONE = '||''''||P_TIMEZONE||'''';

END;

The above procedure works fine, if we make a call in the following manner:

BEGIN
TZ('-05:00');
END;

...........................................

Case 2)
CREATE OR REPLACE PROCEDURE TZ(P_TIMEZONE VARCHAR2) IS
BEGIN
execute immediate 'ALTER SESSION SET TIME_ZONE = '||''||P_TIMEZONE||''||'';
END;


The above procedure works fine, if we make a call in the following manner:

BEGIN
TZ('local');
END;

---------------------------------------------------------------------------

Now, how should we write the procedure so that I can be invoked in the following manner:

BEGIN
TZ('America/New_York');
END;

.................................

I am trying a lot of ways, but now able to achieve the correct way.

An early response will be highly appreciated.

Thanks & Regards,
Ashish
Re: ora 01882 [message #666656 is a reply to message #457601] Sun, 19 November 2017 14:23 Go to previous message
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.
Previous Topic: REST API with DDOS protection
Next Topic: Passing warning collection from PL/SQL
Goto Forum:
  


Current Time: Sat Dec 16 00:42:11 CST 2017

Total time taken to generate the page: 0.02684 seconds