Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Passing the result of an anonymous pl/sql block to a shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Passing the result of an anonymous pl/sql block to a shell script
# 1  
Old 05-28-2013
Code Passing the result of an anonymous pl/sql block to a shell script
Hello,

Here is the code i have written to get the count of a plsql query back to the unix.

Code:
function checkforCOIDs
{
countcheck=`sqlplus -s $1/$2@$3
whenever oserror exit sql.oscode rollback
whenever sqlerror exit sql.sqlcode rollback
set serverout on size 2000;
set head off feedback off pages 0;

DECLARE
count_coid INTEGER;

BEGIN
SELECT COUNT(*) INTO count_coid
from XXX.COID_CONTROL 
where Load_Flag = 'Y' and 
Load_Date = (SELECT TO_NUMBER(EXTRACT(DAY FROM Sysdate - 1)) FROM DUAL);

dbms_output.put_line(count_coid);
END;
/
commit;
exit
eof`
echo ${countcheck}
}

I have tried everything, but still while running my script, it throws an error "checkforCOIDs[22]: 0403-057 Syntax error at line 11 : `(' is not expected."

Can anyone help me please..!!
Last edited by Scott; 05-28-2013 at 06:47 PM.. Reason: Please use code tags
# 2  
Old 05-28-2013
Your here document syntax is incorrect.

Correction:
Code:
countcheck=`sqlplus -s $1/$2@$3 << eof

This User Gave Thanks to Yoda For This Post:
santosh2eee
# 3  
Old 05-28-2013
Dint see the small thing..
Thank you so much Yoda..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Calling an Anonymous Block through shell script

Hi, My requirement is to load a LONG datatype data value from one table to another as direct access does not work (DB: ORACLE). eg. SELECT *FROM ALL_VIEWS WHERE TEXT LIKE '%<SEARCH_STRING>%'; As an alternate we are creating a table and trying to insert in it from ALL_VIEWS as direct insert... (2 Replies)
Discussion started by: sanjaydubey2006
2 Replies

2. Shell Programming and Scripting

Passing filename dynamically in SPOOL of SQL*PLUS in shell script

Hi all, I am executing shell script in which I am using SQLLDR In this SQLLDR I am passing text file having PL/SQL script. This script will produce some formated output, this output I have to spool in another text file. Currently I have given this in script file as following Spool... (2 Replies)
Discussion started by: shekharjchandra
2 Replies

3. Shell Programming and Scripting

Help required in passing multiple arguments from a shell script to a pl/sql block

Hi, hope everyone are fine. Please find my issue below, and I request your help in the same In a configuration file, i have a variable defined as below TEST = 'One','Two','Three' I am trying to pass this variable in to a sql script which is define in a pl/sql block as follows, In the... (1 Reply)
Discussion started by: ramakanth_burra
1 Replies

4. Shell Programming and Scripting

call shell script from pl/sql block

Hi Experts, I want to call script_name.ksh as many time as id in customer table and also pass it as a parameter to script. someting Like below. for i in select id from customer do ./script_name.ksh $i & done I have figured out how to have ID from customer but now how to call... (3 Replies)
Discussion started by: Opamps123
3 Replies

5. Shell Programming and Scripting

Executing a shell script from a PL / SQL Block

Hi, I need to call a shell script present on solaris server from within a PL / SQL block. Kindly suggest.. Thanks Sudhir (1 Reply)
Discussion started by: sudhird
1 Replies

6. Shell Programming and Scripting

How to assign the result of a SQL command to more than one variable in shell script.

Hi Friends... Please assist me to assign the result of a SQL query that results two column, to two variables. Pls find the below code that I write for assigning one column to one variable. and please correct if anything wrong.. #! /bin/sh no=' sqlplus -s uname/password@DBname... (4 Replies)
Discussion started by: little_wonder
4 Replies

7. Shell Programming and Scripting

Passing argument to a pl/sql block

Hi, How can I pass an argument to a pl/sql block through perl/unix shell scripting. (2 Replies)
Discussion started by: er_ashu
2 Replies

8. Shell Programming and Scripting

passing values from sql to shell script

Hi guyz, Posting a thread after a long time. I want to pass two variables to unix shell script from sql script. Note: I am calling sql script from unix script. sql script has 2 variables one is the return code for status of program run and second one email flag. I don't know how to capture... (3 Replies)
Discussion started by: sachin.gangadha
3 Replies

9. Shell Programming and Scripting

Passing argumnets from shell script to sql

hi I all , I have sql statment in my shell script , I pass two argument to the script I need to pass the this two arguments to the sql statment example : runsql.sh "1" "2" sql : updat tables_x set y=0 where A=:x should subsituted by "1" and B=:y shuold subsituted bt "2"... (1 Reply)
Discussion started by: habuzahra
1 Replies

10. Shell Programming and Scripting

passing parameter from Shell-script to Sql-script

Dear Friends, Please help me to achieve the following: I want to pass one parameter from Shell-script to Sql-script. Example: My ShellScript.sh is calling report.sql like this: /bin/sqlplus /reports.sql And My report.sql is calling many Stored-Procedures like this: exec... (0 Replies)
Discussion started by: subodhbansal
0 Replies
Login or Register to Ask a Question