Wednesday, October 31, 2012

Windows command prompt and correct national characters

Until Oracle 11g, every Oracle client installation on Windows had sqlplusw.exe, which is classic Windows application version of sqlplus. But in 11g, Oracle decided that this small application is not necessary. So only sqlplus.exe remain (not counting iSqlPlus which works only through WEB interface and additional setting). For Linux world this is far from a problem, but on Windows some of users may find in problem according this.

The Problem

Start sqlplus from command prompt (Start->Run->cmd):
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlplus tools/pass@db1

SQL*Plus: Release 11.2.0.1.0 Production on Sri Lis 24 13:20:35 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

SQL>
Create small table with two columns: c1 number and c2 varchar2(16 char):
SQL> create table xxx (c1 number, c2 varchar2(16 char));

Table created.

SQL>
Put in this table one row with some national accented characters (in Croatia):
SQL> insert into xxx values (1,'đšžćčĐŠŽĆČ');

1 row created.

SQL> commit;

Commit complete.
Let's see from the same sqlplus the content of inserted row:
SQL> select * from xxx;

        C1 C2
---------- ----------------
         1 đšžćčĐŠŽĆČ

SQL>
Looks good. Let's see the same data from Toad:
Ouups! Something went wrong badly.
Let me look in some important properties in same command prompt, nls setings:
select * from nls_database_parameters;                        select * from nls_instance_parameters;     select * from nls_session_parameters;

PARAMETER                      VALUE                          PARAMETER                      VALUE       PARAMETER                      VALUE
------------------------------ --------------------------     ------------------------------ ---------   ------------------------------ ----------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16                      NLS_LANGUAGE                   CROATIAN    NLS_LANGUAGE                   CROATIAN
NLS_LANGUAGE                   CROATIAN                       NLS_TERRITORY                  CROATIA     NLS_TERRITORY                  CROATIA
NLS_TERRITORY                  CROATIA                        NLS_SORT                                   NLS_CURRENCY                   kn
NLS_CURRENCY                   kn                             NLS_DATE_LANGUAGE                          NLS_ISO_CURRENCY               CROATIA
NLS_ISO_CURRENCY               CROATIA                        NLS_DATE_FORMAT                            NLS_NUMERIC_CHARACTERS         ,.
NLS_NUMERIC_CHARACTERS         .,                             NLS_CURRENCY                               NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               EE8MSWIN1250                   NLS_NUMERIC_CHARACTERS                     NLS_DATE_FORMAT                DD.MM.RR
NLS_CALENDAR                   GREGORIAN                      NLS_ISO_CURRENCY                           NLS_DATE_LANGUAGE              CROATIAN
NLS_DATE_FORMAT                DD.MM.RR                       NLS_CALENDAR                               NLS_SORT                       CROATIAN
NLS_DATE_LANGUAGE              CROATIAN                       NLS_TIME_FORMAT                            NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_SORT                       CROATIAN                       NLS_TIMESTAMP_FORMAT                       NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_FORMAT                HH24:MI:SSXFF                  NLS_TIME_TZ_FORMAT                         NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF         NLS_TIMESTAMP_TZ_FORMAT                    NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR              NLS_DUAL_CURRENCY                          NLS_DUAL_CURRENCY              kn
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR     NLS_COMP                                   NLS_COMP                       BINARY
NLS_DUAL_CURRENCY              kn                             NLS_LENGTH_SEMANTICS           BYTE        NLS_LENGTH_SEMANTICS           BYTE
NLS_COMP                       BINARY                         NLS_NCHAR_CONV_EXCP            FALSE       NLS_NCHAR_CONV_EXCP            FALSE
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE                          17 rows selected.                          17 rows selected.
NLS_RDBMS_VERSION              10.2.0.5.0

20 rows selected.
All looks more then clear and correct.
NLS_CHARACTERSET is EE8MSWIN1250, what is the default value on every Oracle Windows configuration in mine country. NLS_NCHAR_CHARACTERSET is strong enough to keep all these characters, but something is obviously wrong.
Let me show active NLS_LANG variable, in which command prompt was run:
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>echo %NLS_LANG%
CROATIAN_CROATIA.EE8MSWIN1250

C:\>
Looks correct also, and may say, like it should be in Windows installations, again, in our region.

The Solution

To cut a long story short, I'll show you the main reason of this behavior-Windows code page in command prompt. In regular Windows installation (all defined as Croatia or Croatian), default code page in command prompt is:
C:\>chcp
Active code page: 852

C:\>
Amazing, regardless the whole Windows run in something they call MS Win 1250, command prompt is not in the same code page.
So, changing the code page to 1250 brought me the correct result:
C:\>chcp
Active code page: 852

