i am trying to display a record rowtype in oracle, i am getting

SQL Error [900] [42000]: ORA-00900: invalid SQL statement at line 7

here :

intraftrecord xyz%rowtype;

i can't seem to figure it out.

CREATE OR REPLACE PROCEDURE intraftresponseprocedure
IS 
CURSOR xyz IS
select REGEXP_REPLACE(RESPONSE_PARAM , '-', '') from (
    select RESPONSE_PARAM from AUDIT_LOG WHERE ACTIVITY ='Intra Bank Funds Transfer' order by id asc
);
intraftrecord xyz%rowtype;
BEGIN
    OPEN xyz;
LOOP
FETCH xyz INTO intraftrecord;
EXIT WHEN intraftrecord%notfound;
dbms_output.put_line(intraftrecord);
END LOOP;
END;

CodePudding user response：

Three issues -

1. It's the cursor that has a %found attribute, not the record.
2. dbms_output.put_line() takes a single varchar2 as argument, not xyz%rowtype
3. You'll need to refer to the specific attribute of intraftrecord that you want to display, so the cursor needs to give it a name.

Fixed version:

create or replace procedure intraftresponseprocedure
as
    cursor xyz is
        select regexp_replace(response_param, '-', '') as response
        from   ( select response_param
                 from   audit_log
                 where  activity = 'Intra Bank Funds Transfer'
                 order by id asc );

    intraftrecord xyz%rowtype;
begin
    open xyz;

    loop
        fetch xyz into intraftrecord;
        exit when xyz%notfound;
        dbms_output.put_line(intraftrecord.response);
    end loop;
end;

The cursor could also be simplified, and there's no need for an explicitly declared and fetched cursor and record. Simplified version:

create or replace procedure intraftresponseprocedure
as
begin
    for r in (
        select regexp_replace(response_param, '-', '') as response
        from   audit_log
        where  activity = 'Intra Bank Funds Transfer'
        order by id asc
    )
    loop
        dbms_output.put_line(r.response);
    end loop;
end;

CodePudding user response：

You have to specify the column name in print statement -

CREATE OR REPLACE PROCEDURE intraftresponseprocedure
IS 
CURSOR xyz IS
select REGEXP_REPLACE(RESPONSE_PARAM , '-', '') col1 from (
    select RESPONSE_PARAM from AUDIT_LOG WHERE ACTIVITY ='Intra Bank Funds Transfer' order by id asc
);
intraftrecord xyz%rowtype;
BEGIN
    OPEN xyz;
LOOP
FETCH xyz INTO intraftrecord;
EXIT WHEN xyz%notfound;
dbms_output.put_line(intraftrecord.col1);
END LOOP;
END;