469,282 Members | 1,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Declaring a cursor ROWTYPE%

11
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
Jun 15 '07 #1
8 17853
debasisdas
8,127 Expert 4TB
Can you please post what/how you have tried to solve it.
Jun 15 '07 #2
Ora
11
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
Jun 15 '07 #3
debasisdas
8,127 Expert 4TB
please use this in the declareation block

Expand|Select|Wrap|Line Numbers
  1. blocking_rec blocking_sid_cursor%ROWTYPE;
  2.  
Jun 15 '07 #4
debasisdas
8,127 Expert 4TB
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.
Jun 15 '07 #5
Ora
11
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
Jun 15 '07 #6
debasisdas
8,127 Expert 4TB
may i know u are executing the code in which schema ???
Jun 15 '07 #7
Ora
11
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.
Jun 15 '07 #8
debasisdas
8,127 Expert 4TB
You are most welcome

feel free to post your queries/doubts here in the forum.
Jun 15 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Jim Garrison | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.