You can use REGEXP_REPLACE since Oracle 10: SELECT REGEXP_REPLACE(‘+34 (947) 123 456 ext. 2013’, ‘[^0-9]+’, ”) FROM DUAL This example returns 349471234562013. Alternative syntaxes include: POSIX character classes: ‘[^[:digit:]]+’ Perl-influenced extensions (since Oracle 11): ‘\D+’
Update 2024-01-17: SQL Fiddle no longer works for Oracle and seems to have lost many old fiddles. See the other answer for a more up-to-date list. SQL Fiddle Here is a trivial example of use: http://sqlfiddle.com/#!4/8cf15/5
This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement. According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least … Read more
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;
You cannot issue DDL as static SQL in a PL/SQL block. If you want to put those commands in a PL/SQL block, you’d need to use dynamic SQL, i.e. BEGIN EXECUTE IMMEDIATE ‘alter table table1 enable row movement’; EXECUTE IMMEDIATE ‘alter table table1 shrink space cascade’; END; / It may be easier, however, to just … Read more
SQL Fiddle Here is a trivial example of use: http://sqlfiddle.com/#!4/8cf15/5
A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you’ll get a no_data_found exception. If it returns more than 1 row, you’ll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, … Read more
If your 200+ packages work as intended with “old fashioned” syntax, let it be. SQL will not start to perform better after migration to ANSI syntax – it’s just different syntax. All that being said, ANSI syntax is cleaner – you are not going to normal join if you forget (+) in some multi-column outer … Read more
Default values are only used if the arguments are not specified. In your case you did specify the arguments – both were supplied, with a value of NULL. (Yes, in this case NULL is considered a real value :-). Try: EXEC TEST() Share and enjoy. Addendum: The default values for procedure parameters are certainly buried … Read more
This limitation is based on older Windows restrictions, where length of environmental variables was important. This limitation is still there in the Oracle installation. Work around this: Step 1: Copy the value of your ‘path’ variable to a text-editor (Ex.: notepad) and save this value as backup. Step 2: Reduce the size of this path … Read more