Solved

cursor_type emp%rowtype

Posted on 2001-09-04
4
1,769 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

Independent Software Vendors: 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

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 …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 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