Discussions
Categories
- 384.5K All Categories
- 2.5K Data
- 551 Big Data Appliance
- 1.9K Data Science
- 451.3K Databases
- 222.1K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 560 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 561 SQLcl
- 4K SQL Developer Data Modeler
- 187.4K SQL & PL/SQL
- 21.4K SQL Developer
- 296.8K Development
- Application Development
- 18 Developer Projects
- 140 Programming Languages
- 293.5K Development Tools
- 112 DevOps
- 3.1K QA/Testing
- 646.2K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 193 Java Community Process
- 106 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 21 Java Essentials
- 165 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 525 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 178 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 236 Portuguese
re: Sql string parameter
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
-
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
Answers
-
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.
-
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
-
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
-
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.
-
Thanks jaramill. It's working now.
-
3295629 wrote:Thanks jaramill. It's working now.
Glad it worked.
-
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...
-
** post forum migration - the link to the substitution variables document that has gone missing from my post is now:
