CODEWITHSUNDEEP
oracle-databaseselectplsqldynamic-sql
rohansr002
rohansr002

Reputation: 107

How to use single quote twice in constructing select statement

I am constructing Dynamic Sql, Dont understand how to use single quote in the select statement, which contains some variables

For E.g.

SELECT ABC.NEXTVAL,'||val_name||' Vname,'||ta_tab||' Tname,'||', Accnt_id, rowid from emp;

Where val_name and ta_tab are NOT column of the table Emp, but those are variables calculated in block, and I require them to Insert into another table directly.

How to solve This, I tried using 'Double-quotes' but it doesnt work and variable values are not printed ( it considered as string )

Upvotes: 1

Views: 1009

Answers (4)

rohansr002
rohansr002

Reputation: 107

Team, I found the answer. In oracle I have to use ' 3 times

E.g.

stmt := 'SELECT ABC.NEXTVAL,'''||val_name||''' Vname,'''||ta_tab||''' Tname,''||'', Accnt_id, rowid from emp'

Above query worked. Thanks!!!

Upvotes: 0

yannik995
yannik995

Reputation: 306

You can use literal quoting:

stmt := q'[SELECT ABC.NEXTVAL,'||val_name||' Vname,'||ta_tab||' Tname,'||', Accnt_id, rowid from emp]';

Documentation for literals can be found here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref344

Alternatively, you can use three quotes to denote a single quote:

stmt := 'SELECT ABC.NEXTVAL,'''||val_name||''' Vname,'''||ta_tab||''' Tname,'''||''', Accnt_id, rowid from emp';

The literal quoting mechanism with the Q syntax is more flexible and readable, IMO.

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

As others have mentioned, there is no need of dynamic SQL here.

However, if you just want to know how to use single-quotes for learning purpose, here is an example,

SQL> SET serveroutput ON
SQL> DECLARE
  2    val_name VARCHAR2(10);
  3    ta_tab   VARCHAR2(10);
  4    v_str    VARCHAR2(100);
  5    A        VARCHAR2(10);
  6    b        VARCHAR2(10);
  7    c        VARCHAR2(10);
  8    d        NUMBER;
  9  BEGIN
 10    val_name := 'LALIT';
 11    ta_tab   := 'TEST';
 12    v_str    :='SELECT '''||val_name||''', ename,'''||ta_tab||''', sal from emp where empno = 7788';
 13    EXECUTE IMMEDIATE v_str INTO A,b,c,d;
 14    dbms_output.put_line(a||'-'||b||'-'||c||'-'||d);
 15  END;
 16  /
LALIT-SCOTT-TEST-3000

PL/SQL procedure successfully completed.

There are other things to keep in mind, like using bind variables etc. however, it is out of scope of this topic.

Upvotes: 2

Boneist
Boneist

Reputation: 23578

Rather than using dynamic sql, why can't you just use the variables directly in the SQL? E.g.:

declare
  val_name varchar2(30) := 'fred';
  ta_tab varchar2(30) := 'bob';
begin
  insert into some_table (id, vname, tname, accnt_id, row_id)
  select abc.nextval,
         val_name vname,
         ta_tab tname,
         accnt_id,
         rowid
  from   emp;
end;
/

Upvotes: 0

Related Questions