Solved

cursor_type emp%rowtype

Posted on 2001-09-04
4
1,764 Views
Last Modified: 2011-09-20
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
Comment
Question by:FarazMSyed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Expert Comment

by:Irka
ID: 6453526
Use DBMS_SQL package
0
 

Accepted Solution

by:
Grinberg earned 10 total points
ID: 6456226
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
 

Expert Comment

by:shikhadh
ID: 6456411
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
 

Author Comment

by:FarazMSyed
ID: 6456774
Guys!!!
thanx for your help i think Grinberg has given me the solution ...

thanx again
Faraz
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question