Thanks for the question, ls.

Asked: December 23, 2001 - 9:00 pm UTC

Last updated: February 25, 2018 - 7:59 am UTC

Version: 8.1.7.2

Viewed 10K+ times! This question is

You Asked

Hi Tom

We have an anonymous pl/sql block which looks like follows but using dbms_sql (the following doesnt work)

declare
vRows number;
begin
for i in (select * from user_tables)
loop
select count(*)
into vRows
from i.table_name;
dbms_output.put_line(vRows);
end loop;
end;
/

I also tried to use excute immediate to use bind variables

declare
vRows number;
vStmt varchar2(128);
begin
for i in (select * from user_tables)
loop
vStmt := 'select count(*)
from :tabname';
execute immediate 'vStmt' into vRows using i.table_name;
dbms_output.put_line(vRows);
end loop;
end;
/

declare
vRows number;
vTab varchar2(36);
vStmt varchar2(128);
begin
select table_name
into vTab
from user_tables
where table_name='EMP';
vStmt := 'select count(*)
from :vTab';
execute immediate 'vStmt' into vRows using vTab;
dbms_output.put_line(vTab);
end;
/

none of this works, the only way is to use dbms_sql?

and Tom said...

You cannot bind an identifier. You can only BIND where a character string constant can go. For example the following is legitimate:

select * from emp where ename = :x;

since you can code:

select * from emp where ename = 'X';

but the following it NOT legal:

select * from :x;

since you cannot:

select * from 'X';

To do what you want, you simply code:

execute immediate 'select count(*) from ' || vTab into vRows;

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

variable in FROM clause inside pl/sql

Kathy Overcash, December 24, 2001 - 5:14 pm UTC

I'm converting my SQL Server databases to Oracle. I just looked at one of the stored procedures that I have to convert. It contains a sql string that is executed using Exec. Your example is exactly what I need to get the job done.
I bought your book and look forward to reading it. It just arrived in the mail today.
I love your web-site.

why concatenate would work?

ls, January 03, 2002 - 9:50 am UTC

hi tom

have been on vacations so couldnt see this answer until today!

I wonder why

execute immediate 'select count(*) from ' || vTab into vRows

works?. I thought we would get an error since there is no table name inside the statement we are executing, how did || do this magic :-?

January 03, 2002 - 10:01 am UTC

you are building a string

'select count(*) from ' || vTab

say vtab := 'DUAL'

you built the string 'select count(*) from dual'

that works. you cannot bind an identifier as you tried, you can only use binds where a character string constant would work. For example, given:

select * from emp where ename = :x

I know that is OK since: "select * from emp where ename = 'X'" is ok. however:

select * from :x

I know that is NOT ok since "select * from 'X'" is not valid.

Same Error : Missing Key Word

Ankit Jaiswal, February 16, 2018 - 7:51 am UTC

Tom, what is missing here could not identify.

DECLARE
V_THPO Number(10,2);
Table_Name Varchar(30);
SQLUpdate Varchar(1000);

BEGIN
Table_Name:='ABB';

SQLUpdate:='SELECT TH_PO INTO V_THPO FROM ( SELECT Distinct( TH_PO ),dense_rank() over (order by TH_PO asc) THPO FROM '||Table_Name||' WHERE Rownum<=30) WHERE THPO =2';

EXECUTE IMMEDIATE SQLUpdate;
dbms_output.put_line('Name: '||V_THPO);
END;

February 16, 2018 - 11:38 am UTC

When using exec immediate, you put INTO outside the statement:

execute immediate 'select * from tab' into var;

Ankit Jaiswal, February 16, 2018 - 12:26 pm UTC

Thanks Chris !

IT worked.

Just one Confirmation after this my understanding says as " INTO " Statement can not be part of string so can not execute query by first building string and assign it in string variable and than execute it using below statement.

EXECUTE IMMEDIATE SQLUpdate;

as I attempted OR there is any way to make "INTO" Statement Part of String.

February 17, 2018 - 1:56 am UTC

No, the into/using clauses sit outside the statement, because they are the source/target values rather than the statement itself.

No Data Found Error

Ankit Jaiswal, February 16, 2018 - 6:51 pm UTC

Below query is only working when i assign signle name in variable "Table_Name" but when trying to execute it for all tables using cursor , giving No data Found Error.

Can you help me to know why applying cursor changing it's behavior.

