re: Sql string parameter — oracle-tech

    Forum Stats

  • 3,708,961 Users
  • 2,241,165 Discussions
  • 7,840,722 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

re: Sql string parameter

User_IPZH9User_IPZH9 Posts: 51 Red Ribbon
edited September 2020 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

  • mathguymathguy Posts: 9,333 Gold Crown
    edited September 2020

    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.

  • jaramilljaramill Posts: 4,298 Gold Trophy
    edited September 2020 Accepted 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_IPZH9User_IPZH9 Posts: 51 Red Ribbon
    edited September 2020

    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

  • mathguymathguy Posts: 9,333 Gold Crown
    edited September 2020

    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_IPZH9User_IPZH9 Posts: 51 Red Ribbon
    edited September 2020

    Thanks jaramill. It's working now.

  • jaramilljaramill Posts: 4,298 Gold Trophy
    edited September 2020
    3295629 wrote:Thanks jaramill. It's working now.

    Glad it worked.

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond
    edited September 2020
    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...

  • BluShadowBluShadow Moderator Posts: 40,816 Red Diamond

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


Sign In or Register to comment.