Home » SQL & PL/SQL » SQL & PL/SQL » Invalid identifier when update
Invalid identifier when update [message #428426] Wed, 28 October 2009 06:03 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all, i got a simple error which i dont know which i need to alter...here's are my code


for f_man_cur in f_man_tbl loop

 
  if (v_fname <> ' ') then 
    EXECUTE IMMEDIATE 'SELECT COUNT('||v_fname||') FROM '||f_man_cur.m_tbl INTO v_frow;
            for i in 1.. 20 loop
            select ran_fname into v_ran_fname from
                                    (select decode(sex,'U', unisex_name,(decode(sex,'F', female_first_name, male_first_name))) ran_fname 
                                    from scrambled_chubb_empl_tbl order by dbms_random.value 
                                    ) where rownum = 1;
            
            execute immediate '
               update '||f_man_cur.m_tbl||'
                set '||v_fname||'= '||v_ran_fname||'
                where '||v_fname||' = (select '||v_fname||' from 
                                      (select row_number() over (order by rownum) as n, '||v_fname||'
                                        from '||f_man_cur.m_tbl||')
                                        where n= '||i||')'; 
              
          
          end loop;
    
  end if;
end loop;




My code here actualy like this...cursor f_man_tbl will grab a table name and v_fname contains a column name which in the table name..here the code works fine...here i got error like this

Error Encountered ORA-00904: "BELVA": invalid identifier

i've made a testing to check which part that raise this error. I note that it happen in the update process coz when i change the update to dbms_output.put_line to output the select result...it show as expected...

Kindly please guide me on this... thanks a lot
Re: Invalid identifier when update [message #428437 is a reply to message #428426] Wed, 28 October 2009 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the generated query in a variable, display it, then copy and paste it in SQL*Plus and il will give you where the error comes from.

Regards
Michel

[Updated on: Wed, 28 October 2009 07:03]

Report message to a moderator

Re: Invalid identifier when update [message #428444 is a reply to message #428437] Wed, 28 October 2009 06:43 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Sorry, i didn't much understand what u want me to do...if u want the result ...here it is :

Scramble process starts at 10-28-2009 07:42:33
Error Encountered ORA-00904: "VIRGINIA": invalid identifier

Scramble process done at 10-28-2009 07:42:38
Re: Invalid identifier when update [message #428451 is a reply to message #428444] Wed, 28 October 2009 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know any "u", did you talk to me?

I just show what you have to do to debug your code.

Regards
Michel
Re: Invalid identifier when update [message #428457 is a reply to message #428451] Wed, 28 October 2009 07:24 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Sorry, 'u' means you...its a short term at my country Smile

May i know, what do you mean by this?

Quote:

Put the generated query in a variable
Re: Invalid identifier when update [message #428459 is a reply to message #428457] Wed, 28 October 2009 07:27 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Just for your updates, i already declare the variable for v_ran_fname, v_fname and v_frow :

v_ran_fname varchar(30)
v_fname varchar(30)
v_frow number(10)

Thanks
Re: Invalid identifier when update [message #428464 is a reply to message #428459] Wed, 28 October 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of "execute immediate 'something'" use "myvar := 'something'; dbms_output.put_line(myvar);"

Regards
Michel
Re: Invalid identifier when update [message #428563 is a reply to message #428464] Wed, 28 October 2009 21:00 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi Michel,

Thanks for the reply, just want to confirm with you. What should be the datatype for myvar? Is it varchar2, number or integer....because i've declare it as varchar2(200) but when i try to execute...it shows this error...


Scramble process starts at 10-28-2009 09:55:24
Error Encountered ORA-06502: PL/SQL: numeric or value error

Scramble process done at 10-28-2009 09:55:29


Kindly correct me if i did somethings wrong here

Here are my update codes :

for f_man_cur in f_man_tbl loop

 
  if (v_fname <> ' ') then 
    EXECUTE IMMEDIATE 'SELECT COUNT('||v_fname||') FROM '||f_man_cur.m_tbl INTO v_frow;
            for i in 1.. 20 loop
            select ran_fname into v_ran_fname from
                                    (select decode(sex,'U', unisex_name,(decode(sex,'F', female_first_name, male_first_name))) ran_fname 
                                    from scrambled_chubb_empl_tbl order by dbms_random.value 
                                    ) where rownum = 1;
            
            my_var := '
               update '||f_man_cur.m_tbl||'
                set '||v_fname||'= '||v_ran_fname||'
                where '||v_fname||' = (select '||v_fname||' from 
                                      (select row_number() over (order by rownum) as n, '||v_fname||'
                                        from '||f_man_cur.m_tbl||')
                                        where n= '||i||')'; 
              
          
dbms_output.put_line(my_var);

          end loop;
    
  end if;
end loop;



Re: Invalid identifier when update [message #428596 is a reply to message #428563] Thu, 29 October 2009 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REMOVE EXCEPTION WHEN OTHERS, it hides where the error comes from and it is really STUPID to just display the message by yourself when Oracle will do it alone.

Regards
Michel

[Updated on: Thu, 29 October 2009 01:54]

Report message to a moderator

Re: Invalid identifier when update [message #428611 is a reply to message #428563] Thu, 29 October 2009 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If - as you say - this is true:
declare myvar varchar2(200)
then, regarding the error you got (ORA-06502: PL/SQL: numeric or value error), it appears that setting myvar to be a character variable larger than 200 characters (let's say, varchar2(2000)) might solve that problem.
Re: Invalid identifier when update [message #428623 is a reply to message #428563] Thu, 29 October 2009 03:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are working on this full time at the moment. Your mind is completely focused on this; nothing else is distracting you.
However, you are unable to see the forest for the trees; it is sheer impossible for you to grasp what REALLY is going on (for example the exact query that gets executed).
Now suppose we are two years from now and somebody else has to make an adjustment to this piece of code. Would you envy him/her?

Dynamic SQL complicates things to an insane level. Try to avoid it where possible, because it is a maintenance-nightmare.
Previous Topic: Backup table using create statement
Next Topic: how to restrict while inserting date information
Goto Forum:
  


Current Time: Wed Sep 28 17:44:10 CDT 2016

Total time taken to generate the page: 0.05287 seconds