DECLARE
CURSOR cursor_Table_Name IS Select SCRIP_SYMBOL FROM CREATE_TABLE_SCRIP_LIST;
Table_Name CREATE_TABLE_SCRIP_LIST.SCRIP_SYMBOL%type;

SERIES_EQ VARCHAR(3);

V_THPO Number(10,2); V_TOPO Number(10,2); V_POTL Number(10,2); V_POTC Number(10,2);
V_THPC Number(10,2); V_TOPC Number(10,2); V_PCTL Number(10,2); V_PCTC Number(10,2);
V_THPH Number(10,2); V_TOPH Number(10,2); V_PHTL Number(10,2); V_PHTC Number(10,2);
V_THPL Number(10,2); V_TOPL Number(10,2); V_PLTL Number(10,2); V_PLTC Number(10,2);
V_HO Number(10,2); V_OL Number(10,2); V_OC Number(10,2); V_HL Number(10,2);
V_CGU Number(10,2); V_CGD Number(10,2); V_SGU Number(10,2); V_SGD Number(10,2);

--Table_Name Varchar(30);
SQLUpdate Varchar(1000);

BEGIN
Table_Name:='NMDC';
/*
OPEN cursor_Table_Name;
LOOP
FETCH cursor_Table_Name into Table_Name;
EXIT WHEN cursor_Table_Name%notfound; */

EXECUTE IMMEDIATE 'SELECT TH_PO FROM ( SELECT Distinct( TH_PO ),dense_rank() over (order by TH_PO asc) THPO FROM '|| Table_Name||' WHERE Rownum<=30) WHERE THPO =2' INTO V_THPO;
EXECUTE IMMEDIATE 'SELECT TO_PO FROM ( SELECT Distinct( TO_PO ),dense_rank() over (order by TO_PO asc) TOPO FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPO =2' INTO V_TOPO ;
EXECUTE IMMEDIATE 'SELECT PO_TL FROM ( SELECT Distinct( PO_TL ),dense_rank() over (order by PO_TL asc) POTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE POTL =2' INTO V_POTL ;
EXECUTE IMMEDIATE 'SELECT PO_TC FROM ( SELECT Distinct( PO_TC ),dense_rank() over (order by PO_TC asc) POTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE POTC =2' INTO V_POTC ;

EXECUTE IMMEDIATE ' SELECT TH_PC FROM ( SELECT Distinct( TH_PC ),dense_rank() over (order by TH_PC asc) THPC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPC =2' INTO V_THPC ;
EXECUTE IMMEDIATE ' SELECT TO_PC FROM ( SELECT Distinct( TO_PC ),dense_rank() over (order by TO_PC asc) TOPC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPC =2' INTO V_TOPC ;
EXECUTE IMMEDIATE ' SELECT PC_TL FROM ( SELECT Distinct( PC_TL ),dense_rank() over (order by PC_TL asc) PCTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PCTL =2' INTO V_PCTL ;
EXECUTE IMMEDIATE ' SELECT PC_TC FROM ( SELECT Distinct( PC_TC ),dense_rank() over (order by PC_TC asc) PCTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PCTC =2' INTO V_PCTC ;

EXECUTE IMMEDIATE ' SELECT TH_PH FROM ( SELECT Distinct( TH_PH ),dense_rank() over (order by TH_PH asc) THPH FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPH =2' INTO V_THPH ;
EXECUTE IMMEDIATE ' SELECT TO_PH FROM ( SELECT Distinct( TO_PH ),dense_rank() over (order by TO_PH asc) TOPH FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPH =2' INTO V_TOPH ;
EXECUTE IMMEDIATE ' SELECT PH_TL FROM ( SELECT Distinct( PH_TL ),dense_rank() over (order by PH_TL asc) PHTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PHTL =2' INTO V_PHTL ;
EXECUTE IMMEDIATE ' SELECT PH_TC FROM ( SELECT Distinct( PH_TC ),dense_rank() over (order by PH_TC asc) PHTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PHTC =2' INTO V_PHTC ;

EXECUTE IMMEDIATE ' SELECT TH_PL FROM ( SELECT Distinct( TH_PL ),dense_rank() over (order by TH_PL asc) THPL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE THPL =2' INTO V_THPL ;
EXECUTE IMMEDIATE ' SELECT TO_PL FROM ( SELECT Distinct( TO_PL ),dense_rank() over (order by TO_PL asc) TOPL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE TOPL =2' INTO V_TOPL ;
EXECUTE IMMEDIATE ' SELECT PL_TL FROM ( SELECT Distinct( PL_TL ),dense_rank() over (order by PL_TL asc) PLTL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PLTL =2' INTO V_PLTL ;
EXECUTE IMMEDIATE ' SELECT PL_TC FROM ( SELECT Distinct( PL_TC ),dense_rank() over (order by PL_TC asc) PLTC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE PLTC =2' INTO V_PLTC ;

