I have a macro in Excel that generates a flat SQL file with INSERT statements to be executed from an Oracle Forms form, by means of an invocation to sqlplus. I am the developer and in my team everything works correctly, but the people who use this mechanism have problems with coding when executing the inserts. The process is as follows: the macro is executed on a filled Excel. The macro generates .SQL files with a series of instructions to load the data in BD. When you open the SQL file, the characters are displayed correctly (accents and special symbols).
SET ECHO OFF;
DELETE FROM TABLATEMP;
INSERT INTO TABLATEMP ( CAMPOS ) VALUES ('E126',301,'Un código de producto','Una descripción con acentos y símbolos especiales como Ø',1,Null,Null,0,0,Null,7);
...
COMMIT;
QUIT;
Then the ERP is started, and from the corresponding screen, when clicking on a button, a sentence is sent:
HOST('SQLPLUS USU/PASS@INSTANCIA @' || v_nomfichero );
The coding of the BD is:
PARAMETER VALUE
NLS_LANGUAGE SPANISH
NLS_TERRITORY SPAIN
NLS_CURRENCY?
NLS_ISO_CURRENCY SPAIN
NLS_NUMERIC_CHARACTERS,.
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD / MM / RR
NLS_DATE_LANGUAGE SPANISH
NLS_SORT SPANISH
NLS_TIME_FORMAT HH24: MI: SSXFF
NLS_TIMESTAMP_FORMAT DD / MM / RR HH24: MI: SSXFF
NLS_TIME_TZ_FORMAT HH24: MI: SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD / MM / RR HH24: MI: SSXFF TZR
NLS_DUAL_CURRENCY?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.4.0
The characterset must be the one that is seen to allow compatibility with the version of OracleForms with which the ERP (6i) is developed. I have the Developer tool installed for Oracle Forms and Reports 6i, as well as the SqlClient version 11 and an instance of OracleXE11 and probably some rest of something else. Users have installed the Oracle Forms runtime and Reports as well as the Sql client of version 11.
Does anyone know how I can force the execution of the script to use correct coding? Or some of what I can do on users' computers to make sure that the encoding with which they connect to the BD is correct. The users are few, so I can alter files on their computers if necessary.
Thank you very much.
EDIT: I have tried adding an ALTER SESSION at the beginning of the script and it has not worked for me either. The Alter Session has been:
ALTER SESSION SET NLS_LANGUAGE= 'SPANISH' NLS_TERRITORY= 'SPAIN' NLS_ISO_CURRENCY= 'SPAIN' NLS_NUMERIC_CHARACTERS= ',.' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MM-RR' NLS_DATE_LANGUAGE= 'SPANISH' NLS_SORT= 'SPANISH'
I have filled in the values as they are defined in the DB (the list of values I have placed above).