i have table as below
CREATE TABLE req1_tb(TableName VARCHAR2(43),
ColumnName VARCHAR2(98),
Edit_ind CHAR)
please find dml for this table
insert into req1_tb VALUES('Employees','employee_id','Y');
insert into req1_tb VALUES('Employees','first_name','Y');
insert into req1_tb VALUES('Employees','last_name','N');
insert into req1_tb VALUES('Employees','email','N');
insert into req1_tb VALUES('Employees','job_id','N');
insert into req1_tb VALUES('Employees','salary','Y');
insert into req1_tb VALUES('Employees','commission_pct','Y');
insert into req1_tb VALUES('Employees','hire_date','N');
insert into req1_tb VALUES('Employees','department_id','Y');
i assumed that edit_ind column in `enter code here`below table will change dynamically
SQL> SELECT * FROM REQ1_TB;
TABLENAME COLUMNNAME EDIT_IND
------------------------------------------- --------------- ----------
Employees employee_id Y
Employees first_name Y
Employees last_name N
Employees email N
Employees job_id N
Employees salary Y
Employees commission_pct Y
Employees hire_date N
Employees department_id Y
i have created procedure that will dynamically print columns who are maked 'Y' only
CREATE OR REPLACE PROCEDURE dyn_sql_sp
AS
cols VARCHAR2(2000);
v_cols VARCHAR2(2000);
cls VARCHAR2(2000);
v_employee_id number;
emp employees%rowtype;
cnt number;
cursor tab_c
is
select columnname from req1_tb
where EDIT_IND='Y';
cursor col_c
is
select employee_id from employees;
BEGIN
for i in tab_C
loop
cols:=cols||'emp.'||i.columnname||',';
end loop;
cols:=rtrim(cols,',');
for i in col_c
loop
EXECUTE IMMEDIATE 'SELECT ' || cols || ' FROM employees WHERE employee_id = :1'
INTO emp
USING i.employee_id;
end loop;
dbms_output.put_line(cols);
Exception
When Others Then
dbms_output.put_line(sqlerrm||sqlcode);
end;
/
while executing i got error as
SQL> exec dyn_sql_sp;
ORA-01007: variable not in select list-1007