can anyone please help me with this:
i have written a shell script and a stored procedure which has one OUT parameter. now i want to use that out parameter as an input to the unix script but i am getting an error as variable not found. below are the unix scripts and stored procedure...
-----------------------------------------------------
#!/bin/ksh
. /users/lrep/set10.2.0
export ORACLE_SID=SID123
var = `sqlplus �s login/password<<ENDOFSQL
set feedback off
exec NEW_PACKAGE.NEW_PROC;
exit;
ENDOFSQL`
echo $var
-------------------------------------------------------------
create or replace PROCEDURE NEW_PROC IS
cnt number(10) output
begin
select cnt = count(*) from emp;
end NEW_PROC;
----------------------------------------------------
Error: "var: not found"
----------------------------------------------------
Can you strip off the spaces around the equal to sign and try..
var=`sqlplus -s l...
..
After trying with what you said... now I am getting a below error...
Error at line 1 ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in all to �NEW_PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
can you please help me in this?
---------- Post updated at 12:51 PM ---------- Previous update was at 12:41 PM ----------
Please note I had changed the stored procedure as below:
create or replace NEW_PROC (cnt out number) is
declare cnt int
begin
set cnt = (select count(*) from table)
end;
---------- Post updated at 04:11 PM ---------- Previous update was at 12:51 PM ----------
can anyone please help in resolving the problem i have posted??
As you would know the error is related to Oracle. Please check the procedure you have written, it has some syntax errors. Or explain what your trying to achieve with the procedure. If your trying to read the row count of a table to a unix shell variable then try/compile either of the below procedures..
create or replace NEW_PROC is
begin
select count(*) from table;
end;
or
create or replace NEW_PROC is
cnt int;
begin
select count(*) into cnt from table;
dbms_output.put_line(cnt);
end;
1 Like
