Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2162
  • Last Modified:

How do i do this correctly?

How do I do this correctly?
declare
      type erec is  record (
             erow aeher01.teh_er_errors%ROWTYPE,
             days number(5));
       e_rec erec;      
begin
      select  a.*,aeher02.sf_check(a.domain,a.program_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
0
Rao_S
Asked:
Rao_S
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
schwertnerCommented:
The SELECT statement possibly returns many records. The record is single value one.

0
 
Rao_SAuthor Commented:
If i run the select, works fine, no errors. (i comment out the into clause).
0
 
slightwv (䄆 Netminder) Commented:
From what I've been able to find it is because of the nested record (%ROWTYPE is seen as it's own record).

I've not been able to figure out a way around this nor can I find an example where anyone else has.

I have two work-arounds and neither is that great.

1: Two selects into the individual pieces of your record
or
2: Physically declare the individual columns in your record.


Examples below.
declare
      type erec1 is  record (
             erow char(1),
             days number(5)
       );
       e_rec1 erec1;      

      type erec2 is  record (
             erow dual%ROWTYPE,
             days number(5)
       );
       e_rec2 erec2;      
begin
      select a.*, 1 into e_rec1.erow, e_rec1.days from dual a;

      select a.* into e_rec2.erow from dual a;
      select 1 into e_rec2.days from dual a;

end;
/

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
ThankxxCommented:
Hi Expert,

may try with cursor:

declare
     cursor c1 is
     select  a.*,aeher02.sf_check(a.domain,a.program_name) days
      from aeher01.teh_er_errors a
      where a.error_log_pk=189;
      rec c1%ROWTYPE;
begin
      open c1;
      fetch c1 into rec;
      if rec.days > 15 then
          dbms_output.put_line(rec.error_log_pk);
      end if;
      close c1;
end;

thankxx
0
 
Rao_SAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
You will need to display the individual columns.  I don't know of a way to display: row.* in a single command.
0
 
Rao_SAuthor Commented:
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.program_name);
            dbms_output.put_line('v_days: ' || v_days);
            if r1.error_res_tmstp < (systimestamp - v_days) then
               insert into aeher01.teh_er_errors_history
               values r1;
            end if;
       end loop;
end;
0
 
ThankxxCommented:
Its seems OK.
0
 
Rao_SAuthor Commented:
i am going to try to use a object type and see if that will work.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now