Home » SQL & PL/SQL » SQL & PL/SQL » Update not detect table in Procedure
Update not detect table in Procedure [message #427317] Thu, 22 October 2009 00:16 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all guru's,

Once again i want to share sumthing that happen a bit weird to me...

Currenty i have a procedure which will change a last name from table A with last name from table B...what need to do is user input last name column from table A, the table A name and employee id column from table A as reference.

Here are the codes :

create or replace
PROCEDURE CHANGE_A (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
employee_id varchar2 default ' ') IS


v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := employee_id;
v_num number(10) := 1;
v_row integer;
cnt_tbl number(10);
cnt_emp number(10);

cnt_lname number(10);



    proc_id varchar2(30) := 'CHANGE QUERY';

    V_ERR_CD		VARCHAR2(40);
    V_ERR_NAME		VARCHAR2(100);


BEGIN


select count(distinct table_name) into cnt_tbl 
from user_tab_columns
where table_name = upper(v_tbl); 

select count(distinct column_name) into cnt_emp
from user_tab_columns
where table_name = v_tbl
and column_name = upper(v_empid);

select count(distinct column_name) into cnt_lname 
from user_tab_columns
where table_name = v_tbl
and column_name = upper(v_lname);

  if ((v_tbl = ' ') or (cnt_tbl <> 1) or (cnt_emp <> 1))
  then
  
    dbms_output.put_line('Must put Table name / Employee ID column');
  else
  
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;
  
  
          if (v_lname <> ' ') and (v_empid <> ' ') and (cnt_lname = 1) 
          then
  
           execute immediate '
             update
              (select a.'||v_lname||' a_lname, a.'||v_empid||
' a_empid, b.last_name b_lname, b.emplid b_emplid
              from '||v_tbl||' a, scrambled_chubb_empl_tbl  b
              where b.emplid = a.'||v_empid||')
              set a_lname= initcap(b_lname) ';
  
          end if;
  

    -----------------------------------------------------------------------------------
  
  
          if (v_lname <> ' ') and (v_empid <> ' ') and (cnt_lname = 1)
          then
            dbms_output.put_line(v_row||' '||v_lname||' from table '||v_tbl||
' have been scramble');         
          end if;
  

  
  -----------------------------------------------------------
          if (cnt_lname = 0) and (v_lname <> ' ') then
            dbms_output.put_line('Last Name column must be exist');
          end if;
      
          
  end if;


END CHANGE_A;
/



But when i execute the proc...it check that the table does not exist..but weird is i can normally select the table, also can update the data in the table using normal sqlplus.

Do any of u have any idea on this...it would be helpful..thanks

[Updated on: Thu, 22 October 2009 00:43] by Moderator

Report message to a moderator

Re: Update not detect table in Procedure [message #427318 is a reply to message #427317] Thu, 22 October 2009 00:18 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Do you own the table?
Re: Update not detect table in Procedure [message #427320 is a reply to message #427317] Thu, 22 October 2009 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: Update not detect table in Procedure [message #427323 is a reply to message #427317] Thu, 22 October 2009 01:17 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

No, i didn't own the table...if i log as my id...the table act as synonym...the table belongs to SYS...then in that case, i try to put sys name with tbale name like this... SYS.TABLE_A as the table name...but still, it shows the table does not exist :S
Re: Update not detect table in Procedure [message #427324 is a reply to message #427323] Thu, 22 October 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the table belongs to SYS...

Aaaaargh! You update SYS tables?

Regards
Michel

Re: Update not detect table in Procedure [message #427325 is a reply to message #427317] Thu, 22 October 2009 01:32 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Sorry, the table belong to SYSADM...
Re: Update not detect table in Procedure [message #427330 is a reply to message #427325] Thu, 22 October 2009 01:51 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! Read the link I provided.

Regards
Michel
Previous Topic: Struck in Loop (merged)
Next Topic: how to select all when value is null
Goto Forum:
  


Current Time: Thu Sep 29 03:52:44 CDT 2016

Total time taken to generate the page: 0.08171 seconds