ora-01480: trailing null missing from STR bind value

[johnsok]

When using 5.1.6 pl/sql developer, after we do a "select for update" in a sql window and set a 4000 byte varchar2 column with data greater than 1,333 bytes, we get this oracle error: ora-01480. As soon as I try to update with 1,334 bytes (or greater), then error, if 1,333 bytes or less, then no problems. How do we fix this problem? Our database is UTF8, my PC's NLS_LANG is set to AMERICAN_AMERICA.WE8MSWIN1252, my o/s is Windows 2000. The characters in question were straight ascii characters (nothing beyond ascii 127).

 

[johnsok]

Also, when the same update statement is applied outside of PLSD (such as sqlplus), the update is fine and no errors. It is only side of PLSD that 1334 bytes or higher updates create the error.

 

[Marco Kalter]

Did you also use a bind variable in SQL*Plus?

 

[johnsok]

No binds were used, just a straight hard-coded where clause (where pk=value).

 

[Marco Kalter]

What happens if you do use a bind variable var) in SQL*Plus? This is what PL/SQL Developer does.

 

[johnsok]

The maximum variable size in sql*plus is 240, so I have to manually type (copy/paste) the large 1334+ text into the sql*plus update statement.

Which is why using PLSD to do this task would be a beautiful thing! I've had problems with this error in many prior versions, not just 5.1.6.

 

[Marco Kalter]

Can you go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail?

 

[sacha]

Hello,

I am (still) experiencing the same error using pl/sql developer 6.0.3.893. Our database is set to use multi byte chars, presumably the binding of pl/sql dev can not deal properly with nullterminated strings in combination with multi byte chars.

Is there any solution available to this issue?

-s

 

[Marco Kalter]

Can you also go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail?

 

[vopipari]

I am also recieving this error when trying to update a varchar2(2000) column via PL/SQL Developer. It does not matter if there is a bind in the string or not. I appear to get the error message after the value reaches a certain size. I am using 6.0.5.926.

Any update to this problem? As I cannot perform the update, I must extract the data in the column, make changes then update the record via SQL*Plus or a command window in PL/SQL Developer.

 

[Marco Kalter]

Can you go to Help > About, press the 'Info' button, press the 'Copy all pages' button on this info screen, and paste this text into an e-mail?

 

[Sharon]

I haven't seen a solution to the problem of trying to edit a large field - varchar2(4000 char) - using the large data editor. Here is my the data from 'Info':

--- Info ---

PL/SQL Developer
Version 7.0.3.1123 (MBCS)
01.40067 - Unlimited user license
Windows XP version 6.0 (build 2600) Service Pack 2

Physical memory : 1,039,080 kB (304,372 available)
Paging file : 2,498,448 kB (1,875,876 available)
Virtual memory : 2,097,024 kB (1,992,588 available)

Parameters
C:\Program files\plsql developer\plsqldev.exe
BPC=4

Preferences
Session mode: Multi
OCI Library:
Use OCI7: False

Preference Files
C:\Program files\plsql developer\Preferences\Default\Default.ini
C:\Program files\plsql developer\Preferences\spenner\default.ini

Plug-Ins

Aliases
axiseed
axitest
ba8seed
ba8test
bannext
...

Homes
OraHome92 (C:\oracle\ora92)
OUIHome1 (C:\OraHome_1)

DLLs
C:\oracle\ora92\bin\oci.dll

TNS File
T:\WINDOWS\ORACLE\tnsnames.ora

Using
Home: OraHome92
DLL: C:\oracle\ora92\bin\oci.dll
OCI: version 9.2
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Character size: 4 byte(s)

--- Registry ---

SOFTWARE\ORACLE
inst_loc = C:\Program Files\Oracle\Inventory
ORACLE_HOME = C:\oracle\ora92
ORACLE_HOME_NAME = OraHome92
API = C:\oracle\ora92\dbs
ORACLE_GROUP_NAME = Oracle - OraHome92
NLS_LANG = NA
OLEDB = C:\oracle\ora92\oledb\mesg
OO4O = C:\oracle\ora92\oo4o\mesg
VOBHOME2.0 = C:\OraHome_1

