1

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 )

Jens
  • 67,715
  • 15
  • 98
  • 113
rohansr002
  • 107
  • 7
  • This is not a Duplicate question, I already gone through that question. If I use '' like this below is the out put. SELECT ABC.NEXTVAL,'||val_name||' Vname,'||ta_tab||' Tname,'||',Accnt_id, rowid from emp; – rohansr002 Feb 03 '16 at 09:52
  • googled `plsql using variable in select statements` and got this [answer](http://stackoverflow.com/questions/6861012/use-a-declared-variable-in-a-select-statement) . Let us know if this doesn't satisfy your needs. – Utsav Feb 03 '16 at 09:53
  • 1
    Why are you trying to use dynamic SQL for this; and what are you doing with the returned data? Is this supposed to be part of an `insert into ... select ...` statement? – Alex Poole Feb 03 '16 at 10:01

4 Answers4

2

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

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.

yannik995
  • 307
  • 1
  • 12
  • 2
    It is wrong. You are enclosing the variables into the quote literal as well. – Lalit Kumar B Feb 03 '16 at 09:50
  • It doesnt work , it gives below issues....... If I use '' like this below is the out put. SELECT ABC.NEXTVAL,'||val_name||' Vname,'||ta_tab||' Tname,'||',Accnt_id, rowid from emp; – rohansr002 Feb 03 '16 at 09:56
0

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;
/
Boneist
  • 22,910
  • 1
  • 25
  • 40
0

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!!!

rohansr002
  • 107
  • 7