Declaring a cursor ROWTYPE%

[Ora]

Hi all,

I need to fetch data from two tables into a cursor(Using inner Join), and i want to declare a ROWTYPE variable for this cursor.

Pls Guide. Its urgent.

thanks in advance

[debasisdas]

Can you please post what/how you have tried to solve it.

[Ora]

Can you please post what/how you have tried to solve it.

CREATE or REPLACE PROCEDURE SIDS
AS
blocking_rec gv$lock%ROWTYPE;
CURSOR blocking_sid_cursor
IS
SELECT * FROM gv$lock a, gv$lock b
WHERE (a.block <>0 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2);

BEGIN
OPEN blocking_sid_cursor;
LOOP
FETCH blocking_sid_cursor INTO blocking_rec;
EXIT WHEN blocking_sid_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('SID is : ' || blocking_rec.SID);
END LOOP;
CLOSE blocking_sid_cursor;
END SIDS;

This is the error I am getting.
SQL> show error
Errors for PROCEDURE SIDS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/1 PLS-00394: wrong number of values in the INTO list of a FETCH
statement

13/1 PL/SQL: SQL Statement ignored
SQL>




Pls Help Me

[debasisdas]

please use this in the declareation block

blocking_rec blocking_sid_cursor%ROWTYPE;
 

[debasisdas]

Try running this code
====================

[code]
CREATE or REPLACE PROCEDURE SIDS
AS
CURSOR blocking_sid_cursor IS SELECT a.* FROM gv$lock a, gv$lock b WHERE (a.block <>0 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2);
blocking_rec blocking_sid_cursor%ROWTYPE;
BEGIN
OPEN blocking_sid_cursor;
LOOP
FETCH blocking_sid_cursor INTO blocking_rec;
EXIT WHEN blocking_sid_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('SID is : ' || blocking_rec.SID);
END LOOP;
CLOSE blocking_sid_cursor;
END SIDS;
[/code}

Hope that solves your problem.

Note :--You are not supposed to manipulate with this system table unless you are aware of possible danger or you are a DBA.

[Ora]

Try running this code
====================

[code]
CREATE or REPLACE PROCEDURE SIDS
AS
CURSOR blocking_sid_cursor IS SELECT a.* FROM gv$lock a, gv$lock b WHERE (a.block <>0 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2);
blocking_rec blocking_sid_cursor%ROWTYPE;
BEGIN
OPEN blocking_sid_cursor;
LOOP
FETCH blocking_sid_cursor INTO blocking_rec;
EXIT WHEN blocking_sid_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('SID is : ' || blocking_rec.SID);
END LOOP;
CLOSE blocking_sid_cursor;
END SIDS;
[/code}

Hope that solves your problem.

Note :--You are not supposed to manipulate with this system table unless you are aware of possible danger or you are a DBA.

SQL> CREATE or REPLACE PROCEDURE SIDS
2 AS
3 blocking_rec blocking_sid_cursor%ROWTYPE;
4 CURSOR blocking_sid_cursor
5 IS
SELECT * FROM gv$lock a, gv$lock b
6 7 WHERE (a.block <>0 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2);
8 BEGIN
9 OPEN blocking_sid_cursor;
10 LOOP
11 FETCH blocking_sid_cursor INTO blocking_rec;
12 EXIT WHEN blocking_sid_cursor%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE('SID is : ' || blocking_rec.SID);
14 END LOOP;
15 CLOSE blocking_sid_cursor;
16 END SIDS;
17 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE SIDS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/14 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

3/14 PL/SQL: Item ignored
11/1 PL/SQL: SQL Statement ignored
11/32 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

13/1 PL/SQL: Statement ignored
13/37 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

LINE/COL ERROR
-------- -----------------------------------------------------------------

SQL>

Its Not working . Pls help me

[debasisdas]

may i know u are executing the code in which schema ???

[Ora]

may i know u are executing the code in which schema ???

Hey Debasis its working now i ran the different code. Ur amazing . Thanks alot.

[debasisdas]

You are most welcome

feel free to post your queries/doubts here in the forum.