OraFAQ Forum: SQL & PL/SQL » Problem with explicit cursor

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with explicit cursor

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with explicit cursor [message #36899]

Wed, 02 January 2002 08:01

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

----------------------------------------------------------------------

Report message to a moderator

Re: Problem with explicit cursor [message #36900 is a reply to message #36899]

Wed, 02 January 2002 08:47

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

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	screen messages
Next Topic:	Error in Stored Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 09:57:44 CST 2024