SQL & PL/SQL :: Replacing Multiple Characters?
May 21, 2013
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
[code]...
View 18 Replies
ADVERTISEMENT
Dec 15, 2010
The following query calls the function get_meter_desc 8 times.
SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...
How can I replace the function call by the join in the main query?
View 19 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
Jul 26, 2011
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
following is my trial:
create table abc
(
pk_id number(4) primary key,
abc_name varchar2(10),
fk_id number(4)
);
create table xyz
(
x_id1 number(4),
x_id2 number(4),
x_name varchar2(10)
);
commit;
insert all
into abc values (10,'ab1',1)
into abc values (11,'ab2',2)
into abc values (12,'ab3',5)
into abc values (13,'ab4',7)
into abc values (14,'ab5',9)
into xyz values (1,1,'d1')
into xyz values (2,2,'d2')
into xyz values (5,3,'d3')
into xyz values (7,4,'d4')
into xyz values (9,5,'d5')
select * from dual;
commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2
-- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id);
commit;
select * from abc;
View 3 Replies
View Related
Apr 23, 2010
Is there a way to replace a field with another if the particular value returned is null?
View 11 Replies
View Related
Jul 25, 2012
In forms 11 g, whenever, We did any menu change, and deploy in server, same changes are not reflecting in clients machine immediatly. I even stop forms services and restart.After some time... generally after 1 day, it reflect the new changes.In forms 10G, I never face this issue. As soon as i close the old menu and re-open, new changes reflect.
View 2 Replies
View Related
Jun 22, 2010
I am facing a simple problem, but could not resolve as yet, i want to replace two string 'M/S' and ' " ' with null, i know this command SELECT REPLACE(' " M/S Private linker " ','M/S',NULL) FROM dual Which command i should use
View 3 Replies
View Related
Apr 13, 2013
I am looking to replace the vowels in a string, and get rid of the empty spaces afterwards.
Our Lab Book suggested that we use the REPLACE function, but I have only been able to get it to work using the TRANSLATE function.
Also, I can`t figure out how to get the empty spaces out of the string when output, I tried to use the TRIM command unsuccessfully.
1) Is it possible to use REPLACE instead of TRANSLATE to replace the vowels?
2) How would i get rid of the empty spaces when the vowels have been replaced?
CREATE OR REPLACE PROCEDURE replacingvowels (vowels IN VARCHAR2, var IN VARCHAR2)
IS
vowelreplace VARCHAR2(50);
BEGIN
[code]....
View 5 Replies
View Related
Nov 15, 2012
I did an expdp on the prod DB and have been doing a straight impdp as a test, just to have data to work with, but it spews 214 errors. Mostly these:
ORA-31684 lots
ORA-39083 some
ORA-39151 lots
ORA-39082 lots
ORA-39111 lots
ORA-39112 lots
I can see that I can use REUSE_DATAFILES & TABLE_EXISTS_ACTION to overwrite tables by default, but is there a recognised way of replacing the entire DB with the impdp? Do I just create the instance, (with the init file) and not build/init the tables, or what? I'll experiment, but I'm just interested if there is a DBA best practice for this sort of thing.
View 3 Replies
View Related
May 16, 2013
why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
View 5 Replies
View Related
Jan 11, 2011
Need to create or replace a Standard Sunguard package. When I'm executing, It is asking some series of parameters. when I cancel It is executing but the Web page is not opening giving error like PL/SQL: could not find program unit being called . Not understanding the exact error.
View 5 Replies
View Related
Jan 31, 2013
Getting starting with diving deeper into APEX and reading the developers guide all 800 pages of it! Thing is I'd like to get started with some of the more interesting stuff.
I have created a form page based on the "Form on a Table with Report". The report part looks fine. What I'd like to do now is use an API I've built to change how the insert and update works. So when the submit button is pressed it rather performs an API call to the procedure/function.
1. Should the API be a function or procedure. In my playing around I saw that a procedure might work better or easier to implement
2. How would I go about creating a call for the button to use instead of the current processes?
----------------------------
Apex 4.2
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Linux version 2.6.32-279.19.1.el6.i686
----------------------------
View 4 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
Jun 17, 2010
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
View 7 Replies
View Related
Jul 9, 2012
NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'
File :-mauri.export.ngf
NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD
Required output:-
AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc
CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......
find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.
View 6 Replies
View Related
Mar 16, 2011
can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?
View 15 Replies
View Related
Jan 2, 2013
how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?
View 4 Replies
View Related
Oct 17, 2012
How to merge multiple rows into single row (but multiple columns) efficiently.
For example
IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100
where different attributes mean different thing for each Information_type. For example for Information_Type=Location
Attribute_1 means Country
Attribute_2 means State and so on.
For example for Information_Type=Stats
Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.
I want to create a view that shows like below:
IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100
View 1 Replies
View Related
Nov 26, 2010
I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below
TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan
My desired result is to be able to show the output as follows
Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan
View 6 Replies
View Related
Jul 10, 2011
We have a production database that have : NLS_LANGUAGE=FRENCH_FRANCE.WE8ISO8859P1.
We use (INSERT, UPDATE) arabic and french languages, and it works properly.
When I issue SQL statments to retrieve arabic data (with SQL*PLUS), it works and it returns correct arabic format.
When I use PHP, with the same small query, the arabic format is not correct.
I've tried changing the encoding characters on my browsers (IE and FF) and it's still incorrect.
View 4 Replies
View Related
Dec 12, 2010
how to get last 4 characters in a string. But i don't know the length , for example the string is
abcdefghij
i want only ghij.
View 5 Replies
View Related
Jun 20, 2011
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?
View 1 Replies
View Related
Jun 22, 2013
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.
View 1 Replies
View Related
May 11, 2011
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.
[oracle@basic sqlldr]$ cat jh.txt
ELEKTROM001
ZEIPR�SI002
This is the loader control file
[oracle@basic sqlldr]$ cat jh.ctl
load data
characterset utf8
infile 'jh.txt'
replace into table TEMP1
(
FLD1 POSITION(1:8) CHAR,
FLD2 POSITION(9:11)
)
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
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
I've tried using other sqlldr options such as LENGTH SYMANTICS and BYTEORDER but with no success.
View 1 Replies
View Related
Mar 1, 2011
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
-------------------------------------------
[...]
<ROW>
<NO>10260253</NO>
<SUBJECT>123456789 123456789 123456789 123456789 123456789 123456789 12</SUBJE
CT>
</ROW>
[...]
-------------------------------------------
issue
-------------------------------------------
</SUBJECT> TAG is trunk and makes xml invalid for python parsing
View 14 Replies
View Related
Jan 24, 2011
I need to give validation for not allowing french or arabic or hindi alphabets or numbers except for only english alphanumeric letters.
I understood how to restrict special characters or spaces .
regexp_instr(i, '[^[:alnum:]]') = 0
This function allow only characters and numbers in English language and doesnt allow special characters.
I need to restrict further by not allowing any other language characters or numbers except only english alphanumeric letters.
View 37 Replies
View Related
Jan 8, 2013
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.
View 9 Replies
View Related
Jul 9, 2013
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
[code]....
View 8 Replies
View Related
Jan 7, 2013
How can I find out that exact count of '~'?
SELECT NVL(LENGTH('~~~~~~~~~~~~~~~~~')-LENGTH(REPLACE('~~~~~~~~~~~~~~~~~','~','')),0) result
FROM dual;
View 11 Replies
View Related
Mar 22, 2013
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
View 5 Replies
View Related
