Oracle database patch problems with 1.8

[untergeek]

I have all of my ducks in a row, it's time for the db patch, then I get this:

alter table actions modify def_longdata            nvarchar2(2048)         DEFAULT ''
                                                                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

Everywhere in the script that nvchar2(2048) appears I get this line.

[Alexei]

What is your version of Oracle?

[untergeek]

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 8 17:01:30 2009

[untergeek]

select * from nls_database_parameters


NLS_LENGTH_SEMANTICS
BYTE

[Alexei]

Will it work if you change 2048 to 2000?

[jansonz]

Hello,

I had the same problem. I changed the value to 2000 and the problem went away.

[Alexei]

It seems to be a limitation of older Oracle. It's great to hear it is fixed.

[coop]

We are running into the same issues since we're using Oracle 10.2. Changing the nvarchar2 down to 2000 seems to work for that portion of the patch. This might be a good add-on to documentation? Or possibly a separate 10g patch?

Has anyone encountered an issue with all the & in the script? Our 10g setup has the & as a bind variable and a reserved word which prompts for a value. Is this non-standard or has someone just found a work around for this?

As an example from Oracle Support:

SQL> insert into mytable
values ('a&b');

SQL*Plus is not updating the table and instead responds with the
message:

SQL> enter value for b:


Solution Description
--------------------

You need to modify the update clause as follows:

SQL> insert into mytable
values ('a'||'&'||'b');

[coop]

I think we found the solution. I'll post in case anyone else runs into the same issue of the & being a problem.

* goal: How to insert & character or special character into Database using SQL*Plus.
* fact: Oracle Server - Enterprise Edition
* fact: SQL*Plus

fix:

Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can
do a "SET DEFINE OFF" or "SET SCAN OFF" to turn off the
definition of the ampersand as a substitution variable.

Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES ('AT&T');
/
1 row created

[Alexei]

I think we found the solution. I'll post in case anyone else runs into the same issue of the & being a problem.

* goal: How to insert & character or special character into Database using SQL*Plus.
* fact: Oracle Server - Enterprise Edition
* fact: SQL*Plus

fix:

Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can
do a "SET DEFINE OFF" or "SET SCAN OFF" to turn off the
definition of the ampersand as a substitution variable.

Example:
SQL> SET DEFINE OFF
SQL> INSERT INTO <table_name> VALUES ('AT&T');
/
1 row created

I think it is already in the Manual:

http://www.zabbix.com/documentation/...on_from_source

[coop]

Very true! Sorry, that was entirely my fault. I should have referred back to the original Install steps, I was just following the upgrade steps.

[untergeek]

I had a raft of other import problems. I will elaborate when I get to work (I'm at home still).

I'm tempted to revert to my backup and use the 1.7 patches previously linked that continue to use varchar2 (we have no need for MBstrings or multibyte characters at all).

Will this cause problems?

[coop]

Ok!

Here is one more we encountered....

The following lines from the patch.sql all produced the same error:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT '' NOT NULL
alter table hosts add snmp_error nvarchar2(128) DEFAULT '' NOT NULL
alter table items add username nvarchar2(64) DEFAULT '' NOT NULL
alter table items add password nvarchar2(64) DEFAULT '' NOT NULL
alter table items add publickey nvarchar2(64) DEFAULT '' NOT NULL
alter table items add privatekey nvarchar2(64) DEFAULT '' NOT NULL

*
ERROR at line 1:
ORA-01407: cannot update to NULL

From what we can see every other nvarchar2 accepts NULL in the tables so it wasn't clear why these new ones wouldn't or shouldn't. We tested changing them to accept NULL like all the others and they added fine after that.....

[untergeek]

Yep! That's one of the ones I was experiencing as well!

Even after changing all of the 2048s to 2000s I was getting errors related to these. The zabbix_server process would start to attempt Web checks and fail immediately with a message like this:

18757:20091208:172548.337 [Z3005] Query failed: [-1] ORA-00904: "HTTP_PASSWORD": invalid identifier
 [select httptestid,name,applicationid,nextcheck,status,delay,macros,agent,authentication,http_user,http_password from httptest where status=0 and nextcheck<=1260314748 and mod(httptestid,10)=2 and httptestid between 000000000000000 and 099999999999999]
 18716:20091208:172548.337 One child process died (PID:18755). Exiting ...

[untergeek]

From what we can see every other nvarchar2 accepts NULL in the tables so it wasn't clear why these new ones wouldn't or shouldn't. We tested changing them to accept NULL like all the others and they added fine after that.....

So, did you simply update those lines from:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT '' NOT NULL

to:

alter table hosts add ipmi_error nvarchar2(128) DEFAULT '' NULL