Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Kuwait
    Posts
    27

    Unanswered: Generating String With Input Mask Format

    Hello Gurus,

    I have several tables i.e tab1, tab2, tab3. All with different structure and datatype. I figured that I will be able to extract the data type of the fields in the said table by reading through the user_tab_cols table. For example, I have the following input strings:

    1. 01/01/03 (date in the dd/mm/yy format)
    2. 45354.342 (numeric with decimal places)

    I need to be able to create a function that will take the following inputs:

    1. data type from the user_tab_cols table i.e. number or date
    2. the input string which is either item 1 or 2.
    3. Output based on the data type parameter fed will be to_date('01/01/03','dd/mm/yy') or to_number('45354.342', '99999.999')
    4. The output will become a part of an insert statement for the table.

    Another option I am thinking is the below:

    Inputs

    1. Table name
    2. 2. the input string which is either item 1 or 2.

    Output

    3. Output based on the data type parameter fed will be to_date('01/01/03','dd/mm/yy') or to_number('45354.342', '99999.999')
    4. The output will become a part of an insert statement for the table.

    The second option should include a query to the user_tab_cols table for the data type.

    Can you please help me on this? Thank you and best regards...

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Remember to use bind variables for your code, e.g. generate a stmt like

    insert into table tt (x,y)
    values (to_number(:b1),to_date (:b2,'yy/mm/dddd'));

    Never encode literals like this:

    insert into table tt (x,y)
    values (to_number('123.44'), to_date ('11/11/2003', 'yy/mm/dddd'));

    you will kill performance otherwise.

    To generate the functions, all you need is a simple statement:

    SQL> create table tt (x number, y date);

    Table created.

    SQL> select decode(data_type,'DATE','to_date(:b,''dd/mm/yy'')','NUMBER','to_number(:b)') as func
    2 from user_tab_columns where table_name = 'TT' and column_name = 'X';

    FUNC
    ------------------------------------------------------------------
    to_number(:b)

    SQL> select decode(data_type,'DATE','to_date(:b,''dd/mm/yy'')','NUMBER','to_number(:b)') as func
    2 from user_tab_columns where table_name = 'TT' and column_name = 'Y';

    FUNC
    ------------------------------------------------------------------
    to_date(:b,'dd/mm/yy')

    You can easily adapt this example to your needs (of course, use bind variables for table_name and column_name too ;-) )

    HTH
    al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •