Please help me how to pass some unix vairable to oracle. I have used below , but not displaying passed (inval) value.
calling()
{
sqlplus -s $1/$2@$3 <<EOF
begin
exec call_sql($4);
end;
exit
EOF
}
calling user pwd inst value1
-----------------------------------------------------------------
Procedure is :
create or replace procedure call_sql(inval in varchar2) as
begin
dbms_output.put_line('value is '||inval);
end;
Single quotes around the $4 token, such as '$4'...
Thanks Curleb. but script is not calling procedure..How to call this...?
What IS it producing? Note you don't appear to be redirecting your output within this calling() function so your script might be doing it...or it might not.
Help me how to proceed.?
Too many things are wrong here -
1) "exec" or "execute" is a sqlplus command that wraps the "begin ... end" block around whatever follows it. So you cannot put a "begin ... end" block around "exec" itself.
2) If the input parameter is a string, then add the string delimiter to it, otherwise Oracle will consider it an identifier.
3) Of course, just because the input parameter is declared a VARCHAR2 in the procedure signature doesn't mean that you cannot pass a number/integer etc. Oracle does an implicit conversion of the passed value to VARCHAR2 in that case.
4) If the input parameter is a string that has multiple words separated by whitespace, then enclose it within double-quotes in the shell script, at the function call, that is.
HTH,
tyler_durden
Hi tyler,
Thanks for your input. I have tried my level.
file_cnt=`wc -l $1 | awk '{print $1}'`
sqlplus -s uname/pwd@dbins << EOF
select count(1) into :t_cnt from $2;
insert into t_table(f_name, t_name, f_cnt,loaded) values ('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF
sh o2.sh filename table_name
COUNT(1)
----------
3448
SP2-0552: Bind variable "T_CNT" not declared.
Commit complete.
calling like below:
sh o2.sh file_name table_name
Please tell me how to insert t_cnt to oracle.
edit by bakunin: you surely have just forgotten the code-tags. I inserted them for you.
---------- Post updated at 12:00 PM ---------- Previous update was at 11:05 AM ----------
It will be great if anybody provide solution my problem.
Advance Thanks.
It would be great if you could tell us what it was actually doing wrong instead of just saying it's not working. When I try your code(modified slightly to just print the query) it seems to work fine:
#!/bin/bash
file_cnt=31337
cat << EOF
select count(1) into :t_cnt from $2;
insert into t_table(f_name, t_name, f_cnt,loaded) values
('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF
$ ./sqlplus.sh a b
select count(1) into :t_cnt from b;
insert into t_table(f_name, t_name, f_cnt,loaded) values
('a','b',31337,:t_cnt);
commit;
exit;
Is the highlighted portion what the actual query should be? If not, what do you want the query to be?
You need to declare the t_cnt bind variable before you can use it:
var t_cnt number
You need to do that before the SELECT statement.
Isn't the Oracle error message self-explanatory ?
You did not declare the bind variable, and you expect Oracle to figure out what "t_cnt" is !!
Also, since you are using PL/SQL syntax, you'll have to use "exec" or "execute" or "begin ... end" block structure around your SELECT statement.
HTH,
tyler_durden
Now. there is no error msg. But value is not inserted.
file_cnt=`wc -l $1 | awk '{print $1}'`
sqlplus -s uname/pwd@dbins << EOF
var t_cnt number;
select count(1) into :t_cnt from $2;
insert into t_table(f_name, t_name, f_cnt,loaded) values ('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF
Output is:
COUNT(1)
----------
3448
1 row created.
Commit complete.
That's because you are using PL/SQL syntax in sqlplus without the PL/SQL block structure.
tyler_durden
Any idea,, How do insert this value?
By using PL/SQL block structure around your SELECT statement.
tyler_durden
begin
select count(1) into :t_cnt from $2;
end;
/
file_cnt=`wc -l $1 | awk '{print $1}'`
sqlplus -s uname/pwd@dbins << EOF
var t_cnt number;
begin
select count(1) into :t_cnt from $2;
end;
insert into t_table(f_name, t_name, f_cnt,loaded) values ('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF
No output.
You forgot the '/'. The forward slash executes the pl/sql block.
Now it is Working... It is really Great man.
file_cnt=`wc -l $1 | awk '{print $1}'`
sqlplus -s uname/pwd@dbins << EOF
var t_cnt number;
begin
select count(1) into :t_cnt from $2;
end;
/
insert into t_table(f_name, t_name, f_cnt,loaded) values ('$1','$2',$file_cnt,:t_cnt);
commit;
exit;
EOF