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

sql_mode = Oracle with MariaDB 10.3.4 and Store procedure

Details

    Description

      Hello I tried short example to try compatibility of Oracle with MariaDB but it's not work :

      sql_mode = Oracle

      create or replace function ean_13(P_Str In Varchar2) Return Varchar2 Is 
      l_Sum Number; 
      l_Multiple Number; 
      Begin For I in 1..12 Loop
      		 
       If mod(i,2) = 0 Then
      		 
       l_Multiple := 3;
      		 
       Else l_Multiple := 1;
      		 
       End If;
      		 
       l_Sum := Nvl(l_Sum, 0) + Substr(P_Str, i, 1) * l_Multiple; End Loop; 
      If Floor(l_Sum/10) - Mod(l_Sum, 10) = Substr(P_Str, 13) Then Return('TRUE');
      		 
      Else Return('FALSE'); 
      End If; 
      End; /

      Attachments

        Issue Links

          duplicates

          Technical task - A technical task. MDEV-10654 IN, OUT, INOUT parameters in CREATE FUNCTION

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            Aurelien_LEQUOY Aurélien LEQUOY created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Epic Colour ghx-label-4
            Epic Name mode = Oracle and Store procedure
            Epic Status To Do [ 10100 ]
            Issue Type Epic [ 5 ] Bug [ 1 ]
            serg Sergei Golubchik made changes -
            Affects Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Component/s Stored routines [ 13905 ]
            elenst Elena Stepanova added a comment -

            It appears to be a limitation of the current implementation. There is a separate task for support of IN/OUT for functions, it hasn't been done yet: MDEV-10654

            bar, it probably needs to be documented somehow here.

            elenst Elena Stepanova added a comment - It appears to be a limitation of the current implementation. There is a separate task for support of IN/OUT for functions, it hasn't been done yet: MDEV-10654 bar , it probably needs to be documented somehow here .
            elenst Elena Stepanova made changes -
            Component/s Documentation [ 10903 ]
            Fix Version/s 10.3 [ 22126 ]
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov added a comment -

            Correct, IN/OUT parameter data types are not supported yet in Functions.

            After removing "IN", this script creates the function:

            SET sql_mode=ORACLE;
            		 
            DELIMITER $$
            		 
            create or replace function ean_13(P_Str Varchar2) Return Varchar2 Is
            		 
              l_Sum Number; 
              l_Multiple Number; 
            Begin
            		 
             For I in 1..12 Loop
            		 
               If mod(i,2) = 0 Then
            		 
                 l_Multiple := 3;
            		 
               Else l_Multiple := 1;
            		 
               End If;
            		 
             l_Sum := Nvl(l_Sum, 0) + Substr(P_Str, i, 1) * l_Multiple; End Loop; 
             If Floor(l_Sum/10) - Mod(l_Sum, 10) = Substr(P_Str, 13) Then Return('TRUE');
            		 
             Else Return('FALSE'); 
             End If; 
            End;
            		 
            $$
            		 
            DELIMITER ;

            bar Alexander Barkov added a comment - Correct, IN/OUT parameter data types are not supported yet in Functions. After removing "IN", this script creates the function: SET sql_mode=ORACLE; DELIMITER $$ create or replace function ean_13(P_Str Varchar2) Return Varchar2 Is l_Sum Number; l_Multiple Number; Begin For I in 1..12 Loop If mod(i,2) = 0 Then l_Multiple := 3; Else l_Multiple := 1; End If ; l_Sum := Nvl(l_Sum, 0) + Substr(P_Str, i, 1) * l_Multiple; End Loop; If Floor(l_Sum/10) - Mod(l_Sum, 10) = Substr(P_Str, 13) Then Return ( 'TRUE' ); Else Return ( 'FALSE' ); End If ; End ; $$ DELIMITER ;
            bar Alexander Barkov made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Duplicate [ 3 ]
            Status Confirmed [ 10101 ] Closed [ 6 ]
            Aurelien_LEQUOY Aurélien LEQUOY added a comment -

            i got a new error :

            ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

            Aurelien_LEQUOY Aurélien LEQUOY added a comment - i got a new error : ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86283 ] MariaDB v4 [ 154049 ]

            People

              bar Alexander Barkov
              Aurelien_LEQUOY Aurélien LEQUOY
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.