SOFTWARE\ORACLE\ALL_HOMES
HOME_COUNTER = 1
DEFAULT_HOME = OraHome92
LAST_HOME = 0

SOFTWARE\ORACLE\ALL_HOMES\ID0
NAME = OraHome92
PATH = C:\oracle\ora92
NLS_LANG = NA

SOFTWARE\ORACLE\AUTOTUNE9.2.0
help_path = ${vobhome2.0}bin/
debug_flags =
startlog = ${home}autotune/%1.log
trace_report = 0
trace_data = 0
collect_int_in_min = 15
analyze_int_in_hrs = 24
max_errors_allowed = 5
wip_batch = 0
wip_window = 0
wip_logfile = 0
wip_logtable = 0

SOFTWARE\ORACLE\Calendar
Version = 10.1.2.0.0
AppPath = C:\Program Files\Oracle\Calendar\

SOFTWARE\ORACLE\EXPERT9.2.0
home = ${vobhome2.0}sysman/expert
help_path = ${vobhome2.0}bin/
xpcoin = ${vobhome2.0}bin/xpcoin
default_rule_file = xpo000
debug_flags =
sample = ${home}sample/
sample_xdl = ${sample}xppso.xdl
startlog = ${vobhome2.0}sysman/temp/xpexpert.log
trace_report = 0
trace_data = 0
max_orphan_txn_reqs = 100
max_cache_txn_reqs = 50
extract_explain_plan = 0
create_tmp_plan_table = 0
tmp_plan_table = xp_tmp_plan
wip_batch = 0
wip_window = 1
wip_logfile = 0
wip_logtable = 0
quicktour_start = ${vobhome2.0}classes/oracle/sysman/qtour/tune/us/tune.htm
report_viewer = write
startup_wizard = 1
collect_trace_stats = 1
standalone_mode = 0

SOFTWARE\ORACLE\HOME0
ID = 0
ORACLE_GROUP_NAME = Oracle - OraHome92
ORACLE_HOME_NAME = OraHome92
ORACLE_HOME = C:\oracle\ora92
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
ORACLE_BUNDLE_NAME = Enterprise
ORAMTS_CP_TRACE_LEVEL = 0
ORAMTS_CP_TRACE_DIR = C:\oracle\ora92\oramts\Trace
ORAMTS_CONN_POOL_TIMEOUT = 120
ORAMTS_SESS_TXNTIMETOLIVE = 120
ORAMTS_NET_CACHE_MAXFREE = 5
ORAMTS_NET_CACHE_TIMEOUT = 120000
ORAMTS_OSCREDS_MATCH_LEVEL = OS_AUTH_LOGIN
SQLPATH = C:\oracle\ora92\dbs
ORACLE_HOME_KEY = Software\ORACLE\HOME0
MSHELP_TOOLS = C:\oracle\ora92\MSHELP

SOFTWARE\ORACLE\iSQLPlus
ServerCount = dword:00000000

SOFTWARE\ORACLE\JInitiator

SOFTWARE\ORACLE\JInitiator\1.3.1.18
JavaHome = C:\Program Files\Oracle\JInitiator 1.3.1.18

SOFTWARE\ORACLE\KEY_OUIHome1
ORACLE_HOME = C:\OraHome_1
ORACLE_HOME_NAME = OUIHome1
ORACLE_GROUP_NAME = Oracle - OUIHome1
ORACLE_BUNDLE_NAME = Enterprise
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252
SQLPATH = C:\OraHome_1\dbs
MSHELP_TOOLS = C:\OraHome_1\MSHELP
ORACLE_HOME_KEY = SOFTWARE\ORACLE\KEY_OUIHome1

SOFTWARE\ORACLE\OLEDB
CacheType = Memory
ChunkSize = 100
DistribTX = 1
FetchSize = 100
OSAuthent = 0
PLSQLRSet = 0
PwdChgDlg = 1
SchRstLng = 10000
UserDefFn = 0
DisableRetClause = 1
VCharNull = 1
TraceCategory = 0
TraceFileName = c:\OraOLEDB.trc
TraceLevel = 0
TraceOption = 0

