PLsql cursor not opening when called in script

Dharv · #1 (**permalink**) 12-30-11, 09:10

Hi All,

I have a part of script as below

temp=`sqlplus -s $1 << EOF
set feedback off verify off heading off pagesize 0 linesize 100
SELECT xx_pkg.xx_fuc
FROM dual;
exit;
EOF`

My cursor will update a table based on cursor fetched records, and return no of rows updated.
Now my function is getting called and all operation are done except the cursor for loop. When i executed this (select xx_pkg....) in sql promt it is working as expected. My package is in abc schema and i am connecting to the same scema in unix.
Please help. Thanks in advance

kitaman · #2 (**permalink**) 01-01-12, 09:18

I am not sure that you can extend the back tick operator over multiple lines.
Try this way, by putting the sql statements into a file.

Code:

#select.sql
set feedback off verify off heading off pagesize 0 linesize 100
SELECT xx_pkg.xx_fuc
FROM dual;
exit;

Then use

Code:

temp=`sqlplus $1 < select.sql`

LKBrwn_DBA · #3 (**permalink**) 01-03-12, 14:07

Quote:

Originally Posted by kitaman

I am not sure that you can extend the back tick operator over multiple lines.

Yes you can extend the back tick over several lines.

Quote:

Originally Posted by Dharv

... Etc ...
Now my function is getting called and all operation are done except the cursor for loop.
... Etc ...
Please help. Thanks in advance

We cannot help on what we cannot see! We don't know what code are you refering to as "cursor for loop".

kitaman · #4 (**permalink**) 01-03-12, 14:47

Quote:

Yes you can extend the back tick over several lines.

I ain't never tried that in 25 years, cuz its bad form.

LKBrwn_DBA · #5 (**permalink**) 01-03-12, 15:45

Check it out:

Code:

==> cat m0
ORACLE_SID=orcl
ORAENV_ASK=NO
. /usr/local/bin/oraenv
temp=`sqlplus -s / << EOF
set feedback off verify off heading off pagesize 0 linesize 100
SELECT 'Calling xx_pkg.xx_fuc at '||systimestamp
FROM dual;
exit;
EOF`

echo $temp

==> ./m0
Calling xx_pkg.xx_fuc at 03-JAN-12 03.44.28.327677 PM -05:00
==>

kitaman · #6 (**permalink**) 01-03-12, 16:40

Why do you need the backtick if the only use of the temp variable is to echo it?
m0 could be simplified to

Code:

ORACLE_SID=orcl
ORAENV_ASK=NO
. /usr/local/bin/oraenv
sqlplus -s / << EOF
set feedback off verify off heading off pagesize 0 linesize 100
SELECT 'Calling xx_pkg.xx_fuc at '||systimestamp
FROM dual;
exit;
EOF

or

Code:

ORACLE_SID=orcl
ORAENV_ASK=NO
. /usr/local/bin/oraenv
temp=`sqlplus -s / << EOF >/tmp/output
set feedback off verify off heading off pagesize 0 linesize 100
SELECT 'Calling xx_pkg.xx_fuc at '||systimestamp
FROM dual;
exit;
EOF

cat /tmp/output

If you need to save the output or separate it from other processes within the same procedure.

LKBrwn_DBA · #7 (**permalink**) 01-03-12, 16:49

Quote:

Originally Posted by kitaman

Why do you need the backtick if the only use of the temp variable is to echo it?

I posted an EXAMPLE script for the OP to demonstrate that backtick's can span several lines...

kitaman · #8 (**permalink**) 01-03-12, 16:59

I wasn't trying to be smart, I had seen that technique used in several other threads, and wondered why.

LKBrwn_DBA · #9 (**permalink**) 01-04-12, 10:24

Quote:

Originally Posted by kitaman

I wasn't trying to be smart, I had seen that technique used in several other threads, and wondered why.

Mainly because of the "<<EOF" thing, it allows sql commands to start at beginning of the line -- and -- you get the results in a variable that can be more easily analyzed.

Internet Services

Web Development

Digital Marketing

Consumer Tech