i have to enter pan_no through my form into database, and pan_no format is like BWHPK2334M as first 5 is alphabets then 4 letters and last one is alphabet, how to validate it in my form. can i do this by set fomat mask in property palette and if yes then how, oterwise the 2nd option may be is trigger when validate item, but with which format i should match the entered data.
I have developed one rdf in Text Output Format.In this some special characters is coming for Text Output format of rdf.Shall i do any adjustments in layout? How to remove these special characters?
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?
I have set NLS_NUMERIC_CHARACTERS to ',.' but somehow during my java application life-cycle it got changed to '.,'! Is there any way to find what causes this? I can't find what or who change it. I have ordinary Java app which connects to Oracle 11.2.0.1.0 DB and as far as I know NLS_NUMERIC_CHARACTERS is not set explicitly or any other NLS_XXX setting. Is there any way to look in some logs for this?
I have a XML file which contains Japanese characters and it is parsed by a UNIX script and nawk utility and writes the data to a flat file separated by delimiter. I could the Japanese characters proper in the flat file.
I use SQLoader (within a unix script) to import this data from the flat file to Oracle database. If I view the data in the database through Toad, the Japanese characters are showing differently (not as in XML or in flat file).
But if do a export for the particular table to a flat file through Toad, I can see the Japanese characters proper in the exported flat file.
(Note : I have set the env variables NLS_LANG=Japanese_Japan.JA16SJIS, LC_CTYPE="en_CA.UTF-8" in both XML parser and the loader script)
Why I couln't see the Japanese characters while viewing through Toad.
Here's an odd problem. I'm trying to load German characters positionally (not CSV) using Linux 10g. I don't get this error on Windows or via CSV, but I'm bound to the method and platform.
The problem is simplified thus. I have 2 columns, the 1st varchar2(8) and the 2nd a numeric(3). The error I'm getting is Invalid number only on rows with special characters. Let me demonstrate.
The file has been loaded into Linux and corrected using iconv.
The 1st rows is accepted, but the second fails in sqlldr with
Record 2: Rejected - Error on table TEMP1, column FLD2. ORA-01722: invalid number
The logical assumption is that the double width character is not being properly read by sqlldr but I can find no advice on other setting. My nls parameters look like this.
PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE ENGLISH NLS_TERRITORY UNITED KINGDOM NLS_CURRENCY # NLS_ISO_CURRENCY UNITED KINGDOM NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE ENGLISH NLS_CHARACTERSET UTF8 NLS_SORT BINARY NLS_TIME_FORMAT HH24.MI.SSXFF
Try to send a query to a remote sql server (Oracle DB I guess). I found dbms_xmlgen which generate xml output, very useful, but sometimes lines are trunk and xml tag as well.
------------------------------------------- query ------------------------------------------- set pages 0 set linesize 150 set long 9999999 set head off select dbms_xmlgen.getxml ('select * from (select no,subject where...)') xml from dual ; quit
------------------------------------------- extract of the output -------------------------------------------
I have one column name party_name containing Korean Characters and English characters.Some of the English characters have different symbols.My requirement is to get the data and exclude those symbols but not Korean characters.
Already I used a function to replace special symbols with space.The function contains code based on ASCII values it works good but it filters Korean characters too.the attachment of the screenshot, When I double click the name it shows with some question mark.
We are using Release 11.2.0.3.0 of Oracle. I am having below special characters inserted into one of my columns, how this value got inserted, (what is the source) i need to track it down.
We dont have any audit trigger on this table to track one level below. As per JAVA guys this is uploaded through a file and the file is having well defined characters and no special characters for this column value also they uploaded the file again but its now going fine with no such special characters. So they put it on DBA's to find how special characters came into database?
Again the editor is not recognising all the characters , so i got the ASCII value for each of the characters in the string, its as below.
String - ‡Mw‹O--ggÑ ÆÔéÓÞ³ µmT¤O ˆÓ`ôiyïÎ!Ž ASCII character is : ‡ ASCII Value Is : 14844065 ASCII character is : ‹ ASCII Value Is : 14844089 ASCII character is : -- ASCII Value Is : 14844052 ASCII character is : ASCII Value Is : 49793 ASCII character is : Ñ ASCII Value Is : 50065
I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.
How can i do that ?
i have a string with |R!$#&2-_D%2 and i want to get R2-D2
SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct FROM DUAL
problem is that when i call run_product to generate a report from a form i have the name of employee in arabic characters appear in wrong form. yet when i use query from forms directly or from reports directly. name appears correct.
i want the arabic charcters to appear correct when i call to show report from a form..
I am doing some ETL that I need to run "faster". The function in which I am interested removes low ascii code characters from a string. Please see the timing below and the definitions of the of the functions below those. I am selecting just the first 100K rows for testing and timing purposes only. In production, we are doing millions of records several times a day, thus the desire for "faster". Selecting with no functions is very fast, 0.2 seconds. We would really really want to convert at least 100K rows per second.
The best I can do is get it down to around five seconds using clear_nonlegal. That is, ironically, the one that I thought would be the slowest. It's making thirty-one calls to REPLACE. I would have guessed that the other two would be much faster. I am guessing that REPLACE is just much better optimized than TRANSLATE and, of course, my homegrown PL/SQL, which isn't optimized at all.
So, my question is thisif there is a way I can optimize my custom function, or maybe know of a better already optimized standard SQL and/or Oracle function that would do the job? I am thinking about trying to use a Java stored procedure, but I have never done that before, I am not currently set up for it, if it would be any faster anyway. Is Java faster with string manipulation the PL/SQL? I am thinking it would be really fast to call a C method,
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 Connected as aggs@AGGSTEST
SQL> set timing on SQL> SELECT COUNT(*) 2 FROM (SELECT DISTINCT keyword_dest_url 3 FROM se_keywords sek
I am not sure if the problem is related to hidden characters but its my best guess so far. I am trying to enhance a part of the ERD by creating a lookup for a column one of the table that uses text (finite set of values).
CREATE TABLE N_AGREEMENT_STATUS ( STATUS_ID NUMBER(2) PRIMARY KEY, STATUS_NAME VARCHAR2(10 BYTE) ); INSERT ALL INTO N_AGREEMENT_STATUS VALUES (1, 'INACTIVE') INTO N_AGREEMENT_STATUS VALUES (2, 'ACTIVE') INTO N_AGREEMENT_STATUS VALUES (3, 'CLOSED') INTO N_AGREEMENT_STATUS VALUES (4, 'CANCELLED') SELECT * FROM DUAL;
when I try to update the source table no update takes place (0 records updated) if I used the following statement:
ALTER TABLE N_AGREEMENT ADD STATUS_ID NUMBER(2); UPDATE N_AGREEMENT SET STATUS_ID = ( SELECT STATUS_ID FROM N_AGREEMENT_STATUS WHERE N_AGREEMENT.STATUS = STATUS_NAME );
but it works fine only if I used:
UPDATE N_AGREEMENT SET STATUS_ID = ( SELECT STATUS_ID FROM N_AGREEMENT_STATUS WHERE N_AGREEMENT.STATUS LIKE STATUS_NAME || '%' );
The strange thing is that when I use:
SELECT N_AGREEMENT.STATUS, N_AGREEMENT.STATUS_ID FROM N_AGREEMENT WHERE N_AGREEMENT.STATUS = 'ACTIVE';
it returns correct results and all status = 'ACTIVE' appear correctly!
I am working with Oracle 10G, and have been working on setting up little pl/sql checks to make sure that the data that is imported is in the correct format.
The wall I have hit is removing illegal characters from the data I import. I have started to set something up where the string for a certain column must be be made of only there characters:
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-" (note that there is a - besides just letters) and I may want to add some other characters later. So basically the script will drop or replace any character not found in my definitions with "", thus removing the illegal character and joining the previous and next characters.
I thought for sure there would be a script posted somewhere online that did this but I can't find it and my syntax skills are lacking.
I am using the below query to get the persons details for shell script. Its throwing the error like string not properly terminated but the issue is due to the data is having special characters like below.
select first_name||last_name from employees;
Data output is
O'brainsteve Stevebross
how to remove these special characters like ' in the query string.
I am using Oracle Version 11.2. Here's an example of what I am trying to do..
-- create table with a clob column create table sr_test (c1 CLOB)
-- load data that is more than 4000 characters into clob declare var1 varchar2(32000); begin var1:= '';
for i in 1..5000 loop var1:= var1||i||',';
end loop; dbms_output.put_line(var1); insert into sr_test(c1) values (var1); end;
-- select table to make sure clob is loaded
select c1, dbms_lob.getlength(c1) from sr_test
-- create procedure to return data from table
create procedure sr_p1(result out sys_refcursor) is begin open result for select c1 from sr_test; end;
-- run the procedure to get data
DECLARE RESULT sys_refcursor;
BEGIN RESULT := NULL;
ACCOUNTING.SR_P1 ( RESULT );
:rc0_RESULT := RESULT;
END;
Everything works as intended. However, this procedure is being called from Webservices. According to what I have been told, webservices adds 18ms for each clob that needs to be converted into char so it can be displayed on the screen. So, I need something like this
create procedure sr_p1(result out sys_refcursor) is begin open result for select dbms_lob.substr(c1,32000,1) from sr_test; end;
Is there an alternate method to send more than 4000 characters in the refcursor?
I am using an Oracle view and package to extract data from my DB to build an XML file on the fly.
My problem is that on very rare occasions, an invalid XML character will be in the database. This, of course, causes my XML file to error.
My question is: What are the possible ways to remove invalid XML characters when selecting out of a DB field? Any function that has been written for this type of thing, or is a VERY long 'translate' more of what I should be doing?
trim down the following sql to within 255 characters help: select indate from ( select case count(inputDate) when 1 then inputdate end as indate from commLeaseBut5
[code]...
This sql is check a date field in the database for record which, if the date field is blank it should be a new record. Then the sql will assigned the current timestamp and stored to the new record. Otherwise, the sql will return the record timestamp for display.
I have a table called temp which has nearly 20 columns when i insert values in some columns then null is inserted successfully in remaining column except one column,this column takes string of unexpected characters(like-"inverted question mark") which are not exist on my keyboard, how it is possible. it is happening only in this column and this is only one column of "nvarchar" type , is this because of it(datatype)?
