PL/SQL :: Retrieving Columns With Non-english Characters?
Nov 26, 2012
I'm running a query using SQL Developer on a table which contains several companies names from many different countries, and one of the checks I need to make to ensure data consistency is to search for all rows which the name of company contains special or non-english characters (like ç, ã, ä as example).
I don't know what can I use to do this. I tried to collate using NLS_SORT but it didn't work.
Is there someway to select only the rows that contain these special or non-english characters, excluding from the results the rows that only have english characters? We have many languages in this table.
The field I would like to make the conditions on is VARCHAR2.
View 21 Replies
ADVERTISEMENT
Oct 30, 2011
I want written English characters only in text item (I want control in one text item), in my form 10 g but, in same form can be written other language in other text item.
View 1 Replies
View Related
Nov 6, 2010
i have a column full name which has the name of customer in both English and Sanskrit alphabets. I need to susbtring only the English name and exclude the Sanskrit name .
View 2 Replies
View Related
Jul 23, 2013
create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');
how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?
so my o/p be like;
Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')
View 6 Replies
View Related
Jun 2, 2011
My client is using oracle 10 r2 on window 2003 server with arabic characterset WE8ISO8859P1. I am facing problem to understand the language specially in the case if any error occur it display me unknown characters.Is there any way to display it in English language??
I have also tried to change it in registry i.e changing NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1 but no success.Below are the parameters value of my database.also attach snapshot of sqlpromt.
PARAMETERVALUE
NLS_LANGUAGEAMERICAN
NLS_TERRITORYAMERICA
NLS_CURRENCY$
[code]...
View 1 Replies
View Related
Feb 1, 2012
I need to prepare a bilingual system (English and Arabic).
So i need to store data in English as well as in Arabic
Is there any possibility to convert the data from English to Arabic and Arabic to English through oracle forms
basically what i am thinking is let us suppose user selected the English version of the application and he entered all the data in English during saving the records i need to convert all the English data into Arabic and store it in another table for Arabic data reference.
View 3 Replies
View Related
Apr 17, 2011
I need Code or Procedure which can convert English Names to Arabic Names in Oracle.
Like Given Student ID it fetch Student Name But I need to Convert this name to Arabic.
View 39 Replies
View Related
Apr 18, 2010
Which is changing language in form from English to Arabic and vise versa during run time when I validate from item to another one ..
View 5 Replies
View Related
Mar 1, 2010
I am a programmer in Oracle PL / SQL in Oracle 10g I'd like to use the same time on one Database two types of fonts English and Russian (Cyrillic). Is this possible and how? NLS_LANG ?
View 2 Replies
View Related
Apr 24, 2013
I have a problem in Oracle Forms 10g. For any field that should be by default right-to-left (arabic), the reading order is changed when entering the field, but the keyboard is not changed to Arabic automatically. The user has to type ALT+ Shift to manually modify the language.
I worked before with Forms 2000 and Forms 6i (in client/server mode only), it was working fine. Is it impossible on Web Forms? Must I use a Java Bean? If yes, is there something existing to perform what I need?
View 15 Replies
View Related
May 9, 2010
how i cam CONVERT ' FORM 6i default menu ' from english to arabic?
View 3 Replies
View Related
Sep 19, 2011
I have a table having data in different languages like English, Japanese and Chinese. I need to retrieve only those rows which are in Japanese. What all settings do I need to make. When am doing a normal select, rows in languages other than English are appearing as Junk data.
View 8 Replies
View Related
Oct 5, 2012
I have a table. It's name is INSTITUTION. It has a NUMBER INS_ID and NVARCHAR2(50) INS_NAME . INS_NAME can contain Turkish characters, such as "ğ,ü,ş,ç,ö". According to business logic, there can not be a repetition on the INS_NAME.User will enter institution name from a textbox in ASP.NET , and I check this name in database from c sharp code, if there is no repetition, we will add this record.
The problem is; when user enter a instition name that contains Turkish character, there is a duplication. If there is a instition name is *"su işleri"* , the both query; SELECT * FROM INSTITUTION WHERE INS_NAME = *'su işleri'*; and SELECT * FROM INSTITUTION WHERE INS_NAME = *'su isleri'*; returns no result, even though there it is.But if instition name is "oracle corporation" (there is no Turkish character) it query successfully. I have the same problem in Toad for Oracle 11.5.1.2. When I query database from toad SELECT * FROM INSTITUTION, the phrase *"su işleri"* has appeared. But when I query SELECT * FROM INSTITUTION WHERE INS_NAME = *'su işleri'*; , there is again no result.When I connect oracle database directly and perform the query SELECT * FROM INSTITUTION , the phrase *"su isleri"* (not *"su işleri"* ) has appeared.
Here are the language settings of the database:
National Language Support
National Language Parameter Value
NLS_CALENDAR______________GREGORIAN
NLS_CHARACTERSET__________WE8MSWIN1252
NLS_COMP__________________BINARY
NLS_CURRENCY______________TL
NLS_DATE_FORMAT__________DD/MM/RRRR
NLS_DATE_LANGUAGE________TURKISH
NLS_DUAL_CURRENCY_________YTL
[code]....
View 8 Replies
View Related
Jul 8, 2009
I have the following tables:
REQUEST
----------------------
request_id
item_name
unit_of_measure
UOM_CODES_TL ("tl" means translation - this table has the same record 3 times, once for English, once for Spanish, and one for France)
----------------------
unit_of_measure
description
language
[code]....
So what I'm trying to do with the outer join ((+)) is to get the request record, regardless of whether the r.unit_of_measure is null. That works as long as I don't include the underlined portion above. As soon as I do that it only returns results where r.unit_of_measure is not null. That all makes sense to me, but I'm wondering how I can restrict the values to English without requiring that a value exist in the request table.
View 4 Replies
View Related
Aug 5, 2011
how can convert number from Arabic to English in oracle report builder
View 2 Replies
View Related
Apr 12, 2008
I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.
I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.
I want the Student name, the Lease details and the Room No from these tables.
The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.
SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO
FROM STUDENT
LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;
View 3 Replies
View Related
Dec 14, 2011
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
View 2 Replies
View Related
Dec 20, 2012
i have created two data blocks
First data data block contains a list item and a text item based on the selection of the list item and the value in the text item i need to retrieve more than one row in another data block whose NUMBER OF RECORDS DISPLAYED property are set to 10.
I have a Button in the first data block. So in the WHEN BUTTON PRESSED trigger i wrote SELECT INTO clause which is raising "exact fetch returns more than requested number of rows".Then i used a cursor in the WHEN-BUTTON-PRESSED trigger in the first block to fetch row by row and assign it to the items in second block. But i am able to retrieve only one record in the second block.
View 5 Replies
View Related
Dec 2, 2010
My table XXX has following records with data
ID AMOUNT ID_TYPE APPROVE_FLAG
A1 2 A N
B1 100 B N
A2 3 A N
A3 100 A Y
The Select Query should be..All the records should be considered and if the (amount is greater than 50 and approve_flag is N )then except that records all records should be considered.The output should be 3 rows; 2nd row (B1) should not come. writing a select statement for the above conditions.
View 4 Replies
View Related
Sep 29, 2011
first of all sample data;
create table test_circular_data(c1 varchar2(10),c2 varchar2(10));
insert into test_circular_data values ('c1','l2');
insert into test_circular_data values ('c1','l3');
insert into test_circular_data values ('c3','l3');
insert into test_circular_data values ('c4','l3');
commit;
There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :
c1--> l2 --> l3 --> c3 --> c4
The steps to get that result is :
1.- c1 related to l2 : c1-->l2
2.- c1 related to l3 : c1-->l2-->l3
3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3
4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4
View -1 Replies
View Related
Mar 21, 2013
i have requirement, that is to retrieve the data from pl/sql table.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
[code]....
PL/SQL procedure successfully completed.now i want receive the data from particular x record to y record
View 8 Replies
View Related
Dec 14, 2011
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
View 6 Replies
View Related
Nov 3, 2011
I have one table when I am querying like below
select * from timeoffreqitem
where timeoffreqitemid=134
getting data like below
134 144 07-OCT-11 13-OCT-11 134
I need to see this result as below.
134 144 07-OCT-11 13-OCT-11 134
134 144 08-OCT-11 13-OCT-11 134
134 144 09-OCT-11 13-OCT-11 134
134 144 10-OCT-11 13-OCT-11 134
134 144 11-OCT-11 13-OCT-11 134
134 144 12-OCT-11 13-OCT-11 134
134 144 13-OCT-11 13-OCT-11 134
I am looking at multilple optons.
View 1 Replies
View Related
Sep 6, 2012
I have two blocks on which I am having relation based on booking number and employee code. As I am doing a query, booking number is getting fetched in employee code's field and employee code is getting fetched in booking number's feild. Because of this, I am not able to update the form. It is giving me error unable to update record unique contraint voilated for primary key which is quite obivious. But why the data could get shuffled in each others field though during inserting its going properly.
View 7 Replies
View Related
Mar 27, 2010
I want to retrieve the data from ex.tables. How to get this.
table1:
Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.
Out put:
I want the data like Account_no who is having more than 3 Account_sub_no values.
But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.
View 5 Replies
View Related
Mar 7, 2013
I have a requirement like to retrieve past 6 months data and i have used below query.
select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate);it is giving the results but am not sure whether it is giving correct data or not.If ran the above query it is taking more time to execute.
Is the above approach is correct? Is there any difference between trunc(sysdate)-180 and trunc(sysdate-180) ?
View 12 Replies
View Related
Mar 12, 2008
I have a table of N records with: Name SeqNo ID Col4 ... ColX
where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the
values of any of the other columns. For instance, if the table contained:
NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...
I'd like to generate:
NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...
I got my desired results by brute-forcing via four sub-queries:
Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo
if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.
View 4 Replies
View Related
Apr 2, 2010
I want to retrieve the data based on the count.
Sample data
AccountSubAccountDate
11.12-Mar-10
11.23-Mar-10
11.34-Mar-10
11.42-Mar-10
11.56-Mar-10
21.67-Mar-10
21.78-Mar-10
21.82-Mar-10
3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10
42.616-Mar-10
42.717-Mar-10
What i want is I want the data which account is having more than 5 subaccounts within the last one month. Also need data with other date criteria like last 100 days if more than10 sub accounts. need single query.
Output is :
3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10
Having more than 8lacks in my database. i wrote the query but it is taking much time and didnt give the data even after 14 hours.
My query is :
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-35
group by b.account having count(b.subaccount)>5)
union
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-100
group by b.account having count(b.subaccount)>10)
how to retrieve the Subaccounts which satisfies my requirements.
View 33 Replies
View Related
Apr 2, 2010
I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.
View 3 Replies
View Related
Jul 30, 2010
I want if the user write for example in text box 'AM TK' the query display the resualt which has am alone tk alone, and that has both. I know that i should use the Like with % but i do not know how to write it in the set property. I have wrote
set_block_property('Employee_Other',default_where,'Upper(name) like '''||UPPER(:key_search.person_name)||'''') ;
This will bring the resualt just if the user write am tk. How i can modify it to return value as i explained above.
View 6 Replies
View Related