C:\>chcp 1250
Active code page: 1250

C:\>sqlplus tools/pass@db1

SQL*Plus: Release 11.2.0.1.0 Production on Sri Lis 24 14:34:43 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options

SQL> insert into xxx values (2,'đšžćčĐŠŽĆČ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xxx;

        C1 C2
---------- ----------------
         1 Đ秆źŃ榏¬
         2 đšžćčĐŠŽĆČ

SQL>
Notice that second row, C1=2, is inserted under new 1250 code page, while C1=1 is from previous, 852 code page.
Check in Toad gave me correct result also:
Looks perfect. In all environments is the same.

Make changes to be permanent

To make permanent code page 1250 in command prompt, you have to edit registry. These steps are for 32 bit Windows, so in 64 bit version something may be slightly different:
  1. Start->Run->regedit
  2. Go to [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage]
  3. Change the "OEMCP" value (from "852") to "1250"
  4. Restart Windows

The End

For many users that are Linux based users, this note might not be too important, but I knew few Windows users that were avoiding Oracle in command prompt because mentioned property. Also reason not to upgrade to 11g was nearly the same - missing sqlplusw.exe.
So now there is no need to skip installation of new Oracle 11g Client and start to use sqlplus.exe in all ways ... like you see in this post ...
:-)
Cheers!

Tuesday, October 23, 2012

The folder you entered does not appear to be valid. Please choose another

Using Apex with Embedded PL/SQL Gateway, accessing files and folders on WEB server is possible only through WebDAV folders. Whenever I try to setup a web folder by "adding a network location" or "map a network drive" through My Computer, on mine previous PC with Windows XP it works like hell ... not to mention that Total Commander's WebDAV Quick Connection interface is also very handy and used before really often. But lately, when change OS, I’m given an error saying that either:
  • The folder you entered does not appear to be valid. Please choose another
  • Windows cannot access… Check the spelling of the name. Otherwise, there might be..
These errors appear whether I’m using SSL or non-SSL. I personally have Windows 2008 R2 SP1 64-bit on mine laptop but the same problem exists on Windows 7 as well on x86 or x64 versions when using web folders functionality.
WebDAV problem might be described in two general ways:
  1. Connection to a web folder or web disk cannot be established.
      Whenever you try to setup a web folder or web disk (whether it is through "add a network location", "map a network drive", through the "net use" command, or any other method), it fails (picture above).
  2. You can establish or setup a web folder but can only view contents.
      Here, you can setup a web folder but you can only browse the root folder. When you try to open a folder or transfer a file to it, you won’t be able to. This is exactly what happened to me when tried to open WebDAV folder through mentioned Total Commander's WebDAV Quick connection
If you try to put MS Windows 7 fix, intentionally do not place link because it is in many cases not useful, but if you like to try MS approach, search on WEB for "Webfldrs-KB907306-ENU.exe" file, it doesn't help in Apex case. The reason is that Apex's WebDAV type-not IIS one and, beside all mentioned, totally inappropriate in any Windows 2008 R2 configuration (like in mine case).

The Solution

The free solution come from BitKinex through their WebDAV client.
Regardless this is in fact 32 bit application, it runs on 64 bit as well and, of course, on Windows 2008 R2 x64 servers, what is in mine case very important.
Very intuitive interface as well as pretty strong stability (on some unstable connections it freezes sometimes) gave me power tool for general usage-what i like most. One tool for all operating systems.

The End

This beautiful part of software is really a required tools for any WEB developer, that will soon or later use WebDAV folders in it's development. So this topic was to reduce searching WEB on finding solution.
Hope this helps someone.

Cheers!

After all, new approach ..

According Peer James this is permanent fix of broken Windows registry.
Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\ftp]
@="URL:File Transfer Protocol"=""
"AppUserModelID"="Microsoft.InternetExplorer.Default"
"EditFlags"=dword:00000002
"FriendlyTypeName"="@C:\\Windows\\system32\\ieframe.dll,-905"
"ShellFolder"="{63da6ec0-2e98-11cf-8d82-444553540000}"
"Source Filter"="{E436EBB6-524F-11CE-9F53-0020AF0BA770}"
"URL Protocol"=""

[HKEY_CLASSES_ROOT\ftp\DefaultIcon]
@=hex(2):25,00,53,00,79,00,73,00,74,00,65,00,6d,00,52,00,6f,00,6f,00,74,00,25,\
  00,5c,00,73,00,79,00,73,00,74,00,65,00,6d,00,33,00,32,00,5c,00,75,00,72,00,\
  6c,00,2e,00,64,00,6c,00,6c,00,2c,00,30,00,00,00

[HKEY_CLASSES_ROOT\ftp\shell]
@="open"

