Home » SQL & PL/SQL » SQL & PL/SQL » Problem with explicit cursor
Problem with explicit cursor [message #36899] Wed, 02 January 2002 08:01 Go to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Can some one tell me why I am getting this error when I compile this procedure. Thank you in advance.

SQLWKS> create or replace procedure translate_code
2> (p_column_name varchar2, p_table_name varchar2,
3> p_code varchar2, p_uncode varchar2) is
4> v_column_name varchar2(30):=p_column_name;
5> v_table_name varchar2(30):=p_table_name;
6> v_code varchar2(30):=p_code;
7> v_uncode varchar2(30):=p_uncode;
8> cursor c_table is
9> select v_column_name from v_table_name;
10> begin
11> for c1 in c_table loop
12> if c1.v_column_name = v_code then
13> update v_table_name
14> set v_column_name = v_uncode
15> where v_column_name = v_code;
16> end if;
17> end loop;
18> commit;
19> end;
20> /
MGR-00072: Warning: PROCEDURE TRANSLATE_CODE created with compilation errors.
SQLWKS>
SQLWKS> show errors
Errors for PROCEDURE TRANSLATE_CODE:
LINE/COL ERROR
--------------------------------------------------------------------------------
9/27 PLS-00201: identifier 'V_TABLE_NAME' must be declared
9/1 PL/SQL: SQL Statement ignored
12/4 PLS-00364: loop index variable 'C1' use is invalid
12/1 PL/SQL: Statement ignored

----------------------------------------------------------------------
Re: Problem with explicit cursor [message #36900 is a reply to message #36899] Wed, 02 January 2002 08:47 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Paul, no need here for the cursor, variables, or conditional. One update statement using native dynamic SQL does it:

create or replace procedure translate_code(
  p_column_name in varchar2,
  p_table_name in varchar2, 
  p_code in varchar2, 
  p_uncode in varchar2)
is
begin
  execute immediate 
    'update ' || p_table_name ||
    '   set ' || p_column_name || ' = :uncode' ||
    ' where ' || p_column_name || ' = :code'
    using p_uncode, p_code;

 commit;
end;
/


----------------------------------------------------------------------
Previous Topic: screen messages
Next Topic: Error in Stored Procedure
Goto Forum:
  


Current Time: Tue Aug 11 14:44:23 CDT 2020