purplexing err on the PL/SQL code... 1

[avjoshi]

Hi Guys,

I have a confusing problem,
I wrote following program to populate a table. It gives me an "Invalid Character" error but when I generate output using dbms_output and run it is works as expected.

Declare
valStr varchar2(250);
tmSlice date;
hh24 number;
mi number;
hago date;
minSeq number;
hrMin varchar2(5);
begin
dbms_output.enable(1000000);
for i in 0..1 loop
    tmSlice := trunc(sysdate)+(i/1440);
    hh24 := to_number(to_char(tmSlice,'HH24'));
    mi :=  to_number(to_char(tmSlice,'MI'));
    hago := tmSlice-(60/1440);
    if trunc(tmSlice)-trunc(hago) = 1 then
        hago := null;
    end if;
    minSeq := i+1;
    hrMin := to_char(tmSlice,'HH24:MI');
    valStr := 'insert into S_ETL_TIME_DAY values (to_date('''||
    to_char(tmSlice,'DD-MON-YYYY HH24:MI')||''',''DD-MON-YYYY HH24:MI''),'||
    to_char(hh24,'09')||','||
    to_char(mi,'09')||',';
    if hago is not null then
        valStr := valStr ||'to_date('''||
        to_char(hago,'DD-MON-YYYY HH24:MI')||''',''DD-MON-YYYY HH24:MI''),';
    else
        valStr := valStr ||'null,';
    end if;
    valStr := valStr || minSeq || ',' || 
    '''' || hrMin || ''');';
    execute immediate valStr;
    --dbms_output.put_line(valStr);
end loop;
end;
/

I get following error

Error at line 2
ORA-00911: invalid character
ORA-06512: at line 33

It generates following statements with dbms_output,

insert into S_ETL_TIME_DAY values (to_date('27-MAR-2007 00:00','DD-MON-YYYY HH24:MI'), 00, 00,null,1,'00:00');
insert into S_ETL_TIME_DAY values (to_date('27-MAR-2007 00:01','DD-MON-YYYY HH24:MI'), 00, 01,null,2,'00:01');

And they insert just fine.
Not sure what am I doing wrong.

not sure what am I doing wrong.



Anand

 

[SantaMufasa]

Anand,

The semicolon character (";") is of value only to SQL*Plus. Therefore, you do not want to include it in your "valStr" assignment. Specifically, "execute immediate" does not want to see ";".

Let us know how things go after you remove the ";".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[avjoshi]

Silly me. That was it!! Removing the semicolon solved the problem.

Thanks,
SantaMufasa!!


Anand