Rao_S
asked on
How do i do this correctly?
How do I do this correctly?
declare
type erec is record (
erow aeher01.teh_er_errors%ROWT YPE,
days number(5));
e_rec erec;
begin
select a.*,aeher02.sf_check(a.dom ain,a.prog ram_name)
into e_rec.erow,e_rec.days
from aeher01.teh_er_errors a
where a.error_log_pk=189;
if e_rec.days > 15 then
dbms_output.put_line('row: ' ||e_rec.erow);
end if;
end;
Error at line 1
ORA-06550: line 7, column 7:
PLS-00494: coercion into multiple record targets not supported
ORA-06550: line 9, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 7, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 32:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 12, column 11:
PL/SQL: Statement ignored
declare
type erec is record (
erow aeher01.teh_er_errors%ROWT
days number(5));
e_rec erec;
begin
select a.*,aeher02.sf_check(a.dom
into e_rec.erow,e_rec.days
from aeher01.teh_er_errors a
where a.error_log_pk=189;
if e_rec.days > 15 then
dbms_output.put_line('row:
end if;
end;
Error at line 1
ORA-06550: line 7, column 7:
PLS-00494: coercion into multiple record targets not supported
ORA-06550: line 9, column 7:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 7, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 32:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 12, column 11:
PL/SQL: Statement ignored
The SELECT statement possibly returns many records. The record is single value one.
ASKER
If i run the select, works fine, no errors. (i comment out the into clause).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi Thankxx,
the above curson option works fine, server output is 189.
but i ant to display the whole table row without the days field.
the above curson option works fine, server output is 189.
but i ant to display the whole table row without the days field.
You will need to display the individual columns. I don't know of a way to display: row.* in a single command.
ASKER
I found a solution for what I want to do (below), but let me know if i can improve the script with a TYPE defination of ref cur, record, table, object etc..
for now the below script works fine and i have to expand it for further processing..
set serveroutput on
declare
v_days number;
cursor c1 is
select * from aeher01.teh_er_errors a where a.error_log_pk = 6665;
begin
for r1 in c1 loop
v_days := aeher02.sf_check(r1.domain ,r1.progra m_name);
dbms_output.put_line('v_da ys: ' || v_days);
if r1.error_res_tmstp < (systimestamp - v_days) then
insert into aeher01.teh_er_errors_hist ory
values r1;
end if;
end loop;
end;
for now the below script works fine and i have to expand it for further processing..
set serveroutput on
declare
v_days number;
cursor c1 is
select * from aeher01.teh_er_errors a where a.error_log_pk = 6665;
begin
for r1 in c1 loop
v_days := aeher02.sf_check(r1.domain
dbms_output.put_line('v_da
if r1.error_res_tmstp < (systimestamp - v_days) then
insert into aeher01.teh_er_errors_hist
values r1;
end if;
end loop;
end;
Its seems OK.
ASKER
i am going to try to use a object type and see if that will work.