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

sql_mode=ORACLE: call procedure in packages

    XMLWordPrintable

Details

    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

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.