Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10801

sql_mode=ORACLE: Dynamic SQL placeholders

    XMLWordPrintable

Details

    • 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

    Description

      When running with sql_mode=ORACLE, the parser should understand Oracle style placeholders in EXECUTE IMMEDIATE and in PREPARE.

      Placeholders are designated as a colon followed by a regular identifier, a delimited identifier, or an integer unsigned number in the range 0..65535:

      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:x,:y) FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:"x",:"y") FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:1,:2) FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:2,:1) FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:x,:1) FROM DUAL' USING 10,20;
      

      Placeholders in individual statements are associated by position, not by name. All the above queries insert values 10 and 20 into exactly the same columns of the table t1.

      Placeholders can have duplicate names. In this case, every placeholder must have a bind value in the USING clause.

      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:x,:x) FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:1,:1) FROM DUAL' USING 10,20;
      EXECUTE IMMEDIATE 'INSERT INTO t1 SELECT (:2,:2) FROM DUAL' USING 10,20;
      

      Unlike individual statements, rules in blocks are different. Only each unique placeholder must have an unique value. Blocks are out of scope of this task.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              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.