Solved

cursor_type emp%rowtype

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 5 70
query in Oracle forms Builder 2 42
Oracle Pivot Question 8 45
How to free up undo space? 3 31
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now