SOFTWARE\ORACLE\OO4O
CacheBlocks = 20
FetchLimit = 100
FetchSize = 4096
HelpFile = C:\oracle\ora92\MSHELP\oracleo.hlp
PerBlock = 16
SliceSize = 256
TempFileDirectory = c:\temp
OO4O_HOME = C:\oracle\ora92\oo4o

SOFTWARE\ORACLE\OracleMTSRecoveryService

SOFTWARE\ORACLE\OracleMTSRecoveryService\Protid_0
Name = HTTP
Host = 130.179.250.128
Port = 2030

SOFTWARE\ORACLE\OracleMTSRecoveryService\Setup
NumberOfInstalls =

SOFTWARE\ORACLE\Workflow
Icon Directory = C:\oracle\ora92\WF\ICON
Level = 100
Version = 2.6.1.5.2

SOFTWARE\ORACLE\Workflow\Languages

--- Path ---

C:\oracle\ora92\bin
C:\OraHome_1\bin

C:\Program Files\Oracle\jre\1.3.1\bin
C:\Program Files\Oracle\jre\1.1.8\bin
C:\WINDOWS\system32
C:\WINDOWS
C:\WINDOWS\System32\Wbem
C:\WINDOWS\system32\nls
C:\WINDOWS\system32\nls\ENGLISH
C:\Program Files\Microsoft SQL Server\80\Tools\BINN
C:\Program Files\MySQL\MySQL Server 5.0\bin
C:\Program Files\Novell\ZENworks\
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\
C:\Program Files\Microsoft SQL Server\90\Tools\binn\
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
Z:.
Y:.
X:.
W:.

 

[mamv]

Hi Marco,

I'm having exactly the same problem using the large data editor.

Bellow you will find the string I'm trying to put on a varchar2(4000) field using large data editor and that triggers the ORA-01480: trailing null missing from STR bind value:

BEGIN pk_prov_ndb_interface.get_service_info; pk_client_service.treat_create_client_service(service_name_i => pk_prov_ndb_interface.l_service_info.service_name, servicetypeid_i => pk_prov_ndb_interface.l_service_info.servicetypeid, service_relativename_i => pk_prov_ndb_interface.l_service_info.service_relativename, service_tectype_i => pk_prov_ndb_interface.l_service_info.service_tectype, service_bandwidth_i => pk_prov_ndb_interface.f_parse_string('BANDWIDTH'), subscriber_name_i => pk_prov_ndb_interface.f_parse_string('CLIENT_NAME'), nif_i => pk_prov_ndb_interface.f_parse_string('NIF'), subscribertypeid_i => pk_prov_ndb_interface.l_service_info.subscribertypeid, subscriber_node_i => pk_prov_ndb_interface.f_parse_string('CLIENT_NODE'), provisionid_i => pk_prov_ndb_interface.l_service_info.provisionid, service_exists_flag_i => 'N', errorcode_o => :errorcode, output_o => utput); IF :errorcode = 1 THEN :errorcode := NULL; utput := NULL; pk_vadx.ps_vadb_bripots_aloc_port(i_tiporua => NULL, i_morada_rua => NULL, i_morada_num_pol => NULL, i_morada_andar => NULL, i_fraccao => NULL, i_localidade => NULL, i_edificio => NULL, i_edif_preinfra => NULL, i_sigle_pap => pk_prov_ndb_interface.f_parse_string('CENTRAL_ACRONYM'), i_cod_postal => NULL, i_contacto_tecn => NULL, i_telefone => NULL, i_fax => NULL, i_area_central => pk_prov_ndb_interface.f_parse_string('CENTRAL_AREA'), i_cod_site_dim => pk_prov_ndb_interface.f_parse_string('CLIENT_NODE'), i_agregado => pk_prov_ndb_interface.f_parse_string('SERVICE_AGGREGATOR'), i_class_debito => pk_prov_ndb_interface.f_parse_string('BANDWIDTH'), o_output => utput, o_errorcode => :errorcode); END IF; END;

If I try to update the same string via an update statement everything works OK.