CODEWITHSUNDEEP
sqloracle-databaseplsql
user1077685
user1077685

Reputation:

PL/SQL Dynamic Where Clause - Single Quote Around Dynamic String?

Simple question - If I'm trying to build a dynamic query and run it in a PL/SQL Stored Procedure, how do I enclose the variable in single quotes so that it is called correctly?

For example:

I first declare a variable to hold a cursor's column value. I also declare a variable to hold the dynamic query:

vTest VARCHAR(200);
l_cur_string VARCHAR2(128);

After defining my cursor, I loop through it like so:

For entry in c1
LOOP
vTest:= entry.variable;

 l_cur_string := 'SELECT ex1, ex2FROM exTable WHERE col1= || vTest;
END LOOP;

Of course, when I print out the dynamic query, it doesn't have the single quotes around vTest. I've tried adding them, but nothing I do compiles correctly.

Any help for a simple question? Thanks!

Upvotes: 0

Views: 10162

Answers (2)

antlersoft
antlersoft

Reputation: 14786

Double the single quotes that you want to appear within the string:

l_cur_string := 'SELECT ex1, ex2FROM exTable WHERE col1= '''|| vTest || '''';

Remember, though, that embedding a string directly within an SQL expression is a quick route to an exploit.

Upvotes: 8

Mez
Mez

Reputation: 4726

Try these 2 snippets


    DECLARE @sqlCommand varchar(1000)
    DECLARE @columnList varchar(75)
    DECLARE @city varchar(75)
    SET @columnList = 'CustomerID, ContactName, City'
    SET @city = '''London'''
    SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
    print @sqlCommand
    --EXEC (@sqlCommand)


    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @columnList varchar(75)
    DECLARE @city varchar(75)
    SET @columnList = 'CustomerID, ContactName, City'
    SET @city = 'London'
    SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
    print @sqlCommand
    --EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Upvotes: 1

Related Questions