Ignore hash character in a query on sqlplus

i’ve launched this query on sql developer without error.

    insert into
    COMUNICAZIONI_TEMPLATE (ID, DESCRIZIONE, TESTO_TEMPLATE)
values
    (3, 'TEMPLATE EMAIL NUOVO DOCUMENTO', 'Gentile <strong>$nome $cognome</strong>, <br>
Document list:
<br>
#foreach($doc in $listaDocumentiAssociatiATaleCf)
<strong>$doc.getNomeDocumento()</strong> relativo/a al contratto  <strong>$doc.getNomeProdotto() </strong>, polizza numero <strong>$doc.getNumeroPolizza() </strong> <br>
#end
<br>');

But when i launch it with sqlplus i’ve the following errors:

SP2-0734: unknown command beginning “foreach($d…” – rest of line ignored.
SP2-0042: unknown command “end” – rest of line ignored.

There is a way to ignore special character ‘#’ inside the string?

Answer

This is what you have now:

SQL> select * from comunicazioni_template;

no rows selected

SQL> insert into
  2      COMUNICAZIONI_TEMPLATE (ID, DESCRIZIONE, TESTO_TEMPLATE)
  3  values
  4      (3, 'TEMPLATE EMAIL NUOVO DOCUMENTO', 'Gentile <strong>$nome $cognome</strong>, <br>
  5  Document list:
  6  <br>
  7  #foreach($doc in $listaDocumentiAssociatiATaleCf)
SP2-0734: unknown command beginning "foreach($d..." - rest of line ignored.
  7  <strong>$doc.getNomeDocumento()</strong> relativo/a al contratto  <strong>$doc.getNomeProdotto() </strong>, polizza numero <strong>$doc.getNumeroPolizza() </strong> <br>
  8  #end
SP2-0042: unknown command "end" - rest of line ignored.
  8  <br>');

1 row created.

SQL> select length(testo_template) from comunicazioni_template;

LENGTH(TESTO_TEMPLATE)
----------------------
                   240

Row actually is inserted, but with errors.

Why is that so? Because you hit the SQLPREFIX issue.

Sets the SQL*Plus prefix character. While you are entering a SQL command or PL/SQL block, you can enter a SQL*Plus command on a separate line, prefixed by the SQL*Plus prefix character. SQL*Plus will execute the command immediately without affecting the SQL command or PL/SQL block that you are entering. The prefix character must be a non-alphanumeric character.

By default, hash # is the SQLPREFIX character. So, change it to something different (what you don’t have in a value you’re inserting, e.g. !) and then repeat the action:

SQL> rollback;

Rollback complete.

SQL> set sqlprefix "!"
SQL> insert into
  2      COMUNICAZIONI_TEMPLATE (ID, DESCRIZIONE, TESTO_TEMPLATE)
  3  values
  4      (3, 'TEMPLATE EMAIL NUOVO DOCUMENTO', 'Gentile <strong>$nome $cognome</strong>, <br>
  5  Document list:
  6  <br>
  7  #foreach($doc in $listaDocumentiAssociatiATaleCf)
  8  <strong>$doc.getNomeDocumento()</strong> relativo/a al contratto  <strong>$doc.getNomeProdotto() </strong>, polizza numero <strong>$doc.getNumeroPolizza() </strong> <br>
  9  #end
 10  <br>');

1 row created.

SQL> select length(testo_template) from comunicazioni_template;

LENGTH(TESTO_TEMPLATE)
----------------------
                   295

SQL>

Right; no more errors, length is now as it should be (295 characters).