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
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
#!/usr/bin/bash
sqlplus -s scott/tiger << EOF
declare
ret varchar2(10):= '0';
OUTRES varchar2(10);
begin
begin
insert into mytab(col1) values(1);
exception
when others then
ret:=ret||'1';
end;
select ret into OUTRES from dual;
dbms_output.put_line('Value of OUTRES is' || OUTRES);
end;
/
quit
EOF
