Swaminathan K
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('&tabn ame');
new 2: l_tablename varchar2(40):=upper('emplo yees');
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 13
declare
l_tablename varchar2(40):=upper('&tabn ame');
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.
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('&tabn
new 2: l_tablename varchar2(40):=upper('emplo
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 13
declare
l_tablename varchar2(40):=upper('&tabn
l_columnnames varchar2(4000):='employee_
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.