Preventing Loss Of Data Because Of NLS_LANG Setting?
Oct 28, 2010
I have a database using character set AL32UTF8. The database contains character strings (VARCHAR2 colums) that may contain both Western European and Eastern European characters (and may be even other kinds of characters such as Cyrillic or Asian).
Suppose a client application has set NLS_LANG character set to WE8ISO8859P1. By this Western European characters will be shown correctly, while Eastern European characters which do not compare with WE8ISO8859P1 will be converted and shown as '?' (question marks) in the client application. If a user of this application fetches a record with Eastern European characters, modifies the record and then rewrites it to the database, the Eastern European characters with be rewritten to the database as question marks, i.e. Eastern European data have been corrupted.
I would like to prevent this by detecting that data were not converted properly during the fetch and then show the record to the user in read-only mode in order to avoid data loss, but I have not been able to detect the conversion error.
The application fetches data through the OCI interface using the "ofetch" function. The error code set by ofetch is the same (i.e. no error) regardless of whether the record contains Eastern European characters or not.
I thought I could manage this by setting the database parameter NLS_NCHAR_CONV_EXCP to TRUE, but this has no effect. Apparently this only deals with operations directly in the database.
We would like to remove the partitions from a particular table. The table in question has 12 partitions. Based on some initial investigation, I've come up with the following options. because the table we going to remove partition will have millions of records so on considering the db downtime we are looking for a alternative way. Is there a better way?
Copy data into another table, drop all partitions, then copy the data back into the original table Copy data into another table, drop the original table, then rename the new table and rebuild the indexes.
I am having a few issues trying to set up a physical standby database.
The primary database has a different naming structure than the standby and they are at different sites.
Physical is /var/hpsrp/ctrorp03/oradata/u0x/<DB_NAME>/<DB_UNIQUE_NAME> and standby is /var/hpsrp/drforp03/oradata/u0x/<DB_NAME>/<DB_UNIQUE_NAME>.
I have set db_file_name_convert to '<PRIMARY PATH>', '<STANDBY PATH>' times the number of paths as pairs. I have created a blank database for standby and having taken a full backup of primary with control file and standby control file.
Now I hit the issues:
The DB incarnation numbers are different. When I try and do a normal restore e.g. set dbid, restore controlfile; mount; it fails as it is trying to find files in the primary structure and not standby structure. When I try renaming via set newname or auxname it can't find files to restore I guess due to the incarnation id's. If I set the incarnation id and try to restore backup it fails as the incarnation id's don't match.
I have tried looking through various forums and the documentation but can't find a solution, probably can't see the wood for the trees though as there is so much.
I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.
We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.
if there is a clever way of preventing the same SP to be run concurrently?
Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.
Are there better more efficient/effective ways of doing this?
We are experiencing a problem with SSO causing 2nd or 3rd concurrent Oracle sessions to hang. The Oracle application hangs during loading and the task manager has to be used to close the application.
I have tested logging onto our application servers using SSO and I cannot load more than 3 concurrent Oracle sessions. When I bypass the SSO and logon to the same server I can load more than 20.
Here is one way to create EXCEL file from oracle sql query and prevent excel displaying large numbers in scientific notation(exponential notation)
set feedback off set verify off set heading off spool c:excel_test.xls select 'PO_NUMBER'||chr(9)||'VENDOR_NUMBER' from dual union select '=PROPER('||po_number||')'||chr(9)||'=PROPER('||vendor_number||')'||chr(9) from invoices where rownum < 12 order by 1 desc
Note that PO_NUMBER is 16 characters, VENDOR_NUMBER is 15 characters in invoices table.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "XXX"; ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "XXX" * ERROR at line 1: ORA-28368: cannot auto-create wallet
As you can see the directory is not created.
ls -ld /oracle/10.2.0.1.0/network/admin/encryption_wallet ls: 0653-341 The file /oracle/10.2.0.1.0/network/admin/encryption_wallet does not exist.
The directory above where I want to create my wallet is owned by oracle, which is the user I am running sqlplus as (see below)
I am currently working on a bug that came from a user. An oracle form is used for entering employee information and the backend table is employees. One for the field here is LAN ID. It was found that some users were using space bar either before or after entering the Lan id and this is causing issues in finding the employees in the system. A request is being submitted to not let any blanks or space to be entered in the field. In the table, this particular field/column datatype is varchar2 and the size is 8.
How do I make no blanks or spaces to be entered in the field or otherwise, even if they put a space or blank, the field should only use the actual ID entered.. Is it possible?
now what can i do for set the icon at run time..the first part of the message has been removed as it belongs to @allianz2010's previous topic; the rest has been split into a new.
Recently i faced with a situation of redirecting a report output to a particular path in my server, and then opening the output by using web.show_document.
I was successful in redirecting it too a path , however i am not able to open the file using URL. We should setup virtual path in orion-web.xml and proceed further.
I pointed the output to an existing virtual path as below in my orion_web.xml file.
D:OraHome_2 oolswebhtml
and tried opening the output through the url
<<<server_name>/forms/html/filename.extn>>>
however i was not able to add new virtual path here .
My server version is:- 10.2.0.3.0 Here is the INIT Parameter file.
My question is, how it will affect if I configure the UTL_MAIL package by executing the Scripts ("utlmail.sql" and "prvtmail.plb")? And setting the Parameter SMTP_OUT_SERVER=''?
I have been allocated a new PC. Where is the date format used by forms (6i) set up. I previously used to enter 20110519 in the application and it used to accept. It now does not accept the above format.
The nls_date_format on the database is setup as YYYYMMDD.
We are running a brower based client application in a J2EE container on weblogic, connecting to the database using proxy authentication from a connection pool.
We want to set the logging level in the front end, which should set a PL/SQL package/session variable to the respective value.
Any subsequent calls to the DB layer (stored procs) would then log appropriate messages depending on what the logging level has been set to.
The trouble is that the calls to be database are not persistent and in different sessions, and therefore the second call (i.e. the call to the stored proc) would not 'know' what the first call (to set the package variable) has done.
Alternatively we can change all the stored procedures to accept an input parameter denoting the logging level, but I am sure there are other better options.
I used to click the "export to excel" icon on the menu and it open the excel worksheet with the option for me to save in the specified directory and name. But after my computer have Java v24 updated, when I try to export to excel, it does not give the option to open the worksheet instead it saves the excel file to the C:/users/ directory and overwrite every time I export.
I am reading in a selection of parameters. I have created a new variable which I want to set according to the value of one of the input parameters.
I am doing this straight after declaring the variable, but before the cursors and BEGIN statement It is throwing an error when I do this - but I have to do it before the cursors.the variable I am setting is: v_fptransType you can see the IF statement towards the end of the code.
the error I am getting is:Error(28,3): PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor The symbol "begin" was substituted for "IF" to continue.
beginning of the
create or replace PROCEDURE "P_GLPOST" (i_entity IN varchar2, i_transType IN varchar2, i_startDate IN VARCHAR2, i_endDate IN VARCHAR2, i_accountPeriod IN VARCHAR2, i_includeInternals IN NUMBER, i_chargeable IN NUMBER, i_trialPost IN NUMBER, i_postingReport IN NUMBER, TESTER IN VARCHAR2) is --set serveroutput on size 1000000;
I am trying to set a item to system date + 18 years. What type of formula or where to put such a thing. i tried putting- select add_months(sysdate,216) from dual; into the calculation-formula in property palette with no success.
