Unix script to read from oracle table

[Jesse Delk]

I think I get the blah thing now. Instead of read blah VAR blah....I needed "read VAR blah blah"..... It seems as my batch number is the first num/word.

[Jesse Delk]

OK,
on this line

) | read blah VAR blah

I changed to ) | read VAR blah blah

It started picking up the right number. The problem now is that when the table is empty..I get a memoryfault error in unix. Any last changes to clean this up?

[Jesse Delk]

Actually........I changed the line back to
) | read blah VAR blah

and I still get the memory fault core error when the table is empty.

[Jonathan Fife]

Hmmm, that is odd. I tested my script on 9.2.0.8, so that might be a difference between our versions.

Can you run just the sqlplus command, and if so does it crash, or is it something else crashing?

[Jesse Delk]

running just the sql I get this error


set feedback off
set escape off
set head off
set verify off
set serveroutput on
declare var number;
begin
select batch into var from ashdrum where rownum < 2;
delete from ashdrum where batch = var;
dbms_output.put_line(var);
EXCEPTION
WHEN OTHERS THEN
IF SQL%NOTFOUND THEN -- check for 'no data found'
var := 'NO_RESULTS';
dbms_output.put_line(var);
END IF;
end;
/


ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 9
ORA-01403: no data found

[Jonathan Fife]

Oh, try declaring var as a char(20). I was setting it to 'NO_RESULT', which isn't a numeric :)

[Jesse Delk]

Got it. I believe I'm golden now. Everything looks good. Table empty or not.

Thanks for all the help.

[Jesse Delk]

Thanks for all the help