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

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Parser
    • Labels:

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: