troubleshooting Question

Dynamic sql using execute immediate

Avatar of sam_2012
sam_2012 asked on
Oracle Database* Oracle PL/SQLSQL
1 Comment1 Solution33 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros