Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19804

sql_mode=ORACLE: call procedure in packages

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.6
    • Fix Version/s: 10.4
    • Component/s: PL/SQL
    • Labels:
      None
    • Environment:
      Windows

      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;
      

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            wodrsoftware Wolfgang Draxler
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration