[MDEV-19150] sql_mode="oracle" errors on create procedure AS Created: 2019-04-02  Updated: 2019-09-11  Resolved: 2019-09-11

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.3.14
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Manjot Singh (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: Compatibility, need_feedback


 Description   

proc

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
   BEGIN
 
      DELETE FROM employees
 
      WHERE employees.employee_id = remove_emp.employee_id;
 
   tot_emps := tot_emps - 1;
 
   END;
 
/

error:

MariaDB [test]> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1



 Comments   
Comment by Dmitry Tolpeko [ 2019-06-25 ]

manjot I was able to create such procedure. What is the issue?

MariaDB [test]> CREATE PROCEDURE remove_emp2 (employee_id NUMBER) AS tot_emps NUMBER;
 
    ->    BEGIN
 
    ->       DELETE FROM employees
 
    ->       WHERE employees.employee_id = remove_emp.employee_id;
 
    ->    tot_emps := tot_emps - 1;
 
    ->    END;
 
    -> /
 
Query OK, 0 rows affected (0.007 sec)

Comment by Ralf Gebhardt [ 2019-07-15 ]

Hi manjot, tested with 10.4.6, works as expected. Please verify it again and add the effected version, if you still see issues.

Comment by Manjot Singh (Inactive) [ 2019-07-15 ]

@Ralf This does not work in 10.3.

MariaDB [(none)]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.3.14-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> set sql_mode="oracle";
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [(none)]> create database test;
 
Query OK, 1 row affected (0.000 sec)
 
 
 
MariaDB [(none)]> use test;
 
Database changed
 
 
 
MariaDB [test]> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 
MariaDB [test]>

Comment by Ralf Gebhardt [ 2019-07-17 ]

manjot I tried it with the new 10.3.16, no problem.

Comment by Alexander Barkov [ 2019-09-10 ]

Don't forget to set DELIMITER properly:

SET sql_mode=ORACLE;
 
DELIMITER /
 
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
   BEGIN
 
      DELETE FROM employees
 
      WHERE employees.employee_id = remove_emp.employee_id;
 
   tot_emps := tot_emps - 1;
 
   END;
 
/
 
DELIMITER ;


MariaDB [test]> DELIMITER /
 
MariaDB [test]> CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
    ->    BEGIN
 
    ->       DELETE FROM employees
 
    ->       WHERE employees.employee_id = remove_emp.employee_id;
 
    ->    tot_emps := tot_emps - 1;
 
    ->    END;
 
    -> /
 
Query OK, 0 rows affected (0.011 sec)
 
 
 
MariaDB [test]> DELIMITER ;

Comment by Alexander Barkov [ 2019-09-10 ]

By the way, there should not be a semicolon after NUMBER. It should be followed by BEGIN immediately.

manjot, can you clarify please, what is the problem?
Does it work for you if you remove the redundant semicolon and add DELIMITER commands?

Comment by Manjot Singh (Inactive) [ 2019-09-10 ]

This example with semi colon works in pl/sql. I believe this is from one of
oracle's tutorials.

Thanks,
Manjot Singh

On Mon, Sep 9, 2019, 7:47 PM Alexander Barkov (Jira) <jira@mariadb.org>

Comment by Alexander Barkov [ 2019-09-10 ]

manjot Sorry, the semicolon is OK.

So this script works without problems in MariaDB:

SET sql_mode=ORACLE;
 
DELIMITER /
 
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
   BEGIN
 
      DELETE FROM employees
 
      WHERE employees.employee_id = remove_emp.employee_id;
 
   tot_emps := tot_emps - 1;
 
   END;
 
/
 
DELIMITER ;

Should we close the issue?

Comment by Manjot Singh (Inactive) [ 2019-09-10 ]
Comment by Alexander Barkov [ 2019-09-11 ]

I just tested with 10.3.14, this script works without problems:

SET sql_mode=ORACLE;
 
DELIMITER /
 
CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;
 
   BEGIN
 
      DELETE FROM employees
 
      WHERE employees.employee_id = remove_emp.employee_id;
 
   tot_emps := tot_emps - 1;
 
   END;
 
/
 
DELIMITER ;

You got a syntax error because you forgot the DELIMITER command before CREATE PROCEDURE.

So this query fragment:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS tot_emps NUMBER;

is interpreted by the "mysql" command line client as a separate statement.
The server returns a syntax error because on unexpected end-of-query.

Generated at Thu Feb 08 08:49:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.