8i - Calling SQL + from Shell Script Question

I have a Korn shell script where I need to determine if a database row exists and provide a return code to the shell so I can determine if the row exists, doesn't exist, or some other error occured (invalid login, can't select from table, whatever). I'm having a problem providing the return code. Here is what I have in my test.sql file:

whenever sqlerror exit failure
var my_exit number
declare
  v_ctr number(1) :=0;
begin
  select count(*)
  into v_ctr
  from table_x
  where column_a = '&1';

  if v_ctr = 0 then
:my_exit := 1;

 else

:my_exit := 0;

  end if;
end;
/
exit :my_exit

Here is how I'm calling sqlplus from my shell script: sqlplus -S userid/password @test.sql wherevalue

SqlPlus doesn't seem to like my 'exit :my_exit' statement. What am I doing wrong? Is this even the right way to approach this?

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net