Shell script embedding Oracle PL/SQL code defining bind variable

Question

If I run the below script, I am getting the error SP2-0552: Bind variable "OUTRES" not declared. So, how to define the bind variable OUTRES and where to define?

#!/usr/bin/bash
sqlplus -s scott/tiger << EOF 
declare ret varchar2(10):= '0';
begin
  begin
    insert into mytab(col1) values(1);
  exception
    when others then
      ret:=ret||'1';
  end;
  select ret into :OUTRES from dual;
end;
/
quit
EOF

Answer 1

If you want to declare the bind variable in sqlplus. use the VAR keyword.

sqlplus -s scott/tiger << EOF 
VAR OUTRES NUMBER;
BEGIN
  NULL; /* Your Statements */
END;
/
EOF

You can also try quit :OUTRES and

quit :OUTRES
EOF
MYRESULT=$?
echo $MYRESULT

It output the return status in UNIX.

#!/usr/bin/bash
sqlplus -s scott/tiger << EOF 
VAR OUTRES NUMBER;
declare ret varchar2(10):= '0';
begin
  begin
    EXECUTE IMMEDIATE 'insert into mytab(col1) values(1)';
  exception
    when others then
      dbms_output.put_line(SQLERRM);
      ret:=ret||'1';
  end;
  :OUTRES := ret;
end;
/
quit :OUTRES
EOF
MYRESULT=$?
echo $MYRESULT