Hi team,
I am able to see data in my dbms_output.put_line stmnts based on what i am fetching when iam trying to insert same using insert stmnt i am facing error called columns not allowed here.
I tried in server ways by passing values like this '||v_year|' but still not working. below is script..
This is sample data of my cursor
with test as(
select sysdate date_details from dual union all
select sysdate+1 date_details from dual )
set serveroutput on;
declare
v_year varchar2(10);
v_quarter varchar2(10);
v_weekofyear varchar2(10);
v_month varchar2(10);
v_weekday varchar2(10);
v_date varchar2(10);
v_sql varchar2(32567);
cursor c1 is (select date_details from date_detail);
BEGIN
BEGIN
execute immediate 'create table date_details_targets_pros(s_year varchar2(10),quarter varchar2(10),weekofyear varchar2(10),s_month varchar2(10),
weekday varchar2(10),s_date varchar2(10))';
exception
when others then
null;
END;
begin
for i in c1
loop
begin
select
to_char(i.date_details,'YYYY')
,to_char(i.date_details,'q')
,to_char(i.date_details,'ww')
,to_char(i.date_details,'mon')
,to_char(i.date_details,'day')
,to_char(i.date_details,'ds')
into v_year ,v_quarter,v_weekofyear,v_month,v_weekday,v_date from dual;
exception
when others then
null;
END;
dbms_output.put_line(v_year);
dbms_output.put_line(v_quarter);
dbms_output.put_line(v_weekofyear);
dbms_output.put_line(v_month);
dbms_output.put_line(v_weekday);
dbms_output.put_line(v_date);
execute immediate 'insert into date_details_targets_pros (s_year,quarter,weekofyear,s_month,weekday,s_date)
values ( v_year , v_quarter , v_weekofyear,v_month, v_weekday,v_date)';
commit;
end loop;
end;
END;