Re: Unexpected conversion from AL32UTF8 to WE8ISO8859P1
Date: Fri, 9 Jan 2009 00:02:10 -0800 (PST)
Message-ID: <d89de489-f168-43d3-a561-9f9ba88ba7f1_at_17g2000vbf.googlegroups.com>
On Jan 8, 5:21 pm, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> Jaap W. van Dijk <j.w.vand..._at_hetnet.nl> wrote:
>
> > Just to be sure about what is in the database: could you execute a
> > 'select dump(val) from nlsdate' on both databases?
>
> I already deleted all the stuff...
> But I'm sure that I did not encounter your problem because there were
> no inverted question marks.
>
> > Furthermore my, procedure is coded as follows:
>
> > CREATE OR REPLACE procedure jvd_conv
> > as
> > begin
> > delete from test_jvd;
> > dbms_output.put_line('delete: '||sql%rowcount);
> > insert into test_jvd
> > select * from test_jvd_at_apps_cjibda;
> > dbms_output.put_line('insert: '||sql%rowcount);
> > commit;
> > end;
> > /
>
> Looks simple enough.
>
> Could you run my test case and see if it works for you or not?
>
> Yours,
> Laurenz Albe
This is my testcase (comments between square brackets):
[Logging on to the source database:]
SQL*Plus: Release 10.1.0.2.0 - Production on Vr Jan 9 08:40:14 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select *
2 from v$nls_parameters
3 where parameter = 'NLS_CHARACTERSET';
PARAMETER
VALUE
NLS_CHARACTERSET
AL32UTF8 [Logging on to the target database:]
SQL*Plus: Release 10.1.0.2.0 - Production on Vr Jan 9 08:41:31 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
[Here I have got a procedure with the following code:]
CREATE OR REPLACE procedure DWH.jvd_conv_rbn
as
charset varchar2(100);
begin
select value
into charset
from v$nls_parameters
where parameter = 'NLS_CHARACTERSET'
DBMS_OUTPUT.PUT_LINE(CHARSET);
delete from test_jvd;
dbms_output.put_line('delete: '||sql%rowcount);
insert into test_jvd
select * from test_jvd_at_robein_ontw01_rbn;
dbms_output.put_line('insert: '||sql%rowcount);
commit;
end;
/
[If I execute this procedure it fails because the table does not exists in the source database:]
SQL> exec jvd_conv_rbn
BEGIN jvd_conv_rbn; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist ORA-02063: preceding line from ROBEIN_ONTW01_RBN ORA-06512: at "DWH.JVD_CONV_RBN", line 12 ORA-06512: at line 1
[Now I create and fill the table at the source database:]
SQL> create table test_jvd (teken varchar2(1 character),tekst varchar2 (100));
Table created.
SQL> insert into test_jvd (teken,tekst) values (chr(49765),'Small E acute');
1 row created.
SQL> commit;
Commit complete.
SQL> select dump(teken)
2 from test_jvd;
DUMP(TEKEN)
Typ=1 Len=2: 194,101
SQL> [And I execute the procedure again at the target database:]
SQL> set serveroutput on
SQL> drop table test_jvd;
Table dropped.
SQL> create table test_jvd (teken varchar2(1),tekst varchar2(100));
Table created.
SQL> exec jvd_conv_rbn
WE8ISO8859P1
delete: 0
insert: 1
PL/SQL procedure successfully completed.
SQL> select dump(teken),tekst
2 from test_jvd;
DUMP(TEKEN)
TEKST
Typ=1 Len=1: 191
Small E acute
SQL> [Which is the wrong value]
Regards,
Jaap.
Received on Fri Jan 09 2009 - 02:02:10 CST