0

I am having trouble trying to insert a timestamp input into my Oracle SQL Developer database. I have a table named userappt and the column called appt_date is a timestamp value. Then, I have a stored procedure called insertapptpro that will be called in my php file named insertappt.php as the action for a html form file named userappt.html. I tried inserting a new appointment but the error Warning: oci_execute(): ORA-01843: not a valid month ORA-06512 appears and returns nothing to my database.

Here is my table and stored procedure code:

create table appt
(
appt_id varchar(15) primary key,
pet_name varchar(100),
purpose varchar(50),
appt_date timestamp
);

create or replace procedure insertapptPro(
p_name appt.pet_name%type, p_purpose appt.purpose%type, p_date appt.appt_date%type)
is
begin
insert into appt(pet_name, purpose, appt_date)
values (p_name, p_purpose, p_date);
end;
/

declare 
a varchar(100);
b varchar(50);
c timestamp;
begin
a := 'Jack Jack';
b := 'Rear bleeding';
c := '15-Dec-2019 09:00';
insertapptpro(a,b,c);
end;
/

And this is my php code for the file insertappt.php:

$name = $_POST['PET_NAME'];
$purp = $_POST['PURPOSE']; 
$date = $_POST['APPT_DATE'];


    $query1 = 'BEGIN INSERTAPPTPRO(:PET_NAME, :PURPOSE, :APPT_DATE);END;';
    $result = oci_parse($connection, $query1);

    oci_bind_by_name($result, ':PET_NAME', $name);
    oci_bind_by_name($result, ':PURPOSE', $purp);
    oci_bind_by_name($result, ':APPT_DATE', $date);

    oci_execute($result);

    if (oci_fetch($result)==0) 
    {
        echo "<script type=\"text/javascript\">window.alert('Booked Successfully!')";window.location.href='/system/userappt.php';</script>";
    }

    else
    {
        echo "<script type=\"text/javascript\">window.alert('Register Failed');</script>";
    }
?>

And lastly, my html form code for the file userappt.html:

<form class="" action="insertappt.php" method="post">
          <p>
        Pet Name: <input type="text" name="PET_NAME" placeholder="Patient Name" class="inputtxt" required>
        Purpose: <input type="text" name="PURPOSE" placeholder="Purpose" class="inputtxt" required>
        Appointment date and time: <input type="datetime-local"  name="APPT_DATE" placeholder="Appointment date and time" class="inputtxt" required>
        <input type="submit" value="Book" class="subbtn">
        </form>

I'm fairly new to using datetime-local in html and timestamp in oracle, so I hope I'll learn something from you guys.

The example of input of datetime in the form

Alccy
  • 11
  • 2
  • can you show us an example of the format of date that you are trying to insert? – Professor Abronsius Dec 13 '19 at 08:30
  • Sure, I've added a screenshot of it at the end of post. – Alccy Dec 13 '19 at 08:38
  • Very probably it is an NLS_DATE_FORMAT problem: Your procedure expects the last param to be of TIMESTAMP format, and Oracle tries converting the string you send to that format, so if the string you enter does not correspond to the NLS_DATE_FORMAT of your session, you'll get a conversion error like the one you show. So, be sure of the timestamp format that is being used on the screen and in your session. – gsalem Dec 13 '19 at 08:38
  • Does that mean that I'll have to change the input type for the date in html file? Or are there other ways of converting them to be in the same format? – Alccy Dec 13 '19 at 08:43
  • if the stored procedure is expecting a timestamp you could try `$date=strtotime($_POST['APPT_DATE'])` – Professor Abronsius Dec 13 '19 at 08:49
  • Thanks for the suggestion, unfortunately I still got the same error. – Alccy Dec 13 '19 at 08:54
  • never used Oracle but the docs suggest that the `NLS_DATE_FORMAT` should be `MM/DD/YYYY` or `NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'` – Professor Abronsius Dec 13 '19 at 08:57
  • @RamRaider `NLS_DATE_FORMAT` can be anything you want and the [default date format](https://stackoverflow.com/a/50164234/1509264) depends on what territory you tell Oracle it is running in when the database is set up. However, you should not rely on `NLS_DATE_FORMAT` to convert strings to dates as each user can set their own value for their session overriding the defaults. Instead explicitly convert the string using Oracle's `TO_DATE` function or use a date or timestamp literal. – MT0 Dec 13 '19 at 09:25
  • In supported browsers, the value of a `datetime-local` input is in ISO8601 format `YYYY-MM-DDTHH:MM`. However, there are still browsers that [do not support](https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/datetime-local#Browser_compatibility) `datetime-local` inputs and it is treated as a text field. – MT0 Dec 13 '19 at 13:22

0 Answers0