在perl脚本中使用输入参数和out光标调用存储过程 [英] invoking a stored procedure with input parameter and out cursor in perl script

问题描述

尝试在perl脚本中执行过程，过程->创建或替换

Trying to execute a procedure in perl script, Proceure ->create or replace

PROCEDURE Getproc
(
    v_catg IN CHAR DEFAULT NULL,
    v_cursor OUT SYS_REFCURSOR
)

1. 执行程序

1. to execute procedure

my $sth = $dbh->prepare(
    q{
        BEGIN
        Getproc(:category, :curs);
        END;
    }
);

绑定i / p和光标

to bind i/p and cursor

$sth->bind_param(":category", $category1);

$sth->bind_param_inout(":curs", \$cursrecords, 0, {ora_type => ORA_RSET});

$sth->execute;
$sth->finish; 

从光标获取记录

Fetch records from cursor

while ($hashRef = $cursrecords->fetchrow_hashref) {
    foreach (keys %$hashRef) {
        print "hashref:$hashRef and $_ is $hashRef->{$_}\n";
    }
}

第三步没有检索任何东西。 curserecords = DBI :: st = HASH（0x2371bd0）
具有哈希对象。有人可以帮我知道这里缺少什么吗？为什么我不能从存储在游标中的表中检索行？

3rd step is not retrieving anything. curserecords=DBI::st=HASH(0x2371bd0) this has hash object. Can some one help me know what is missing here. Why am I not able to retrieve rows from table stored in cursor?

过程以供参考

create or replace PROCEDURE GetProc
(
    v_catg IN CHAR DEFAULT NULL,
    v_cursor OUT SYS_REFCURSOR
)
AS
BEGIN
IF ( v_catgIS NULL ) THEN
    BEGIN
        OPEN  v_cursor FOR
        SELECT EnvVar,
        VALUE 
        FROM table;
    END;
ELSE
    BEGIN
        OPEN  v_cursor FOR
        SELECT EnvVar ,
        VALUE 
        FROM table
        WHERE  Category = v_catg ;
    END;
END IF;

EXCEPTION
    WHEN OTHERS THEN raise_application_error(-20002,SQLCODE||':'||SQLERRM);
END;

推荐答案

请尝试（如RenéNyffenegger的收藏集）

my $sth = $dbh->prepare(
  q{
    DECLARE
      curs sys_refcursor;
    BEGIN
      Getproc(:category, :curs);
    END;
  });

这篇关于在perl脚本中使用输入参数和out光标调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！