Oracle, NLS_LANG, insert and query

To all,

Hopefully I can explain this well enough to get an answer.

My setup:
php 3.0.18
redhat linux 6.1
apache 1.3.20
mod_perl 1.25
mod_ssl 2.8.4
openssl 0.9.6a
unique_id apache module
oracle 8i (on a sun based system)

The scenario:
I'm trying to store and retrieve information for an Oracle 8i database. I'm supporting both a french and english version of the web site using the same apache environment. The user can enter french characters that are stored in the database. Additionally, they can later retrieve these characters from the database. These storage and retrievals are performed using stored procedures. The Oracle 8i database resides on another machine (a sun machine). I'm using the OCI (sql*net) libraries to communicate across the machines. Additionally, I have a web server farm where the retrieval can be performed from a different machine than the storage. I also identify a user's session (and thus maintain state data) via setting a cookie with a value from the unique_id module. This session information is stored in and retrieved from the same oracle database.

The problem:
When I DO NOT have the following environment variables:
export NLS_LANG=American_America.UTF8
export ORA_NLS33=${ORACLE_HOME}/ocommon/nls/admin/data

I can't store the french characters ('é', 'à'...) in the database w/o them getting munged on the way in or out.

When I DO have the environment variables set as above, I can store and retrieve the french characters in the database just fine. However, as I stated above, the session id information is also stored in the database. I know that the session id is stored correctly. However, when the user has switched to a different machine and done the retrieval of the same session id via a stored procedure (verified identical values in the log output), it appears that the query didn't even execute. I have verified that the aforementioned environment variables are also identical on both machines. Additionally, the oracle installations between the 2 web servers is said to be identical from the DBA.

Can someone tell me what is going on here? Should I be using a different NLS setting? Does this value have to match the values as defined in the database server configuration? Hopefully I've provided enough info.

Oracle NLS lang set to Japanese. Insert script fails

We have a requirement, where we need to store Japanese scripts in our table.I did set NLS_Lang settings and I was able to insert the data into the table.When tried Inserting Insert into temp_table ( col1 , col2 , col3) values (' '概算/見積', '新規概算を作成', 20) the script errors out g ...

Oracle Tuning an insert query

I have ...1)a big table(base table ) (60 columns.. 10,00,000 ). And 2)a procedure(test_pro).This test_pro populates a table by insert and store only the PK of base table and user's session id (SID). so this is something happening.......exec test_pro .. takes 4-7 sec... i want it to be 1-2 sec."CAN A ...

Oracle nls lang

i got a problem on a iasdb server running on windows 2000 server, version of the portal is 10gthe problem is that on the old server (9i) a value passed thru to a java applet containing a dot as a decimal seperator,but in the 10g portal it is passed thru as an comma!!strange thing is that everything ...

Oracle problem with insert query

iam working in oracle 9i , i have table named history with the following feildsregno varchar2(10),pname varchar2(10).medicine varchar2(15)I have a the following records in it:regno pname medicine1000 xyz crocin1001 abc metacinnow i want to insert "1000, xyz, amclox" as another record to the same tab ...

Oracle Urgent: regarding insert query problem

i have a one table say "item_master_old" its structure is like item_master_old (item_name, item_rate, item_qty, item_desc)second table say "item_master" its structure is like item_master(id, item_name, item_rate, item_qty, item_desc)i want to insert item_master_old values in item_master using the fo ...

Oracle EXISTS in INSERT Query

I want to insert a record in a table if that value does not exists.Likeselect ip_address into ipadd from server_data where ip_address='100.100.100.100';if ipaddress '' theninsert into server_data values(.......);end ifHow to write a query in a single line? Is there any way to check using exists and ...

Oracle NLS LANG VS CHARACTER SET

Guys,What's the difference between NLS lang and character set? ...

Oracle slow running insert query

