Mojo's SQL Maven Plugin
  1. Mojo's SQL Maven Plugin
  2. MSQL-52

Add Oracle SQLPlus syntax for substitution variables

    Details

    • Type: New Feature New Feature
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 1.4
    • Fix Version/s: None
    • Labels:
      None
    • Number of attachments :
      0

      Description

      Replacement variables in Oracle SQLPlus files have the format of "%%whatever." This makes them rather hard to use with the Maven SQLPlugin. If this syntax were added as an option then the SQLPlugin could execute more scripts.

        Issue Links

          Activity

          Hide
          Robert Scholte added a comment -

          Jon,

          could you add at least a valid example, just something as simple as "select 1;", where the "1" should be the variable, so we can write a proper unit-test.
          A link to some reference might help as well. We could add this to the documentation of the plugin.

          Show
          Robert Scholte added a comment - Jon, could you add at least a valid example, just something as simple as "select 1;", where the "1" should be the variable, so we can write a proper unit-test. A link to some reference might help as well. We could add this to the documentation of the plugin.
          Hide
          Dan Tran added a comment -

          sql plugin should stay neutral as much as possible and adding oracle specific to the parser may not a good idea

          Show
          Dan Tran added a comment - sql plugin should stay neutral as much as possible and adding oracle specific to the parser may not a good idea
          Hide
          Robert Scholte added a comment -

          Yes, it must stay neutral, but what's the problem with giving the option to change the Interpolator?
          By default use the MavenInterpolator, developers could change it. But don't mix interpolators, just pick one.

          Show
          Robert Scholte added a comment - Yes, it must stay neutral, but what's the problem with giving the option to change the Interpolator? By default use the MavenInterpolator, developers could change it. But don't mix interpolators, just pick one.
          Hide
          Jon Strayer added a comment -

          DROP SEQUENCE &&schema..ACCOUNT_CODE_DEFINITION_SEQ;
          would become
          DROP SEQUENCE SOME_SCHEMA.ACCOUNT_CODE_DEFINITION_SEQ;

          SELECT &1 FROM DUAL;

          In this case the &1 refers to the first command line argument.

          The Oracle doco is here:http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html

          It looks like for our uses '&' and '&&' are equivalent. And any '..' would be converted to a '.'. But a single '.' is just concat.

          Show
          Jon Strayer added a comment - DROP SEQUENCE &&schema..ACCOUNT_CODE_DEFINITION_SEQ; would become DROP SEQUENCE SOME_SCHEMA.ACCOUNT_CODE_DEFINITION_SEQ; SELECT &1 FROM DUAL; In this case the &1 refers to the first command line argument. The Oracle doco is here: http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html It looks like for our uses '&' and '&&' are equivalent. And any '..' would be converted to a '.'. But a single '.' is just concat.
          Hide
          Robert Scholte added a comment -

          Jon,

          a few remarks before trying to fix this:
          Are you aware that both statements should be a line within an sql-file?
          If it's inside the pom, there's no reason to interpolate it different than $

          {var}

          : Maven is the only one executing these statements, so use it's power!

          DROP SEQUENCE &&schema..ACCOUNT_CODE_DEFINITION_SEQ;

          This means you need to have a property called schema.
          It's a value in the <properties>-section of the pom, profile or other valid place. Or it's something you add to your Maven-execution like

          mvn sql:execute -Dschema=SOME_SCHEMA

          SELECT &1 FROM DUAL;

          This won't work, we're not executing sqlplus from command-line. In this case you should use the

          {exec-maven-plugin | http://mojo.codehaus.org/exec-maven-plugin/}

          instead.

          Show
          Robert Scholte added a comment - Jon, a few remarks before trying to fix this: Are you aware that both statements should be a line within an sql-file? If it's inside the pom, there's no reason to interpolate it different than $ {var} : Maven is the only one executing these statements, so use it's power! DROP SEQUENCE &&schema..ACCOUNT_CODE_DEFINITION_SEQ; This means you need to have a property called schema . It's a value in the <properties>-section of the pom, profile or other valid place. Or it's something you add to your Maven-execution like mvn sql:execute -Dschema=SOME_SCHEMA SELECT &1 FROM DUAL; This won't work, we're not executing sqlplus from command-line. In this case you should use the {exec-maven-plugin | http://mojo.codehaus.org/exec-maven-plugin/} instead.
          Hide
          Jon Strayer added a comment -

          If Maven were the only one executing these statements I'd be done. But I have to be able to give the scripts to a customer's DBA and let him execute them using whatever tool he wants.

          SELECT &1 FROM DUAL;

          Would work if there was some kind of positional substitution available.

          Show
          Jon Strayer added a comment - If Maven were the only one executing these statements I'd be done. But I have to be able to give the scripts to a customer's DBA and let him execute them using whatever tool he wants. SELECT &1 FROM DUAL; Would work if there was some kind of positional substitution available.
          Hide
          Robert Scholte added a comment -

          The issue db-vendor specific.
          I would first use a groovy-script executed by the gmaven-plugin to adjust the sql, next use this plugin to execute the new SQL-files.

          Show
          Robert Scholte added a comment - The issue db-vendor specific. I would first use a groovy-script executed by the gmaven-plugin to adjust the sql, next use this plugin to execute the new SQL-files.

            People

            • Assignee:
              Unassigned
              Reporter:
              Jon Strayer
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: