Home » SQL & PL/SQL » SQL & PL/SQL » Reference a variable
Reference a variable [message #192977] Thu, 14 September 2006 07:45 Go to next message
jarmstrong
Messages: 2
Registered: September 2006
Junior Member
Hello,
I am trying to check all the columns in a table that end in the number "1" for a value that is to large. If the value is to large a call another procedure that I know works. The procedure compiles and runs but appears not to work. Thank you for any incite you may give. I think that the problem is where I am useing a variable to refernece a column_name in a cursor.


procedure find_errors
is

cursor table1_cursor is
select *
from table1;

cursor table1_column_names is
select COLUMN_NAME
from all_tab_columns
where table_name='table1';

v_column_name varchar2(50);


begin

/* loop cursor table1_cursor */
<<OUTER_LOOP>>
for c in table1_cursor loop
/* loop table1_column_names */
<<INNER LOOP>>
for n in table1_column_names loop
v_column_name := 'c.'||n.column_name;
/* check for column names ending in 1 */
if substr(n.column_name,-1,1) = '1' then
/* check table1_cursor for invalid entry */
if v_column_name > 1000000000 then
p_insert_error('TABLE1 ERROR','Invalid '||n.column_name||' '||v_column_name);
end if;
end if;
end loop INNER LOOP;
end loop OUTER_LOOP;

exception
when others then null;

end find_errors;
Re: Reference a variable [message #192988 is a reply to message #192977] Thu, 14 September 2006 08:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not the way to do this. As you could have seen yourself if you would have done the most basic of debugging (dbms_output of the contents of v_column_name), v_column_name does NOT get filled with the contents of the column, but with the column_name itself.
The only way to do this 'properly' is to use dynamic sql.

Just out of curiosity: Why don't you know WHICH column contains the too-large-value? Sounds like a design-flaw to me, or using a canon to shoot a mosquito as we say it here.

(By the way, table1 should probable in capitals in table1_column_names; furthermore, why filter the column_names ending on '1' AFTER the select instead of IN the select?)

Re: Reference a variable [message #192997 is a reply to message #192988] Thu, 14 September 2006 09:19 Go to previous messageGo to next message
jarmstrong
Messages: 2
Registered: September 2006
Junior Member
I was looking for the column_name in the v_column_name. I think the problem lies with trying to use it as a pointer later on. I'm working on data imported from a very very old database with hundreds of columns in a table. I was looking to save some code by handling all of he columns that end in _value on way and the columns that end in _cost another. The code I posted was just a basic example of what I was trying to do. I'll take a look at using dynamic sql.
Re: Reference a variable [message #193032 is a reply to message #192997] Thu, 14 September 2006 10:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ah ok.
Well there are quite a lot of examples here on the board. One found here. Look at the last reply to get the idea
Previous Topic: Ref Cursor Problem ... Urgent
Next Topic: Why is this Error???
Goto Forum:
  


Current Time: Sun Dec 04 06:50:25 CST 2016

Total time taken to generate the page: 0.09283 seconds