Home » SQL & PL/SQL » SQL & PL/SQL » Need help with insert in Oracle (Oracle SQL Developer)
Need help with insert in Oracle [message #668311] Sat, 17 February 2018 13:53 Go to next message
victor_pulga
Messages: 3
Registered: February 2018
Junior Member
I have a table which i need to be populated.
The data is coming from another table in which the date columns are designed as Varchar2(10) : (eg: 2017-03-29)
My target table has date column defined as DATE.
Now what I want to do is to set a default value to be entered into the target DATE column, when I'm receiving any other data than the date(like 'UUUUUUUUUU').
Is there any way?

Report message to a moderator

Re: Need help with insert in Oracle [message #668312 is a reply to message #668311] Sat, 17 February 2018 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

When all else fails Read The Fine Manual

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

Report message to a moderator

Re: Need help with insert in Oracle [message #668313 is a reply to message #668311] Sat, 17 February 2018 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
insert into target (date_col)
select case 
         when regexp_like(varchar_col, '^\d{4}-\d{2}-\d{2}$') 
           then to_date(varchar_col, 'YYYY-MM-DD')
         else to_date(null)
       end
from source

[Updated on: Sat, 17 February 2018 14:21]

Report message to a moderator

Re: Need help with insert in Oracle [message #668315 is a reply to message #668313] Sat, 17 February 2018 20:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
Senior Member
This will error out if year is 0 and/or month is 00 or > 12 and/or day is 00 or > max day for the month/year. It is simpler to create a function:

create or replace
  function yyyy_mm_dd(
                      p_date_string varchar2
                     )
    return date
    is
    begin
        return to_date(p_date_string,'yyyy-mm-dd');
      exception
        when others
          then
            return null;
end;
/

Now we can:

SQL> alter session set nls_date_format='yyyy-mm-dd'
  2  /

Session altered.

SQL> with t as (
  2             select '2018-02-17' ds from dual union all
  3             select '20-17-2018' from dual union all
  4             select 'abc' from dual
  5            )
  6  select  ds,
  7          yyyy_mm_dd(ds)
  8    from  t
  9  /

DS         YYYY_MM_DD
---------- ----------
2018-02-17 2018-02-17
20-17-2018
abc

SQL>

And for 12C:

SQL> alter session set nls_date_format='yyyy-mm-dd'
  2  /

Session altered.

SQL> with
  2    function yyyy_mm_dd(
  3                        p_date_string varchar2
  4                       )
  5      return date
  6      is
  7      begin
  8          return to_date(p_date_string,'yyyy-mm-dd');
  9        exception
 10          when others
 11            then
 12              return null;
 13  end;
 14       t as (
 15             select '2018-02-17' ds from dual union all
 16             select '20-17-2018' from dual union all
 17             select 'abc' from dual
 18            )
 19  select  ds,
 20          yyyy_mm_dd(ds)
 21    from  t
 22  /

DS         YYYY_MM_DD
---------- ----------
2018-02-17 2018-02-17
20-17-2018
abc

SQL>

SY.

Report message to a moderator

Re: Need help with insert in Oracle [message #668316 is a reply to message #668315] Sun, 18 February 2018 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In 12c better use the built-in feature:
SQL> with t as (
  2    select '2018-02-17' ds from dual union all
  3    select '20-17-2018' from dual union all
  4    select 'abc' from dual
  5    )
  6  select ds, to_date(ds default null on conversion error, 'YYYY-MM-DD') res
  7  from t
  8  /
DS         RES
---------- -------------------
2018-02-17 17/02/2018 00:00:00
20-17-2018
abc

Report message to a moderator

Re: Need help with insert in Oracle [message #668317 is a reply to message #668316] Sun, 18 February 2018 05:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
Senior Member
Thanks, learned something new today.

SY.

Report message to a moderator

Re: Need help with insert in Oracle [message #668318 is a reply to message #668317] Sun, 18 February 2018 05:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
Senior Member
It just hit me I forgot Oracle does some "guessing" when converting to date:

SQL> select to_date('2012.10.11','yyyy-mm-dd') from dual
  2  /

TO_DATE('2
----------
2012-10-11

SQL> select to_date('20121011','yyyy-mm-dd') from dual
  2  /

TO_DATE('2
----------
2012-10-11

SQL>

Or worth:

SQL> select to_date('10201211','yyyy-mm-dd') from dual
  2  /

TO_DATE('1
----------
1020-12-11

SQL>

So to_date format mask (both in my suggested function and in your suggested 12C solution) should use exact format checking modifier FX:

SQL> select to_date('2012.10.11','fxyyyy-mm-dd') from dual
  2  /
select to_date('2012.10.11','fxyyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> select to_date('20121011','fxyyyy-mm-dd') from dual
  2  /
select to_date('20121011','fxyyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> select to_date('10201211','fxyyyy-mm-dd') from dual
  2  /
select to_date('10201211','fxyyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL>

SY.

Report message to a moderator

Re: Need help with insert in Oracle [message #668321 is a reply to message #668313] Sun, 18 February 2018 10:51 Go to previous messageGo to next message
victor_pulga
Messages: 3
Registered: February 2018
Junior Member
--insert into target (date_col)
select case
when regexp_like(varchar_col, '\d{4}-\d{3}-\d{2]')
then to_date(varchar_col, 'YYYY-MM-DD')
else to_date(null)
end
from source--

Hi, I tried this and now all the results are returning as Null Sad

I am using SQL developer to test this.

when I try using this one,
--SQL> with t as (
2 select '2018-02-17' ds from dual union all
3 select '20-17-2018' from dual union all
4 select 'abc' from dual
5 )
6 select ds, to_date(ds default null on conversion error, 'YYYY-MM-DD') res
7 from t
8 /--

it throws me error

Report message to a moderator

Re: Need help with insert in Oracle [message #668322 is a reply to message #668321] Sun, 18 February 2018 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Please read How to use [code] tags and make your code easier to read.
2/ Don't tell us, SHOW us; use SQL*Plus and copy and paste your session, the WHOLE session (in text inside the post and formatted as we posted, no image no download).
3/ As Solomon and I told this depends on your Oracle database version: "select * from v$version"
4/ "it throws me error", this gives no clue of the error; are you sure you are connected? This is one possible reason "it throws me error".

Report message to a moderator

Re: Need help with insert in Oracle [message #668323 is a reply to message #668322] Sun, 18 February 2018 11:20 Go to previous messageGo to next message
victor_pulga
Messages: 3
Registered: February 2018
Junior Member
Sorry, This is my first-time.

The version I'm using is:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SELECT DATE_COLUMN,
TO_DATE(DATE_COLUMN DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD') AS SRC
FROM SOURCE_TABLE;

--ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"--
Above is the error I'm getting

I'm quite new to this and I'm a beginner only, sorry to bother with these basic mistakes.

Report message to a moderator

Re: Need help with insert in Oracle [message #668324 is a reply to message #668323] Sun, 18 February 2018 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"default <> on conversion error" clause is new in 12.2 so did not exist in 12.1
This explains this error.

Bow for your first point: "now all the results are returning as Null" you have to
1/ Show us what happens if you do it with the test case Solonmon provided ("with...")
2/ Post some data for your specific case which should work and does not and show us it does not as Solomon showed posting at the same time the original value and the "converted" one for each row.

And don't forget to FORMAT, there is now no excuse you don't do it, read the link and see our posts.

[Updated on: Sun, 18 February 2018 11:30]

Report message to a moderator

Re: Need help with insert in Oracle [message #668348 is a reply to message #668324] Mon, 19 February 2018 04:12 Go to previous message
quirks
Messages: 82
Registered: October 2014
Member
Its probably the last bracket in 
'\d{4}-\d{3}-\d{2]'
Instead of ] it should be a }

Report message to a moderator

Previous Topic: BULK COLLECT ISSUE
Next Topic: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Sep 26 03:44:07 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.