OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » ora 01882 (Sql Developer, 1.5.1.54.40, Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

BEGIN
 TZ('local');
END;

ORA-01882 timezone region not found

Can anyone please tell the cause of this error

Regards,
Ritesh

Report message to a moderator

Re: ora 01882 [message #457605 is a reply to message #457601]

Tue, 25 May 2010 06:40

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: ora 01882 [message #457606 is a reply to message #457601]

Tue, 25 May 2010 06:40

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: ora 01882 [message #457608 is a reply to message #457606]

Tue, 25 May 2010 06:42

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Plus I don't believe there is a timezone region called local anyway.

Report message to a moderator

Re: ora 01882 [message #457609 is a reply to message #457606]

Tue, 25 May 2010 06:44

Solomon Yakobson
Messages: 3273
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

John Watson
Messages: 8929
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'.

Report message to a moderator

Re: ora 01882 [message #457612 is a reply to message #457609]

Tue, 25 May 2010 06:48

cookiemonster
Messages: 13920
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 ||.

Report message to a moderator

Re: ora 01882 [message #457614 is a reply to message #457609]

Tue, 25 May 2010 06:50

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

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

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

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: ora 01882 [message #457619 is a reply to message #457616]

Tue, 25 May 2010 07:11

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: ora 01882 [message #457620 is a reply to message #457618]

Tue, 25 May 2010 07:12

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: ora 01882 [message #457622 is a reply to message #457620]

Tue, 25 May 2010 07:19

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: ora 01882 [message #457630 is a reply to message #457616]

Tue, 25 May 2010 07:54

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: ora 01882 [message #457636 is a reply to message #457630]

Tue, 25 May 2010 08:12

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?

Report message to a moderator

Re: ora 01882 [message #457637 is a reply to message #457636]

Tue, 25 May 2010 08:17

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: ora 01882 [message #457641 is a reply to message #457636]

Tue, 25 May 2010 08:37

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: ora 01882 [message #457654 is a reply to message #457637]

Tue, 25 May 2010 09:23

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

Re: ora 01882 [message #457669 is a reply to message #457620]

Tue, 25 May 2010 10:29

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: ora 01882 [message #457673 is a reply to message #457636]

Tue, 25 May 2010 10:49

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: ora 01882 [message #457682 is a reply to message #457669]

Tue, 25 May 2010 11:22

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: ora 01882 [message #457697 is a reply to message #457682]

Tue, 25 May 2010 12:00

Solomon Yakobson
Messages: 3273
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.

Report message to a moderator

Re: ora 01882 [message #457702 is a reply to message #457697]

Tue, 25 May 2010 12:15

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: ora 01882 [message #666655 is a reply to message #457601]

Sun, 19 November 2017 14:00

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

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	REGEXP_SUBSTR
Next Topic:	Passing warning collection from PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 05:28:45 CDT 2024