SunOS 5.9The query below is running very slow taking more than 28hrs or seeming infinity to complete for about 29,577,975.00 rowsbut before now this usually take approximately 3hrs for about 8,898,113.00 rowsI have rebuild some indexes yet no much improvement.INSERT INTO XXX_YYY_ZZZ(SELECT * FROM XX ...

Oracle error in insert query

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);String query = "insert into log1(converted_time,elapsed_time,client_ip,status_code,data_received,request_method,url,lookup_info,host_ip,object_type,system_time)values("Fields[0]","Fields[1]","Fields[2]","Fields[ ...

Oracle error with insert query

this is the error that i getORA-01722: invalid number this is my codetry{getDBConnection();stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);String query1 = "INSERT INTO LOGS (CONVERTED_TIME, ELAPSED_TIME, CLIENT_IP, STATUS_CODE, DATA_RECEIVED, REQUEST_METHOD, ...

Oracle Syntax for insert query

I want to insert likeinsert into families(id, name) values(1,'Ram's car');but due to 's in Ram's car error is occurring.Plz suggest any solution..... ...

Oracle UPDATE AND INSERT QUERY

I have a table called all_data which is a hybrid of the fault_data and adv_data tables. Each day the all_data is updated and what I want it to do is update each column based on the data held within fault_data and adv_date. Does anyone know how I can do this, I have tried several update queries and i ...

Oracle nls lang

I took full export from oracle 7 and before import it on oracle 8.1.7, I executed catexp7 against oracle 8.1.7 and import the full exported file so there are two question:-1- when I imported the data into 8.1.7 there is some arabic text appeared garbage,despite thatthe value inside NLS_LANG is:- AME ...

Oracle Update and Insert Query

I am having trouble writing the query for to update or insert or both the column in one table with the value from other table after it finds a correct matchSo, below are the create table and sample data.create table code1 ( codeid number, codedesc varchar2(60) ); Insert into code1 values ( 1,'R1 CON ...

Oracle How to insert new element to existing xmltype field using normal SQL query?

Is there a way to insert new element to existing XMLType field (basically update the table) by using usual SQL query??? <PO><ABC>test</ABC><DEF>test1</DEF><GHI><JKL>test2</JKL></GHI></PO> Now I would to add new element under <GHI>??? ...

oracle insert query failing + php

i have a table whose structure is as below:Name Null? Type----------------------------------------- -------- ----------------NEWSLETTER_ID NOT NULL NUMBERSUBJECT_VC VARCHAR2(255)MAILTYPE_VC VARCHAR2(50)CONTENT_TXT CLOBSCHEDULE_DT VARCHAR2(50)SYSTEM_HOOK_VC VARCHAR2(50)NSTATUS_SI NUMBERTEMPLATE_ID NU ...

Oracle insert query takes too much time

I have two select clauses as follows:"select * from employee" This returns me 6000 rows.& I have next clause as"select * from employee where userid in(1,2,3,....,3000)"This returns me 3000 rows.Now i have to insert the result of above queries into same extended list view of Visual Basic. But the ...

Oracle Insert Query - Logic?

Table Name : WLCustAct_EmpStructure and dataCustLifeNo WLActCatId WLDayId WeekNo WLEmpTypeId330374 FRI 1 330374 FRI 1 C330374 M FRI 1 330374 M FRI 1 330374 M FRI 1 C330374 FRI 1 330374 FRI 1 330374 FRI 1 C330374 FRI 1 330374 D MON 1 330374 M MON 1 330374 M MON 1 330374 D MON 1 C330374 MON 1 330374 M ...

Oracle Please help with multiple insert query into nested table!!!!

I am having a problem with inserting multiple references to objects into a nested table using the following query:INSERT INTO TABLE(SELECT Taken_by FROM courses WHERE course_number= 001)(SELECT REF(p) FROM persons pWHERE p.enroled_in = 'Computing for Business'The database says that p.enroled_in is a ...

Oracle insert query

is there a way to ensure that a columns data will not change once its initial value has been added to the database..I have a Date1 column that records the date a record was entered into a row in a table, certain details of the table are changed and some are not, e.g. the Date1 column...I have a prob ...

Oracle how to insert a sql query statement in the table

I have a stored procedure which has different insert,update statements on different tables. I am writing a trigger on these tables such that when the record is updated or inserted in these tables a trigger should fire and should write the old value,new value and the query (insert or update) that cau ...

Oracle possible to insert select query results?

I am wondering if I can insert the result of a select query fromone table into another table. I have 3 tables: emp1, emp2, emp1_emp2, I want to insert pks of emp1 and emp2 into another table emp1_emp2. If I can insert a row into emp1 and emp2 first, then insert their pks into the first column and se ...

Oracle How to insert the query in table by Form?

all, i am new to developer 2000. and am using Forms. i want when i press button, the values which are in my textboxes are stored in table. i know i have to use insert query but how i dont know. i have to write something in WHEN BUTTON PRESSED trigger. can u please tell me how i will write INSERT QUE ...

Oracle Insert query???

In table t1, there areId varchar2(3)status varchar2(5)request xmltypeNow how can I insert the following in table t1. Id= z1status = openRequest=<message><to>123</to><from> 999 </from><body> abc </body></message>Please help me with example & query. ...

Oracle Insert query???

In table t1, there areId varchar2(3)status varchar2(5)request xmltypeNow how can I insert the following in table t1. Id= z1status = openRequest=<message><to>123</to><from> 999 </from><body> abc </body></message>Please help me with example & query. ...

Oracle Insert query

Hi, I guess this is pretty simple, but I can't manage to find out how to do it..I've got a rather slow query that selects a lot of company_id's[code]select (company_id) from company_registry where comp_foo=2[code]I then want to insert each company_id into a table (f.ex company_div), this table has 2 ...

Oracle SELECT query inside INSERT query

Can i have a SELECT query inside INSERT query?insert into temp values((select no from stud), 'xxxx')Like this, can i have a statement? -haifriends ...

Oracle How to increase the performance of an insert query?

I am using oracle database for our application where we executes one insert query and need the performance of 300 insert queries to be executed per second. As of now we are getting only 30/sec.We are not knowing how to tune the database for getting this performance.we are doing all this though C pro ...

Oracle Insert Query

When I execution an SQL query from SQL prompt it is taking 6 sec But when i execute only that query from Stored Procedure and saving the data into a seprate table, then it is taking about 2 min . What was the issue and how can i fixed it? ...

Oracle Insert Query

When I execution an SQL query from SQL prompt it is taking 6 sec But when i execute only that query from Stored Procedure and saving the data into a seprate table, then it is taking about 2 min . What was the issue and how can i fixed it? ...