在 Unix Korn Shell 脚本中从 Oracle SP Out Param SYS_REFCURSOR 获取数据 [英] Fetch data from Oracle SP Out Param SYS_REFCURSOR in Unix Korn Shell Script

问题描述

我在 oracle 中有一个存储过程，参数为 sys ref_cursor.我需要从 korn shell 执行 sp.阅读输出并将其用作电子邮件正文.但是我找不到任何示例代码来读取 unix 中的 sys ref 游标.下面是我的代码.另外我如何阅读 cur ?print cur 似乎没有打印任何输出.

I have a stored procedure in oracle with out parameter as sys ref_cursor. I need to execute the sp from korn shell . read the output and use it as a email body. But i could not find any example code to read sys ref cursor in unix. below is my code. Also how can i read cur ? print cur doesnt seem to print any output.

#!/usr/bin/ksh

function runproc
{
    #read ref cursor from proc
    cur=`sqlplus -s $connectiondetails <<EOF
        SET PAGESIZE 0 FEEDBACK ON VERIFY OFF HEADING OFF ECHO OFF
        var return_val ;
        exec myproc_retcur(WEEKNUM ,return_val);    
        EXIT;
        EOF`
        print return_val
    return cur
}

#---Start-----
runproc cur
sendmail "Weekly load test results", cur

推荐答案

你的 print return_val 放错了地方；它应该在 SQL*PLUS 命令中，在退出之前，打印出引用游标变量.

You have your print return_val in the wrong place; it shoudl be inside the SQL*PLUS command, before the exit, to print out the ref cursor variable.

您还需要在过程调用中为 return_val 加上冒号前缀，以表明它正在使用您刚刚声明的绑定变量 - 尽管您也从其声明中省略了变量类型.这似乎可以满足您的需求:

You also need to prefix return_val with a colon in your procedure call, to indicate it's using the bind variable you just declared - though you omitted the variable type from its declaration as well. This seems to do what you want:

function runproc
{
    #read ref cursor from proc
    cur=`sqlplus -s $connectiondetails <<EOF
        SET PAGESIZE 0 FEEDBACK ON VERIFY OFF HEADING OFF ECHO OFF
        var return_val refcursor
        exec myproc_retcur(14, :return_val);
        print return_val
        EXIT
        EOF`
    return cur
}

您尚未显示 WEEKNUM 的来源，因此我暂时将其硬编码为一个数字.

You haven't shown where WEEKNUM is coming from so I've hard-coded that to a number for now.

我认为您可能希望关闭而不是偶然地打开反馈.

I think you probably want to set feedback off, not on, incidentally.

这篇关于在 Unix Korn Shell 脚本中从 Oracle SP Out Param SYS_REFCURSOR 获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！