[HKEY_CLASSES_ROOT\ftp\shell\open]
Haven't tested, but author claims it works ... I believe him. Thank you Peter again. :-)

Tuesday, October 16, 2012

Reinstall Apex (without loosing data)

Recently I have place that new version of Apex is published and very soon I got untended problem with installation. Here is mine situation and steps that I did:
  1. Have successfully installed Apex 4.1
  2. renamed "apex" directory to "apex_old"
  3. Put new installation in apex directory (good praxis to have production version always in same dir)
  4. Successfully upgraded to Apex 4.2 from "apex" directory
  5. Tested through WEB that new Apex 4.2 is running. All looks nice and all old applications were converted to new version.
So, as any tedious DBA, wanted to remove old 4.1 version, with mine minds somewhere in new Apex interface ... not thinking actually what should I delete in real life.

The problem

Deletion action I perform in next steps:
  1. Shutdown Oracle XMLDB HTTP server, that was upp and running
    EXEC DBMS_XDB.SETHTTPPORT(0);
  2. Wanted to delete APEX_040100 schema using apex apxremov.sql script from "apex_old" directory. This is really script for removing, in mine case, obsolete APEX_040100 schema.
  3. I run it and have notice that it removes not only APEX_040100 schema but FLOWS_FILES schema as well-and this was very bad for Apex 4.2.
  4. Started Oracle XMLDB HTTP server
    EXEC DBMS_XDB.SETHTTPPORT(8080);
    gave me information that Apex WEB page is not running.
