Loading...

Oracle PL/SQL Cursor Based Records

Cursor-Based records

Cursor-Based record has fields that match in name, datatype, and order to the final list of columns in the cursor’s SELECT statement.

Example 1 of Cursor Based Records
Oracle SQL Cursor Based Records
You can use the %ROWTYPE with an explicit cursor or cursor variable where each field corresponds to a column or aliased expression in the cursor SELECT statement.

In the below example, a record is declared with the same structure as an explicit cursor:
DECLARE 
 CURSOR Books_Cursor IS
  SELECT * FROM books
  WHERE author LIKE '%john%';

my_book_cur_rec Books_Cursor%ROWTYPE;

Example 2 of Cursor Based Records

Cursor emp_cur is
Select ename, eno. , hiredate
From emp;

emp_rec emp_cur%ROWTYPE

Example 3 of Cursor Based Records

CURSOR c IS
SELECT beer, price
FROM Sells
WHERE bar = 'Joe''s bar';

Bp1 c%ROWTYPE;
virtualnuggets 2127091614047525701

Post a Comment

  1. Hi There,

    Oracle PL/SQL Cursor Based Recordsbeing contrived to exist for many projects simply so it can be run will be the first to hit the wall, but those projects where the functions to make existing transactions cheaper in real world applications will find the elusive real world demand.

    wrote this dynamic query and facing below error, any suggesions??

    declare
    sql_stmt clob;
    pivot_clause DATE;
    begin

    select listagg('''' || trunc(rollup_timestamp) || '''', ',') within group (order by trunc(rollup_timestamp)) into pivot_clause from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = 'cpuUtil' and target_type='host'
    and METRIC_NAME = 'Load' group by trunc(rollup_timestamp);
    sql_stmt := 'select * from
    (
    select target_name, trunc(rollup_timestamp) tgl,round(avg(round ((AVERAGE/10),3)),3) average from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host''''
    and METRIC_NAME = ''''Load''''
    group by target_name, trunc(rollup_timestamp), rollup_timestamp
    union
    select target_name,trunc(collection_timestamp) tgl, (round(value,3))
    from MGMT$METRIC_current where
    METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host'''' and METRIC_NAME = ''''Load'''' ) PIVOT (max(average) for (tgl) in (' || pivot_clause || ')) where target_name = ''''health''''
    order by 1,2';

    execute immediate sql_stmt;
    end;
    /


    ERROR at line 1:
    ORA-01423: error encountered while checking for extra rows in exact fetch
    ORA-01489: result of string concatenation is too long
    ORA-06512: at line 5

    Anyways great write up, your efforts are much appreciated.

    Obrigado,
    Preethi

    ReplyDelete

emo-but-icon

Home item

Popular Posts

Labels

Random Posts

Flickr Photo