[MDEV-19804] sql_mode=ORACLE: call procedure in packages Created: 2019-06-19  Updated: 2022-04-04  Resolved: 2022-03-25

Status: Closed
Project: MariaDB Server
Component/s: PL/SQL
Affects Version/s: 10.4.6
Fix Version/s: 10.9.0, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3

Type: Bug Priority: Major
Reporter: Wolfgang Draxler Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Compatibility
Environment:

Windows


Issue Links:
Relates
relates to MDEV-19328 sql_mode=ORACLE: Package function in ... Closed

 Description   

In the ORACLE-Mode there is a problem to call function and procedure in packages.

Generate the samples

SET SESSION sql_mode=ORACLE;
use temp;
 
delimiter //
create or replace package PCK_TEST as
  procedure FUN_TEST();
end;
//
 
delimiter //
create or replace package body PCK_TEST as
 
  procedure FUN_TEST() as
  
  begin
    select 'test-function';
  end;  
end;
 
//
delimiter ;

Following run correct:

use temp;
call pck_test.fun_test;

But when you call it with

use temp;
call temp.pck_test.fun_test;

then came the error:
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 '.fun_test' at line 1

create is this problem when you are in an other Schema

use test;
call temp.pck_test.fun_test;

There I get the same error.

I "quick and dirty" solution is:
Generate a normal function or procedure e.g. "prc_test" in the shema "temp" and in this is the call-statement "call pck_test.fun_test;". Then you can call in the Schema "test" the Function/Procedure "prc_test" in the schema "temp".

use temp;
 
delimiter //
create procedure prc_test() as
 
begin
  call pck_test.fun_test();
end;
//
delimiter ;
 
use test;
call temp.prc_test;


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