1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

problem with pl/sql procedure

Discussion in 'SQL PL/SQL' started by lina, Mar 11, 2009.

  1. lina

    lina Guest

    i'm doing some project at oracle to my studies and I having some problem with one of the procedures..
    Code (Text):

    CREATE OR REPLACE PROCEDURE INSERT_NEW_FLIGHT
    (flight_num IN flights.FLIGHT_NUM%TYPE,
    flight_d_t IN flights.FLIGHT_DATE_TIME%TYPE,
    air_name IN flights.AIRPORT_NAME%TYPE,
    code IN flights.CODE%TYPE) IS
    BEGIN
    INSERT
    INTO flights(FLIGHT_NUM, FLIGHT_DATE_TIME, AIRPORT_NAME, CODE)
    VALUES(flight_num, to_char(flight_d_t, 'DD-MON-YYYY HH:MI A.M.'),
    air_name, code );
    END INSERT_NEW_FLIGHT;
     
    i'm trying to run it but i'm getting warning..
    I suppose it's connected to the timestamp field (flight_d_t)..
    maybe u familiar with it, and can help me??
    TNX, Lina.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Welcome to club Oracle !!!

    You didn't specify the Error Message . what is the message you are getting ?

    If All input parameters are compatible with filed of FLIGHTS table , there is no need to apply TO_CHAR function on flight_d_t. Ypou can insert the parameter as it is.
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Does the flights table exist? what is the exact error you are getting? I got no error when i did this
    Code (Text):

    CREATE TABLE flights (
        flight_num NUMBER,
        flight_date_time DATE,
        airport_name VARCHAR2(20),
        code VARCHAR2(5)
    )

    CREATE OR REPLACE PROCEDURE insert_new_flight (
       p_flight_num   IN   flights.flight_num%TYPE,
       p_flight_d_t   IN   flights.flight_date_time%TYPE,
       p_air_name     IN   flights.airport_name%TYPE,
       p_code         IN   flights.code%TYPE
    )
    IS
    BEGIN
       INSERT INTO flights
                   (flight_num, flight_date_time,
                    airport_name, code
                   )
            VALUES (p_flight_num, TO_CHAR (p_flight_d_t, 'DD-MON-YYYY HH:MI A.M.'),
                    p_air_name, p_code
                   );
    END insert_new_flight;
     
  4. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    oops yes of course, i didn't notice that Rajavu. The TO_CHAR is not required!
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It will give error on Execution

    By the way flight_date_time TIMESTAMP , Even For DATE it will give error.

    Code (Text):

    SQL> CREATE TABLE flights (
        flight_num NUMBER,
        flight_date_time TIMESTAMP,
        airport_name VARCHAR2(20),
        code VARCHAR2(5)
    )  2    3    4    5    6
      7  ;

    Table created.

    SQL>
    SQL>
    SQL> CREATE OR REPLACE PROCEDURE insert_new_flight (
      2     p_flight_num   IN   flights.flight_num%TYPE,
      3     p_flight_d_t   IN   flights.flight_date_time%TYPE,
      4     p_air_name     IN   flights.airport_name%TYPE,
      5     p_code         IN   flights.code%TYPE
      6  )
      7  IS
      8  BEGIN
      9     INSERT INTO flights
     10                 (flight_num, flight_date_time,
     11                  airport_name, code
     12                 )
     13          VALUES (p_flight_num, TO_CHAR (p_flight_d_t, 'DD-MON-YYYY HH:MI A.M.'),
     14                  p_air_name, p_code
     15                 );
     16  END insert_new_flight;
     17  /

    Procedure created.

    SQL> EXEC insert_new_flight ( 13 , SYSTIMESTAMP , 'BANGALORE' , 'IT344');
    BEGIN insert_new_flight ( 13 , SYSTIMESTAMP , 'BANGALORE' , 'IT344'); END;

    *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "SCOTT.INSERT_NEW_FLIGHT", line 9
    ORA-06512: at line 1

     
     
  6. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    ok let's see, first let's define the table with the timestamp column
    Code (Text):

    CREATE TABLE flights (
        flight_num NUMBER,
        flight_date_time TIMESTAMP,
        airport_name VARCHAR2(20),
        code VARCHAR2(5)
    );

    Table  created
     
    Now let's define the procedure with the TO_CHAR function in it
    Code (Text):

    CREATE OR REPLACE PROCEDURE insert_new_flight (
       p_flight_num   IN   flights.flight_num%TYPE,
       p_flight_d_t   IN   flights.flight_date_time%TYPE,
       p_air_name     IN   flights.airport_name%TYPE,
       p_code         IN   flights.code%TYPE
    )
    IS
    BEGIN
       INSERT INTO flights
                   (flight_num, flight_date_time,
                    airport_name, code
                   )
            VALUES (p_flight_num, TO_CHAR (p_flight_d_t, 'DD-MON-YYYY HH:MI A.M.'),
                    p_air_name, p_code
                   );
    END insert_new_flight;
    /

    PROCEDURE SCOTT.INSERT_NEW_FLIGHT compiled with 0 errors and 0 warnings
     
    Now let's try to insert into the table with the procedure
    Code (Text):

    EXEC insert_new_flight ( 13 , SYSTIMESTAMP , 'BANGALORE' , 'IT344');

    ORA-01858: a non-numeric character was found where a numeric was expected
    ORA-06512: at "SYSTEM.INSERT_NEW_FLIGHT", line 9
    ORA-06512: at line 1
     
    Now let's create the procedure without the converting the timestamp to char
    Code (Text):

    CREATE OR REPLACE PROCEDURE insert_new_flight (
       p_flight_num   IN   flights.flight_num%TYPE,
       p_flight_d_t   IN   flights.flight_date_time%TYPE,
       p_air_name     IN   flights.airport_name%TYPE,
       p_code         IN   flights.code%TYPE
    )
    IS
    BEGIN
       INSERT INTO flights
                   (flight_num, flight_date_time, airport_name, code
                   )
            VALUES (p_flight_num, p_flight_d_t, p_air_name, p_code
                   );
    END insert_new_flight;

    PROCEDURE SCOTT.INSERT_NEW_FLIGHT compiled with 0 errors and 0 warnings
     
    And now let's execute it to insert
    Code (Text):

    EXEC insert_new_flight ( 13 , SYSTIMESTAMP , 'BANGALORE' , 'IT344');

    PL/SQL procedure succesfully completed
     
    So obviously the issue is that the table's column is a timestamp and we were converting it to char when inserting. To find a workaround, either we should use the function the to_timestamp when executing the procedure or define the IN parameter as a CHAR and use TO_TIMESTAMP within the procedure.