Link to home
Create AccountLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on

Dynamic sql using execute immediate

Hi Team,

Need an help on  the dynamic sql using execute immediate , iam trying to pass tablename and columnames as input
below is the code , its failing

Enter value for tabname: employees
old   2: l_tablename varchar2(40):=upper('&tabname');
new   2: l_tablename varchar2(40):=upper('employees');
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 13

declare
l_tablename varchar2(40):=upper('&tabname');
l_columnnames varchar2(4000):='employee_id , first_name,last_name';
type t_emprec is record
(
employee_id employees.employee_id%type,
first_name employees.first_name%type,
last_name employees.last_name%type
);
l_emprec t_emprec;
sqlstmt varchar2(4000):='Select :1 from :2 where employee_id =103';
begin
execute immediate sqlstmt
into l_emprec
using l_columnnames , l_tablename
;
end;
/


Any help is  really appreciated.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer