Home » SQL & PL/SQL » SQL & PL/SQL » Applying Valid Number & Date Check (Oracle 10g, HP Unix)
Applying Valid Number & Date Check [message #377417] Mon, 22 December 2008 23:13 Go to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
Dear Guys,

I have a table in which all the field values are VARCHAR2. However, this table has two columns which are supposed to be NUMBER and DATE.

What I want to do is that I want to apply a VALID NUMBER and DATE check on these two fields and store the VALID/INVALID entry in another column of the same table depening on the VALID/INVALID check.

Re: Applying Valid Number & Date Check [message #377418 is a reply to message #377417] Mon, 22 December 2008 23:17 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi,

Apply the VARCHAR2 column for TO_DATE and TO_NUMBER function

Thanks
Trivendra
Re: Applying Valid Number & Date Check [message #377424 is a reply to message #377417] Mon, 22 December 2008 23:54 Go to previous messageGo to next message
shankhan
Messages: 28
Registered: September 2008
Location: Pakistan
Junior Member
But applying these checks would raise exceptions like Invalid Number and i want to apply these checks in bulk rather than on single record.
Re: Applying Valid Number & Date Check [message #377429 is a reply to message #377417] Tue, 23 December 2008 00:19 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Please provide little more details including test script and data.

Thanks
Trivendra
Re: Applying Valid Number & Date Check [message #377434 is a reply to message #377417] Tue, 23 December 2008 01:00 Go to previous messageGo to next message
shettypravs
Messages: 9
Registered: August 2008
Junior Member
Oracle 10g has regular expression functions that will help you to search a string for digits only...thats to validate that your string is a valid number

Ex
SELECT 1 FROM EMP WHERE REGEXP_INSTR(ename, ':digit:') <> 0
-- return "no data found" because all ename values are characters
SELECT 1 FROM EMP WHERE REGEXP_INSTR(sal, ':digit:') <> 0
-- gives 1 for all rows because sal has all numeric values. This result hold true even if there are decimals in the sal values

The two square brackets around :digits: are not getting properly displayed in my reply. Please check the actual format by goggling for "oracle regular expressions"

[Updated on: Tue, 23 December 2008 01:06]

Report message to a moderator

Re: Applying Valid Number & Date Check [message #377439 is a reply to message #377434] Tue, 23 December 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The two square brackets around :digits: are not getting properly displayed in my reply.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Applying Valid Number & Date Check [message #377440 is a reply to message #377417] Tue, 23 December 2008 01:32 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi shankhan,

You can also do it with creating a function. Which can help you in INSERT and UPDATE operations.

Assuming the data fit your requirement. You can try with this

CREATE TABLE TEMP
(
  TEXT    VARCHAR2(20 BYTE),
  RESULT  VARCHAR2(100 BYTE)
);


CREATE OR REPLACE FUNCTION check_validity (i_text IN VARCHAR2)
  RETURN VARCHAR2 IS
  l_check_date       DATE;
  l_check_number     PLS_INTEGER;
  l_invalid_date     EXCEPTION;
  l_invalid_date1    EXCEPTION;
  l_invalid_date3    EXCEPTION;
  l_invalid_date4    EXCEPTION;
  l_invalid_number   EXCEPTION;
  PRAGMA EXCEPTION_INIT (l_invalid_date, -01843);
  PRAGMA EXCEPTION_INIT (l_invalid_date1, -01847);
  PRAGMA EXCEPTION_INIT (l_invalid_date3, -01841);
  PRAGMA EXCEPTION_INIT (l_invalid_date4, -01830);
  PRAGMA EXCEPTION_INIT (l_invalid_number, -01858);
BEGIN
  BEGIN
    SELECT TO_NUMBER (i_text)
      INTO l_check_number
      FROM DUAL;

    RETURN 'VALID NUMBER';
  EXCEPTION
    WHEN INVALID_NUMBER THEN
      BEGIN
        SELECT TO_DATE (i_text, 'DD-MM-YYYY')
          INTO l_check_date
          FROM DUAL;

        RETURN 'VALID DATE';
      EXCEPTION
        WHEN l_invalid_date THEN
          RETURN 'INVALID DATE';
        WHEN l_invalid_date1 THEN
          RETURN 'INVALID DATE';
        WHEN l_invalid_date3 THEN
          RETURN 'INVALID DATE';
        WHEN l_invalid_date4 THEN
          RETURN 'INVALID DATE';
        WHEN l_invalid_number THEN
          RETURN 'INVALID NUMBER';
      END;

      RETURN 'INVALID NUMBER';
  END;
END;


INSERT INTO temp
     VALUES ('01-01-2008', check_validity ('01-01-2008'));
INSERT INTO temp
     VALUES ('01-55-2008', check_validity ('01-55-2008'));
INSERT INTO temp
     VALUES ('99-01-2008', check_validity ('99-01-2008'));
INSERT INTO temp
     VALUES ('01-01-99999', check_validity ('01-01-99999'));
INSERT INTO temp
     VALUES ('01-01-00000', check_validity ('01-01-00000'));
INSERT INTO temp
     VALUES ('01-MON-2008', check_validity ('01-MON-2008'));
INSERT INTO temp
     VALUES ('1', check_validity ('1'));
INSERT INTO temp
     VALUES ('1.000', check_validity ('1.000'));
INSERT INTO temp
     VALUES ('abc', check_validity ('abc'));
INSERT INTO temp
     VALUES ('abc1', check_validity ('abc1'));


COLUMN TEXT FORMAT A20
COLUMN RESULT FORMAT A30
SET LINE 500
SELECT *FROM TEMP;

TEXT                 RESULT                        
-------------------- ------------------------------
01-01-2008           VALID DATE                    
01-55-2008           INVALID DATE                  
99-01-2008           INVALID DATE                  
01-01-99999          INVALID DATE                  
01-01-00000          INVALID DATE                  
01-MON-2008          INVALID NUMBER                
1                    VALID NUMBER                  
1.000                VALID NUMBER                  
abc                  INVALID NUMBER                
abc1                 INVALID NUMBER                

10 rows selected.


Thanks
Trivendra
Re: Applying Valid Number & Date Check [message #377442 is a reply to message #377417] Tue, 23 December 2008 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64269
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "isnumber", "is_number", "isdate," "is_date".

Regards
Michel
Re: Applying Valid Number & Date Check [message #377458 is a reply to message #377440] Tue, 23 December 2008 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doing this:
SELECT TO_NUMBER (i_text)
INTO l_check_number
FROM DUAL;

and this:
 SELECT TO_DATE (i_text, 'DD-MM-YYYY')
INTO l_check_date
FROM DUAL;
will massively degrade the performance of your function.

Just do it in pl/Sql:
l_check_number := to_number(i_text);

and
l_check_date := TO_DATE (i_text, 'DD-MM-YYYY');


Additionally - your function will never return 'Invalid Number' - anything that fails a TO_NUMBER will be checked for being a date, and an INVALID DATE returned.
You'd be better off with seperate VALID_NUMBER and VALID_DATE functions.
Re: Applying Valid Number & Date Check [message #377461 is a reply to message #377417] Tue, 23 December 2008 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your best bet would be to convert the columns to NUMBER and DATE types - that way you don't need to worry about the validation.
Use ALTER TABLE ... RENAME COLUMN, ALTER TABLE ... DROP COLUMN and ALTER TABLE ... ADD to change the column types around
Re: Applying Valid Number & Date Check [message #377498 is a reply to message #377417] Tue, 23 December 2008 04:47 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Changes Done Sir.
Re: Applying Valid Number & Date Check [message #377541 is a reply to message #377417] Tue, 23 December 2008 06:58 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi JRowbottom,

Thanks for your good suggestions, I have seen that issue and I am with you words.

I have a question regarding using the
SELECT TO_DATE (i_text, 'DD-MM-YYYY')
INTO l_check_date
FROM DUAL;


The only performance issue that I see is using select command in PL/SQL block is that when select query fires it will hit the database in two steps,

1. Retrieve Row from the database.
2. Look for any too_many_rows exception.

while
l_check_date := TO_DATE (i_text, 'DD-MM-YYYY');


Will not look for 1 and 2.

Is it this reason or more performance issue need to be considered?

Thanks
Trivendra

[Updated on: Tue, 23 December 2008 08:26]

Report message to a moderator

Re: Applying Valid Number & Date Check [message #377698 is a reply to message #377541] Wed, 24 December 2008 06:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The pl.sql solution is faster for two related reasons.

To call SQL from Pl/Sql involves what is called a context switch - SQL and Pl/Sql are parsed and executed by different parts of Oralce - different engines - and there is an overhead in passing data and processing control between them.

Secondly, once control has been passed to the SQL engine, there is the overhead of parsing and executing the query. Given that the query contains the same to_date command in it, this is never going to be quicker than the pl/sql.

Empirical evidence. Here is a simple timing harness comparing the costs of Pl/Sql and Sql over a variety of iterations:
declare
  v_iter   pls_integer := 10000000;
  v_res    date;
  v_date   varchar2(20) := '12-11-2008';
  v_time   pls_integer;
begin
  dbms_output.put_line(v_iter ||' Iterations');
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    v_res := to_date(v_date,'dd-mm-yyyy');
  end loop;

  dbms_output.put_line('Pl/sql: '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  for i in 1..v_iter loop
    SELECT to_date(v_date,'dd-mm-yyyy')
    INTO   v_res
    FROM   dual;
  end loop;
  
  dbms_output.put_line('Sql: '||to_char(dbms_utility.get_time - v_time));

end;
/


Results for various no. iterations. Times aer 100th of a second:
1000 Iterations
Pl/sql: 0
Sql: 5

10000 Iterations
Pl/sql: 0
Sql: 56

100000 Iterations
Pl/sql: 1
Sql: 418

1000000 Iterations
Pl/sql: 3
Sql: 4100

10000000 Iterations
Pl/sql: 36
Sql: 40829


As you can see, a Pl/Sql solution is rouchly 2 orders of magnitude faster.

The Too_Many_Rows argument (your no. 2) used to apply in v7.3.4, but as far as I know, queries against Dual have been optomised to return only a single row since Oracle 8i.
Re: Applying Valid Number & Date Check [message #377712 is a reply to message #377417] Wed, 24 December 2008 08:03 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks for this example, I was not aware of Point 2. Thanks for Update on dual table.

Thanks
Trivendra

Previous Topic: run in allowed time-zone
Next Topic: Sent attachment with pl/sql as a mail with ( mulitpel recepents )
Goto Forum:
  


Current Time: Mon Jan 16 16:33:14 CST 2017

Total time taken to generate the page: 0.08945 seconds