EXECUTE IMMEDIATE ' SELECT High_Open FROM ( SELECT Distinct( High_Open ),dense_rank() over (order by High_Open asc) HO FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE HO =2' INTO V_HO ;
EXECUTE IMMEDIATE ' SELECT Open_Low FROM ( SELECT Distinct( Open_Low ),dense_rank() over (order by Open_Low asc) OL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE OL =2' INTO V_OL ;
EXECUTE IMMEDIATE ' SELECT Open_Close FROM ( SELECT Distinct( Open_Close ),dense_rank() over (order by Open_Close asc) OC FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE OC =2' INTO V_OC ;
EXECUTE IMMEDIATE ' SELECT High_Low FROM ( SELECT Distinct( High_Low ),dense_rank() over (order by High_Low asc) HL FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE HL =2' INTO V_HL ;

EXECUTE IMMEDIATE ' SELECT C_Gap_up FROM ( SELECT Distinct( C_Gap_up ),dense_rank() over (order by C_Gap_up asc) CGU FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE CGU =2' INTO V_CGU;
EXECUTE IMMEDIATE ' SELECT C_Gap_dwn FROM ( SELECT Distinct( C_Gap_dwn ),dense_rank() over (order by C_Gap_dwn asc) CGD FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE CGD =2' INTO V_CGD ;
EXECUTE IMMEDIATE ' SELECT S_Gap_up FROM ( SELECT Distinct( S_Gap_up ), dense_rank() over (order by S_Gap_up asc) SGU FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE SGU =2' INTO V_SGU ;
EXECUTE IMMEDIATE ' SELECT S_Gap_dwn FROM ( SELECT Distinct( S_Gap_dwn ),dense_rank() over (order by S_Gap_dwn asc) SGD FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE SGD =2' INTO V_SGD ;

UPDATE NSE_SCRIP_MIN_VALUE_TRADED_REP

SET TH_PO_30= V_THPO ,TO_PO_30=V_TOPO ,PO_TL_30=V_POTL ,PO_TC_30=V_POTC ,
TH_PC_30=V_THPC ,TO_PC_30=V_TOPC ,PC_TL_30=V_PCTL ,PC_TC_30=V_PCTC ,
TH_PH_30=V_THPH ,TO_PH_30=V_TOPH ,PH_TL_30=V_PHTL ,PH_TC_30=V_PHTC,
TH_PL_30=V_THPL,TO_PL_30=V_TOPL ,PL_TL_30=V_PLTL,PL_TC_30=V_PLTC,
High_Open_30=V_HO ,Open_Low_30=V_OL ,Open_Close_30=V_OC ,High_Low_30=V_HL,
C_Gap_up_30=V_CGU ,C_Gap_dwn_30=V_CGD ,S_Gap_up_30=V_SGU ,S_Gap_dwn_30=V_SGD

WHERE NSE_SCRIP_MIN_VALUE_TRADED_REP.SCRIP_SYMBOL=Table_Name;

/* END LOOP;
CLOSE cursor_Table_Name; */

END;

February 17, 2018 - 1:58 am UTC

Run the routine and show us *exactly* the full error stack

No Data Found Error Report on running routine

Ankit Jaiswal, February 24, 2018 - 1:26 pm UTC

Error report -
ORA-01403: no data found
ORA-06512: at line 54
01403. 00000 - "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.

February 25, 2018 - 7:59 am UTC

You are not getting some rows from the table in one or more of your queries

SQL> create table t ( S_Gap_dwn int );

Table created.

SQL> declare
  2    V_SGD int;
  3    Table_Name varchar2(30) := 'T';
  4  begin
  5    EXECUTE IMMEDIATE ' SELECT S_Gap_dwn FROM ( SELECT Distinct( S_Gap_dwn ),dense_rank() over (order by S_Gap_dwn asc) SGD FROM ' || Table_Name || ' WHERE Rownum<=30) WHERE SGD =2' INTO V_SGD ;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

Breadcrumb

Question and Answer