Re: Question on PLS-00382

DECLARE
        l_cur_tabname varchar2(30);
        l_cur_consname VARCHAR2(30);
        CURSOR c_get_tabname IS
        SELECT table_name
        FROM user_tables_at_inowsaftst;
BEGIN
        FOR c_tabname IN c_get_tabname LOOP
        begin
                l_cur_tabname:= c_tabname.table_name;
                SELECT cons.constraint_name
                INTO l_cur_consname
                FROM user_constraints_at_inowsafts
                WHERE cons.table_name = c_tabname.table_name
                AND cons.constraint_type = 'P';
        exception when no_data_found then
                null;
        end;
        DBMS_OUTPUT.PUT_LINE(c_tabname.table_name || ',' || l_cur_consname);
        END LOOP;
end;
/

 
David Fitzjarrell





________________________________
From: Rick Weiss <RWeiss_at_safmt.org>
To: oracle-l_at_freelists.org
Sent: Wed, September 8, 2010 2:49:27 PM
Subject: Question on PLS-00382


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_at_inowsaftst;

BEGIN
FOR l_cur_tabname IN c_get_tabname LOOP
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints_at_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_at_inowsaftst;

BEGIN
FOR l_cur_tabname IN c_get_tabname LOOP
SELECT cons.constraint_name
INTO l_cur_consname
FROM user_constraints_at_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_at_safmt.org
(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 



      

--
http://www.freelists.org/webpage/oracle-l