Oracle PL/SQL Cursor Based Records
https://oraclesqlplsqlonlinetraining.blogspot.com/2016/05/oracle-plsql-cursor-based-records.html
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
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;
Hi There,
ReplyDeleteOracle 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
Hello There,
ReplyDeleteoracle-plsql-cursor-based-records.html being 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.
I am trying to use this in a materialized view and got below error:
SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query
12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and nor primary key constraints are supported for
complex queries.
*Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
option or create a simple materialized view.
select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J
LEFT JOIN MV_INST_LOB_R IR ON
(IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)
WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL
UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;
Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2
Can you help me in converting to simple query, so that I can use simple materialized view.
Follow my new blog if you interested in just tag along me in any social media platforms!
Merci,
Preethi.
Nice post. Thanks for sharing.
ReplyDeleteOracle course in Mumbai