Re: Question on PLS-00382
- From: Alisher Yuldashev <yuldashev@xxxxxxxxxxx>
- To: RWeiss@xxxxxxxxx
- Date: Wed, 08 Sep 2010 15:13:13 -0400
Ric,
You receive the error because c_get_tabname cursor returns ROWTYPE
(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/cursor_declaration.htm#LNPLS01313)
You can use
DECLARE
l_cur_tabname VARCHAR2(30);
l_cur_consname VARCHAR2(30);
CURSOR c_get_tabname IS
SELECT table_name
FROM user_tables;
BEGIN
OPEN c_get_tabname;
LOOP
FETCH c_get_tabname INTO l_cur_tabname;
EXIT WHEN c_get_tabname%NOTFOUND;
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints cons
WHERE cons.table_name = l_cur_tabname
AND cons.constraint_type = 'P';
DBMS_OUTPUT.PUT_LINE(l_cur_tabname || ',' || l_cur_consname);
END LOOP;
CLOSE c_get_tabname;
end;
/
or
DECLARE
l_cur_consname VARCHAR2(30);
BEGIN
FOR rec IN (SELECT table_name FROM user_tables) LOOP
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints cons
WHERE cons.table_name = rec.table_name
AND cons.constraint_type = 'P';
DBMS_OUTPUT.PUT_LINE(rec.table_name || ',' || l_cur_consname);
END LOOP;
end;
/
Thanks,
Alisher Yuldashev
www.pythian.com
On 9/8/2010 2:49 PM, Rick Weiss wrote:
Platform is 11.2.0.1 on 64-Bit Redhat
I am getting this error and can't figure out what exactly is wrong,
tried two different methods. Here they are
The first try I used was:
DECLARE
l_cur_tabname VARCHAR2(30);
l_cur_consname VARCHAR2(30);
CURSOR c_get_tabname IS
SELECT table_name
FROM user_tables@inowsaftst;
BEGIN
FOR l_cur_tabname IN c_get_tabname LOOP
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints@inowsaftst cons
WHERE cons.table_name = l_cur_tabname
AND cons.constraint_type = 'P';
END LOOP;
DBMS_OUTPUT.PUT_LINE(l_cur_tabname || ',' || l_cur_consname);
end;
/
the error that occurs is:
WHERE cons.table_name = l_cur_tabname
*
ERROR at line 14:
ORA-06550: line 14, column 28:
PLS-00382: expression is of wrong type
then I tried this:
DECLARE
l_cur_tabname user_constraints.table_name%TYPE;
l_cur_consname VARCHAR2(30);
CURSOR c_get_tabname IS
SELECT table_name
FROM user_tables@inowsaftst;
BEGIN
FOR l_cur_tabname IN c_get_tabname LOOP
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints@inowsaftst cons
WHERE cons.table_name = l_cur_tabname
AND cons.constraint_type = 'P';
END LOOP;
DBMS_OUTPUT.PUT_LINE(l_cur_tabname || ',' || l_cur_consname);
end;
/
and get this error
WHERE cons.table_name = l_cur_tabname
*
ERROR at line 14:
ORA-06550: line 14, column 28:
PLS-00382: expression is of wrong type
As usual, any help would be greatly appreciated
Rick Weiss
Oracle Database Administrator
Student Assistance Foundation
P.O.Box 203101
2500 Broadway
Helena, MT 59620-3101
rweiss@xxxxxxxxx
(406) 495-7356
----------------------------------------------
This email and any files transmitted with may be confidential and are
intended solely for the use of the individual or entity to which they
are addressed. If you have received this email in error please notify
the sender immediately and delete this e-mail from your system. This
message may contain confidential information and the contents of this
email are strictly prohibited from being disseminated, distributed,
printed or copied. Student Assistance Foundation, 2500 Broadway,
Helena, MT 59601 _http://www.safmt.org_
Other related posts:
