Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-11781

sql_mode=ORACLE: IN, OUT, IN OUT modes for dynamic SQL bind arguments

Details

    • Technical task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • None
    • Parser

    Description

      We'll support IN, OUT and IN OUT modes for bind arguments when running in sql_mode=ORACLE, according to the following grammar:

      execute_immediate_statement ::=
      		 
      EXECUTE_IMMEDIATE dynamic_string
      		 
         [ INTO { define_variable [, define_variable ...] | record_name } ]
      		 
         [ USING [ IN | OUT | IN OUT ] bind_argument
      		 
             [, [ IN | OUT | IN OUT ] bind_argument] ... ]
      		 
         [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];

      Example:

      DECLARE
      		 
        plsql_block VARCHAR2(500);
      		 
        new_deptid  NUMBER(4);
      		 
        new_dname   VARCHAR2(30) := 'Advertising';
      		 
        new_mgrid   NUMBER(6)    := 200;
      		 
        new_locid   NUMBER(4)    := 1700;
      		 
      BEGIN
      		 
       -- Dynamic PL/SQL block invokes subprogram:
      		 
        plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
      		 
       
      		 
       /* Specify bind arguments in USING clause.
      		 
          Specify mode for first parameter.
      		 
          Modes of other parameters are correct by default. */
      		 
        EXECUTE IMMEDIATE plsql_block
      		 
          USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
      		 
      END;
      		 
      /

      Attachments

        Issue Links

          relates to

          Technical task - A technical task. MDEV-10585 EXECUTE IMMEDIATE statement

          • Major - Major loss of function.
          • Closed

          Technical task - A technical task. MDEV-10709 Expressions as parameters to Dynamic SQL

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description We'll support {{IN}}, {{OUT}} and {{IN OUT}} modes for bind arguments when running in {{sql_mode=ORACLE}}, according to the following grammar:

            {code:sql}
            execute_immediate_statement ::=
            EXECUTE_IMMEDIATE dynamic_string
               [ INTO { define_variable [, define_variable ...] | record_name } ]
               [ USING [ IN | OUT | IN OUT ] bind_argument
                   [, [ IN | OUT | IN OUT ] bind_argument] ... ]
               [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];
            {code}
            		We'll support {{IN}}, {{OUT}} and {{IN OUT}} modes for bind arguments when running in {{sql_mode=ORACLE}}, according to the following grammar:

            {code:sql}
            execute_immediate_statement ::=
            EXECUTE_IMMEDIATE dynamic_string
               [ INTO { define_variable [, define_variable ...] | record_name } ]
               [ USING [ IN | OUT | IN OUT ] bind_argument
                   [, [ IN | OUT | IN OUT ] bind_argument] ... ]
               [ {RETURNING | RETURN } INTO bind_argument [, bind_argument]... ];
            {code}

            Example:

            {code:sql}
            DECLARE
              plsql_block VARCHAR2(500);
              new_deptid NUMBER(4);
              new_dname VARCHAR2(30) := 'Advertising';
              new_mgrid NUMBER(6) := 200;
              new_locid NUMBER(4) := 1700;
            BEGIN
             -- Dynamic PL/SQL block invokes subprogram:
              plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';

             /* Specify bind arguments in USING clause.
                Specify mode for first parameter.
                Modes of other parameters are correct by default. */
              EXECUTE IMMEDIATE plsql_block
                USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
            END;
            /
            {code}

            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Parent MDEV-10764 [ 57940 ]
            Issue Type Task [ 3 ] Technical task [ 7 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Summary IN, OUT, IN OUT modes for dynamic SQL bind arguments sql_mode=ORACLE: IN, OUT, IN OUT modes for dynamic SQL bind arguments
            serg Sergei Golubchik made changes -
            Fix Version/s 10.3 [ 22126 ]
            serg Sergei Golubchik made changes -
            Labels Compatibility
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 79176 ] MariaDB v4 [ 140183 ]

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              3 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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