I am new to unix and trying to execute sql script from unix. I have function already in database and trying to execute it from Cygwin unix and getting stuck. Can someone guide me where I wrote the shell script wrong
sqlplus -s scott/tiger
declare
a number:=3;
b number:=4;
c number;
d number;
begin
d := fn_multi_returns(a,b,c);
commit;
dbms_output.put_line(d);
end;
exit
Two things:
(1) you forgot the forward-slash ("/") character immediately after the end of the PL/SQL block. Oracle stores the contents of a PL/SQL block in a buffer file in your client machine. The forward-slash character is a signal to Oracle to execute the buffer contents.
(2) you did not specify the here-document. That is required by your Unix/Linux shell to capture a multi-line string that is supposed to be fed to the sqlplus command.
Note carefully the code in red color below:
$
$
$ cat -n test_orcl_block.sh
1 #!/usr/bin/bash
2 sqlplus -s scott/tiger@your_db << EOF
3 set time off timing off
4 declare
5 a number := 3;
6 b varchar2(20) := 'Hello, World!';
7 c date;
8 d number;
9 begin
10 c := TRUNC (SYSDATE, 'yyyy');
11 d := -123.456789;
12 dbms_output.put_line ('a = '|| a);
13 dbms_output.put_line ('b = '|| b);
14 dbms_output.put_line ('c = '|| c);
15 dbms_output.put_line ('d = '|| d);
16 end;
17 /
18 exit
19 EOF
$
$
$ ./test_orcl_block.sh
a = 3
b = Hello, World!
c = 01-JAN-11
d = -123.456789
PL/SQL procedure successfully completed.
$
$
$
tyler_durden
I modified my code as follows. It gives me following error:
SP2-0735: unknown SET option beginning "severoutpu..."
How do specify set serveroutput on, please advise
sqlplus -s scott/tiger << EOF
set severoutput on;
declare
a number:=3;
b number:=4;
c number;
d number;
begin
d := fn_multi_returns(a,b,c);
commit;
dbms_output.put_line(d);
end;
/
exit
EOF
---------- Post updated at 01:19 PM ---------- Previous update was at 01:12 PM ----------
I got it, it was my bad. I coded as "Set severoutput on" instead of "Set serveroutput on"
sqlplus -s scott/tiger << EOF
set serveroutput on
declare
a number:=3;
b number:=4;
c number;
d number;
begin
d := fn_multi_returns(a,b,c);
commit;
dbms_output.put_line(d);
end;
/
exit
EOF