The problem arise to serious when I wanted to repeat Apex installation with apxrtins.sql script (again I'm in "apex" dir where 4.2 version is placed), I was told that 4.2 version is installed!
I.    O R A C L E   S Y S   I N S T A L L   P R O C E S S
...create flows user
create user APEX_040200 identified by "0MOYPMY31JG4XJBXNVODAWNCI7REFX" password expire account lock default tablespace APEX42 temporary tablespace TEMP
            *
ERROR at line 1:
ORA-01920: user name 'APEX_040200' conflicts with another user or role name 
The whole installation log file from this failed attempt may be downloaded from here

But if I remove the whole APEX_040200 schema, as advised, I'll loose all mine installed workspaces with mine applications and have to install them manually again after.

This was not a bright idea for me in this moment and wanted to avoid it if anyhow possible.

The Solution

When I analyzed install script I find that there are few places where previous installation is checked. So editing them help me a lot. Here are scripts that you should TEMPORARY modify.

Edit apxprereq.sql

Edit 104 line that exception is not raised. So change (showing the whole block for easy reference):
begin
    if c_install_type not in ('ADD_DEV') then
        for c1 in (
            select null from sys.dba_users where username = upper('^APPUN')
        ) loop
            dbms_output.put_line('Error: This version of Application Express is already installed (^APPUN).');
            execute immediate 'bogus statement to force exit';
        end loop;
    end if;
end;
/
to
begin
    if c_install_type not in ('ADD_DEV') then
        for c1 in (
            select null from sys.dba_users where username = upper('^APPUN')
        ) loop
            dbms_output.put_line('Error: This version of Application Express is already installed (^APPUN).');
-- comment this line            execute immediate 'bogus statement to force exit';
        end loop;
    end if;
end;
/

Edit coreins.sql

Edit 956 line that create user is not performed. So change (showing the whole block for easy reference):
create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;
to
-- comment this line create user ^APPUN identified by "^ADM_PWD" password expire account lock default tablespace ^DATTS temporary tablespace ^TEMPTBL;
You might commented instead line 954
whenever sqlerror exit
... but I prefer mine way, which are in this case the same.

Premature finishing on 10g

On 10g databases I found out that if you run install in thgis moment with your modified installation, it would probably end like:
Application Express Packaged and Sample Applications installation data

...Remove existing packaged applications from SGID11 workspace

begin
*
ERROR at line 1:
ORA-04091: table APEX_040200.WWV_FLOWS is mutating, trigger/function may not
see it
ORA-06512: at "APEX_040200.WWV_FLOW_LOAD_TABLES_T1", line 21
ORA-04088: error during execution of trigger
'APEX_040200.WWV_FLOW_LOAD_TABLES_T1'
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 1400
ORA-06512: at line 11
Interesting, this step is not known on 11g databases (have tested personally) but only on 10g. Workaround is to modify core\trigger.sql script (lines 769-781):
...
    --
    -- last updated
    --
   if not wwv_flow.g_import_in_progress then
        :new.last_updated_on := sysdate;
        :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
        if inserting then
            :new.created_on := sysdate;
            :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
        end if;
        update wwv_flows set
           last_updated_on = sysdate,
           last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
        where
           id = :new.flow_id and
           security_group_id = :new.security_group_id;
    end if;
end;
/
and comment part of trigger which was causing error:
    --
    -- last updated
    --
--   if not wwv_flow.g_import_in_progress then
--        :new.last_updated_on := sysdate;
--        :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
--        if inserting then
--            :new.created_on := sysdate;
--            :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
--        end if;
--        update wwv_flows set
--           last_updated_on = sysdate,
--           last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
--        where
--           id = :new.flow_id and
--           security_group_id = :new.security_group_id;
--    end if;
end;
/

Repeat Apex installation

Now you are able to repeat Apex installation. This installation will make a lot of errors on creation of objects, but this is not a problem because all of them are the same from previous successful upgrade. As an example here is mine 11g installation log file from such a installation. And here is 10g installation log file for comparasion.
When installation finished with end like:
PL/SQL procedure successfully completed.

timing for: Validate Installation
Elapsed: 00:01:51.74
old   1: alter session set current_schema = ^APPUN
new   1: alter session set current_schema = APEX_040200

Session altered.

timing for: Complete Installation
Elapsed: 00:16:31.99

PL/SQL procedure successfully completed.
do not forget to edit previously modified wwv_flow_load_tables_t1 trigger and un comment previously commented part.
SQL> alter session set current_schema = APEX_040200;

Session altered.

SQL> CREATE OR REPLACE TRIGGER APEX_040200.wwv_flow_load_tables_t1
  2      before insert or update ON APEX_040200.WWV_FLOW_LOAD_TABLES
  3      for each row
  4  begin
  5      if inserting and :new.id is null then
  6          :new.id := wwv_flow_id.next_val;
  7      end if;
  8      --
  9      -- vpd
 10      --
 11      if :new.security_group_id is null then
 12         :new.security_group_id := nvl(wwv_flow_security.g_security_group_id,0);
 13      end if;
 14      --
 15      -- last updated
 16      --
 17      --vadas
 18      if not wwv_flow.g_import_in_progress then
 19          :new.last_updated_on := sysdate;
 20          :new.last_updated_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
 21          if inserting then
 22              :new.created_on := sysdate;
 23              :new.created_by := nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'));
 24          end if;
 25          update wwv_flows set
 26             last_updated_on = sysdate,
 27             last_updated_by = nvl(wwv_flow.g_user,sys_context('USERENV','CURRENT_USER'))
 28          where
 29             id = :new.flow_id and
 30             security_group_id = :new.security_group_id;
 31      end if;
 32  end;
 33  /

Trigger created.

SQL> show errors;
No errors.
SQL> 

Configure the embedded PL/SQL gateway

In mine case I was using embedded PL/SQL gateway, so this step should be repeated as well.
SQL> @apex_epg_config.sql c:\oracle\product\11.2.0\dbhome_2

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

old   1: create directory APEX_IMAGES as '&1/apex/images'
new   1: create directory APEX_IMAGES as 'c:\oracle\product\11.2.0\dbhome_2/apex/images'

Directory created.

old  47:     if '&IMGUPG' != '' then
new  47:     if '' != '' then
old  48:         l_mv_folder := '&IMGUPG';
new  48:         l_mv_folder := '';

PL/SQL procedure successfully completed.


Commit complete.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Load Images
Elapsed: 00:01:23.64

Session altered.


PL/SQL procedure successfully completed.


Commit complete.


Session altered.


Directory dropped.
If you use standalone WEB server you might skip this test because all images should be already placed in appropriate folder on WEB server.
Start Oracle XMLDB HTTP server again:
SQL> EXEC DBMS_XDB.SETHTTPPORT(8080);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

And, even it is not required, run recompile database with more then known utlrp.sql script, to ensure that all objects are compiled properly.

The End

Testing WEB page shows that Apex is again safe and sound and running in all shine!
Do not forget to return changes in scripts.
Even thought this method is run on latest Apex, should be able to perform on any version (haven't tested!)-but lines and changes in script may vary. Test by yourself mine idea and place some responds if find appropriate.

Hope this helps someone.
Cheers!

Sunday, October 14, 2012

Apex 4.2 has been released

On Friday, October 12, 2012, Oracle Application Express Release 4.2.0.00.27 has been released.
If it's the first time you look at 4.2, you can read about the many new features which I find more then reasonable reason to make an upgrade to this last version.


Many users will admit-probably best version ever!
Some of them might accent integration of HTML5 and CSS3 and some of them would point on core support for WEB mobile apps-a big new topic in 4.2. But all of us would admit that Responsive Web Design lead to WEB design in a true declarative way. Responsive Web Design is available also on Youtube video as well.
Cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign