Problem inserting into table in oracle using PopSQL-CodePudding

The problem i'm having is that using PopSQL, when i insert something into a table it says success but then it inserts nothing into the table by saying 0 rows affected and i can't figure out what is the problem because PopSQL works fine with postgres and Mysql. Any help is much appreciated.

  CREATE TABLE employees 
    (
      employee_id NUMBER PRIMARY KEY,
      first_name VARCHAR2(30) NOT NULL,
      last_name VARCHAR2(30) NOT NULL,
      hire_date DATE NOT NULL,
      salary NUMBER(8,2) NOT NULL
    );
    
    
    
    INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes', '01-JAN-2010', 9000);
    COMMIT;
    SELECT * FROM employees;

CodePudding user response：

Seem a Sys error to me

Try this :

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (100, 'Malik', 'Makkes', '01-JAN-2010', 9000);
COMMIT;
SELECT * FROM employees;

Make sure to change the VALUES clause to match the correct number of columns and data types in the employees table.

CodePudding user response：

As Jonas commented, "date" value (string, actually; you just enclosed some digits and letters into single quotes) looks suspicious. Read his comment once again.

I'm in Croatia. Let's see what happens in my database.

SQL> CREATE TABLE employees
  2      (
  3        employee_id   NUMBER       PRIMARY KEY,
  4        first_name    VARCHAR2(30) NOT NULL,
  5        last_name     VARCHAR2(30) NOT NULL,
  6        hire_date     DATE         NOT NULL,
  7        salary        NUMBER(8,2)  NOT NULL
  8      );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes', '01-JAN-2010', 9000);
INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes', '01-JAN-2010', 9000)
                                                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Error; can't say I didn't expect it. What does default NLS value (in my database) say about dates?

SQL> select sysdate right_now,
  2         to_char(sysdate, 'dd-mon-yyyy') another_Format
  3  from dual;

RIGHT_NOW  ANOTHER_FORMAT
---------- --------------------
04.02.2023 04-vel-2023

SQL>

Right; format doesn't match, nor does the language. How to fix it?

One option is to use date literal instead of a string:

SQL> INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes', date '2010-01-01', 9000);

1 row created.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE      SALARY
----------- --------------- --------------- ---------- ----------
        100 Malik           Makkes          01.01.2010       9000

SQL>

That works. Another option is to use to_date function with appropriate format model:

SQL> INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes',
  2    to_date('01-jan-2010', 'dd-mon-yyyy', 'nls_date_language = english'), 9000);

1 row created.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE      SALARY
----------- --------------- --------------- ---------- ----------
        100 Malik           Makkes          01.01.2010       9000

SQL>

That works too. Yet another option is to alter your session (or, if you must, NLS settings for the whole database, but for that you'll need to talk to your DBA):

SQL> alter session set nls_date_format = 'dd-mon-yyyy';

Session altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> INSERT INTO EMPLOYEES VALUES (100, 'Malik', 'Makkes', '01-JAN-2010', 9000);

1 row created.

SQL> select * from employees;

EMPLOYEE_ID FIRST_NAME      LAST_NAME       HIRE_DATE       SALARY
----------- --------------- --------------- ----------- ----------
        100 Malik           Makkes          01-jan-2010       9000

SQL>

Works again.

Now you've seen a few valid ways to insert date value into a date datatype column. Pick one you find the most appropriate. My choice would be a date literal.

On the other hand, if that's not the issue, provide more info (illustrate execution of your code; post exact error you got).