Forum Stats

  • 3,852,376 Users
  • 2,264,100 Discussions
  • 7,905,055 Comments

Discussions

re: Sql string parameter

User_IPZH9
User_IPZH9 Member Posts: 59 Blue Ribbon
edited Sep 25, 2020 3:12AM in SQL & PL/SQL

Hi,

I have a sql program which accepts 5 parameters. One of them is a text.

Some times it can have ' (quote).

var := '&1';

Trying to replace single quote with double.

How can I do it as program fails on the assignment itself

var := '&1'; 

ORA-01756: quoted string not properly terminated

replace commands also same error.

This is called by many programs. We would like to change in this one program instead of all calling program do the replace.

thanks

Best Answer

Answers

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Sep 24, 2020 12:57PM

    What is a "SQL program"? Do you mean a PL/SQL program (function or procedure)?

    Then - what do you mean by "assign value to a parameter"?  That is normally done OUTSIDE the "program" - then the parameter values (the arguments) are passed to the "program" and used there; values are not assigned to the parameters IN the "program". Or is that an OUT (or IN OUT) parameter? If so, it would be worth mentioning that in your question.

    What data type is VAR?  And are you having trouble with the ampersand (used by SQL*Plus as a marker for substitution variables)?

    In any case, if what you mean is to change  var := '&1'  to  var := "&1", that's invalid syntax in Oracle SQL and PL/SQL; I am not sure what you hope to accomplish by that.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 24, 2020 9:24PM Answer ✓

    Pass in the parameter value using the text literal option of "Q" or "q" (see the documentation --> https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218)

    It shows examples like  'Jackie''s raincoat' can be passed in as q' followed by YOUR own version of a delimiter. In this case I'm using the brace

    create table test (x varchar2(100));

    insert into test values (q'{Jackie's raincoat}');

    commit;

    select *

      from test;

    Output

    Jackie's raincoat

  • User_IPZH9
    User_IPZH9 Member Posts: 59 Blue Ribbon
    edited Sep 24, 2020 1:05PM

    Hi,

    It's  customer.sql   script.

    inside the program parameters like customer name is prompted for input.

    sql>@customer.sql

    Enter value for &1 = ABC's corp.

    In the .sql script

    var1 := '&1';

    It's erroring due to the  ' (quote) in the input.

    Trying to replace it with double quotes.

    One is ask the user to input  ABC''C corp.  That would work. But trying to see programmatic approach.

    thanks

  • mathguy
    mathguy Member Posts: 10,675 Blue Diamond
    edited Sep 24, 2020 1:12PM

    Oh, I see. "Double quotes" is 100% incorrect, what you want is two (separate, but consecutive) SINGLE quotes.

    As jaramill said: use the q-quotation mechanism (Google-search for the phrase "q-quotation mechanism" or "q-quote syntax" or similar if you haven't heard of that). The ability to work correctly with single-quotes within the input string is one of the benefits of q-quotes.

  • User_IPZH9
    User_IPZH9 Member Posts: 59 Blue Ribbon
    edited Sep 24, 2020 1:56PM

    Thanks jaramill. It's working now.

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Sep 24, 2020 9:19PM
    3295629 wrote:Thanks jaramill. It's working now.

    Glad it worked.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,316 Red Diamond
    edited Sep 25, 2020 3:12AM
    3295629 wrote:Thanks jaramill. It's working now.

    And just for your learning, understand that what you are doing is not "parameters" but is substitution variables...

  • BluShadow
    BluShadow Member, Moderator Posts: 42,316 Red Diamond

    ** post forum migration - the link to the substitution variables document that has gone missing from my post is now: