Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: PLsql cursor not opening when called in script

    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

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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`

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by kitaman View Post
    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 View Post
    ... 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".
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    Yes you can extend the back tick over several lines.
    I ain't never tried that in 25 years, cuz its bad form.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    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
    ==>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by kitaman View Post
    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...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    Provided Answers: 1
    I wasn't trying to be smart, I had seen that technique used in several other threads, and wondered why.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by kitaman View Post
    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.
    Last edited by LKBrwn_DBA; 01-04-12 at 11:27.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •