Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1786
  • Last Modified:

cursor_type emp%rowtype

here is my problem ....

i need to create a procedure e.g
Create or replace procedure abc
(cur_type emp%rowtype);
...... and so on

if i need to call this procedure in a pl/sql block e.g.

declare
cur c1 is select * from emp;
begin
for rec in c1 loop
abc(cursor_type);
blah blah ...

my point is i need to dynamic this cursor_type ...  whatever table I select in c1 i wouldn't change that abc procedure with cursor_type emp%rowtype to
dept%rowtype (if i am selecting from dept table in c1 cursor)... there must be something dynamic in it ... i hope i clear my point ...if anyone have any idea to solve it ....

tx
0
FarazMSyed
Asked:
FarazMSyed
1 Solution
 
IrkaCommented:
Use DBMS_SQL package
0
 
GrinbergCommented:
I believe you can't use c%ROWTYPE with "weak" cursor types. But find below some similar example, which uses cursor parameter instead of "rowtype" parameter;

Define package:
------------------------------
PACKAGE test_pac IS
  TYPE c_weak_type IS REF CURSOR;
  FUNCTION count_records (c c_weak_type) RETURN INTEGER;
END test_pac;

PACKAGE BODY test_pac is
 FUNCTION count_records(c c_weak_type) RETURN INTEGER IS  
  i INTEGER := 0; j INTEGER;
 BEGIN
  LOOP
   FETCH c INTO j;
   EXIT WHEN c%NOTFOUND;
   i := i+1;
  END LOOP;  
  RETURN i;
 END count_records;
END test_pac;
---------------------

and then execute in SQL*Plus:
------------------------------------
declare c test_pac.c_weak_type;
begin
 open c for 'SELECT 1 FROM emp';
 dbms_output.put_line(test_pac.COUNT_RECORDS(c));
end;
---------------------------------  

Hope this helps
0
 
shikhadhCommented:
u cannot use %rowtype in procedures straight away in declaration statements. but u can define a cursor inside the body and then return the result in a variable which can be dynamically called in sql.
0
 
FarazMSyedAuthor Commented:
Guys!!!
thanx for your help i think Grinberg has given me the solution ...

thanx again
Faraz
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now