Solved

cursor_type emp%rowtype

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

623 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