Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.
My requiremnt is that a field must contain on Alpha number value, no specail character allowed, how can we sure by query that string which is going to commit is alphanumeric.
I am using Release 10.2.0.1.0 version of oracle. I am getting a special character, but i suspect it as any other language character.
Its appearing in my 'TOAD editor/Sql prompt editor' as 'A?'. but when i am selecting it from the table using below query, its giving 'No rows Returned'.
select id from tab1 where id like 'A%';
How can i be able to see the exact character or which editor will enable me to see the character?
I think sql developer GUI might be able to show the same but i dont have sql developer with me.
I have a pl/sql question about interpreting special characters in pl/sql. I am generating an update statement and get an invalid character when the customer name has an ampersand in it (&). I�m now generating the update statement to include the customer name cut up with concatenation characters surrounding the ampersand, for example �CSCP-C�||�&�||�E�. However, pl/sql is still not executing the update.
Here is the code.
SET PAGES 2000; SET LINES 200; SET SERVEROUTPUT ON; SET VERIFY OFF; spool update_table.log ;
[code]....
The RESULTS follow. Of course, I can copy this statement and execute in sqlplus without a problem.
This is the cursor which fetches from View uk_case_recommends_vw and this view calls the table ukcc_case_casenames below is the query
cursor cur_case_name_search is select a.*,DENSE_RANK() OVER (ORDER BY sc desc) rank from uk_case_recommends_vw a where rownum < 2 + 3; SELECT /*+first_rows(4) index(u ukcc_case_casename_idx)*/ persistentid, casename, FROM ukcc_case_casenames
Here in this table my case name may be with special character or normal .
For Ex ('R V Wilson') - With Special character ('R V Wilson') - Without Special character
Actually my cursor is not fetching with special character ..neither it is not mentioning as no data found. Find the piece of code below.. I am not getting values after opening the cursor.
open cur_case_name_search; loop fetch cur_case_name_search into v_case_name_row; exit when cur_case_name_search%notfound or v_case_name_row.rank > v_matchingvalue or [code]......
i want to store special characters in database. i have tried following but not succeed. how to store and retrieve it. Herewith i'm posting my try.I want to store and retrieve following character.
We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"
If i use workaround by using chr(255) function i am getting below behavior.
select chr(255) from dual --------------------------- ÿ
A database containing inventory data has been migrated from Oracle 10g to Oracle 11g. I have access to both the Oracle 10g and Oracle 11g database on different client computers. Both databases use the same character set, WE9MSWIN1252 (query shown below). However the results from the sql SELECT show incorrectly displayed characters. I would like the "1/2" character and degree character to show in the text. The ASCIISTR function shows that the underlying ascii is the same in the two copies of the databases.
Is there a setting that needs to be changed in Oracle 11g so that the saved special characters in the database show correctly (as in Oracle 10).
Query of database character set
SQL> Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET' WE8MSWIN1252
Under Oracle 11g, this is a query on DSI using SQLPLUS 11.2.0.1.0.
SQL> select description from part where id = '57234';
DESCRIPTION ---------------------------------------- KL BRKT PLN 22╜░ ANGLE (AMER BOT RAIL) SQL> select asciistr(description) from part where id='57234'; ASCIISTR(DESCRIPTION) --------------------------------------------------------------------------------
I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.How can i prevent it to load as it is not present in the file
data in file
Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8
the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.
"SELECT xmltype(RCATT).extract('/JASON/RCA/SUB_PROBLEM_TYPE').getStringVal() as SPT, xmltype(RCATT).extract('/JASON/RCA/SUB_ROOT_CAUSE').getStringVal() as SRC, xmltype(RCATTT).extract('/JASON/RCA/ELABORATION').getStringVal() as ELA, xmltype(RCATT).extract('/JASON/RCA/PREVENTION').getStringVal() as PRE FROM table"
Above is my select statement
some explanation: the above select statement is to select the child node inside the xml content. But the child note contains some special character "<>" that illegally in xml.
Below is my xml content for the node of sub_problem_type
<JASON> <RCA> <SUB_PROBLEM_TYPE> For <FS_PLS_WAIT>, it is appeared in both valid and invalid password entered case but in requirement, only valid case has stated the display of <FS_PLS_WAIT>. This is the legacy radio behavior. </SUB_PROBLEM_TYPE> </RCA> </JASOn>
The result will generate the following issue.
ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00225: end-element tag "ELABORATION" does not match start-element tag "FS_PLS_WAIT" Error at line 10 ORA-06512: at "SYS.XMLTYPE", line 301 ORA-06512: at line 1
I wish to know where can i by pass the checking for special character.
How to handle special charater '&' in stored procedure.
Following is a test code I want to Implement
CREATE OR REPLACE PROCEDURE SP_Test(QueryID NUMBER,DateFirst DATE,DateLast DATE) IS BEGIN INSERT INTO TempTable(QueryID,LineID,data,Datetime) SELECT 1,6,Emp,startdate FROM salary
[code]....
The code above resuts into INVALID state of stored procedure and cannot be executed.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS where parameter = 'NLS_CHARACTERSET'; PARAMETER VALUE ------------------------------ ---------------------------------- NLS_CHARACTERSET AL32UTF8 SQL>
There is table (VIN_TEMP) in my company database containing following records. It seems like this table should contain some greek language special chracter values instead of this weird data.
Client are reporting these records as invalid and requesting us to fix. As i investigated i found out, this table was created and loaded few year back. Client sent us one time files which we loaded into this table. I was able to find the code which was actually used to load this table, but unfortunately i was not able to find the raw files where we load this data from...
It seems like Previous Developer specified character set "UTF8" statement, in his sql loader script, to load this data. It seem those file contain some Greek language special character data which was not support by "UTF8" charater set and result in creating those invalid data. My Job is to fix these invalid records and convert them back to its original values which were present in the raw file. I tried to contact client and see if i can find out the raw files but no luck. I tried to use convert function as mention to convert this data from "UTF8" to our current character set format but no luck.
I was playing with different CHARACTERSET, but some special characters e.g. "greater than or equal" do not get loaded/displayed correctly in the database. Also tried changing NLS_LANG registry key and following some advices in the Oracle doc
we have a Oracle 11g database, but it does not support UTF8 character set. Now we have received a file which contains records with Trade Mark symbol (special character). Now when we are trying to load the record, it is getting loaded with a sign "?". As a result when we try to display record in web application, the application is not able to show the records properly.
We have migrated the database from Oracle 10.1.0.3 to 10.2.0.5 by using conventional export and import. (OS - HP.Uinx).,After the migration some characters (like é or à or è) have not been migrated correctly.
how to validate this characters after migration?
Note:
All NLS parameters are same in both source and target database
I dont know why, but my PL SQL Developer keeps changing special character as "ã", or "ç", to "?". And As I use these letters because of my language, I need a way to solve this thing.
create or replace PACKAGE TABLE_PKG1 AS PROCEDURE make(table_name VARCHAR2, colspec varchar2); PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2); END TABLE_PKG1 CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS
[code]....
The package and the package body compiles successfully. When I add table-
BEGIN TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)'); END;
The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-
SET SERVEROUTPUT ON BEGIN TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency'); END;
The it generates the following error-
Error report:
ORA-00984: column not allowed here ORA-06512: at "SCOTT.TABLE_PKG1", line 14 ORA-06512: at line 2 00984. 00000 - "column not allowed here"
emp_id number, name varchar2(30), from_dt date, remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark'); insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark'); insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
SQL> insert into t51 values (100000000000000000.00000); insert into t51 values (100000000000000000.00000) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column
I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
