SHELL接受存储过程返回的值

有网友问到SHELL如何接受存储过程返回的值，做了一个简单的小例子：

 

[oracle@rac1 ~]$ sqlplus htyansp/htyansp

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 19 13:05:19 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE PROCEDURE GETDATE(p_date OUT VARCHAR2)
  2 IS
  3 BEGIN
  4 p_date:=TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS');
  5 END;
  6 /

Procedure created.

SQL> VARIABLE str VARCHAR2(20);
SQL> exec GETDATE(:str);

PL/SQL procedure successfully completed.

SQL> select :str from dual;

:STR
--------------------------------

2012-08-19 13:08:31

首先创建了一个存储过程，返回当前的日期，可以看到存储过程已经正常工作。

编写的SHELL脚本如下：

 

[oracle@rac1 ~]$ cat getdate.sh
#!/bin/bash

function getdate(){
$ORACLE_HOME/bin/sqlplus -s htyansp/htyansp <<EOF
set term off
set feedback off
set head off
VARIABLE str VARCHAR2(20);
exec GETDATE(:str);
select 'date|'||:str from dual;
exit;
EOF
}

mydate=`getdate|grep "date"| awk -F "|" '{print $2}'`
echo $mydate

[oracle@rac1 ~]$ chmod u+x getdate.sh
[oracle@rac1 ~]$ ./getdate.sh
2012-08-19 13:23:14

如果问题复杂了，用SHELL出来起来麻烦，用C来处理或许更好。

 

[oracle@rac1 ~]$ cat getdate.pc
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <stdlib.h>
exec sql include sqlca;

void sqlerror()
  {
    exec sql whenever sqlerror continue;
    printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
    exec sql rollback work release;
    exit(1);
  }

int main(int argc,char *argv)
{
  exec sql begin declare section;
    char str[20];
    char * connstr="yansp/yansp";
  exec sql end declare section;

  exec sql whenever sqlerror do sqlerror();
  exec sql connect :connstr;
  exec sql call getdate(:str);
  printf("The date is :%s\n",str);
  exec sql commit work release;
  exit(0);
}

[oracle@rac1 ~]$ proc parse=none getdate.pc

Pro*C/C++: Release 11.2.0.1.0 - Production on Sun Aug 19 14:14:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

System default option values taken from: /home/oracle/app/product/11.2.0/db_1/precomp/admin/pcscfg.cfg

[oracle@rac1 ~]$ gcc getdate.c -o getdate $ORACLE_HOME/lib/libclntsh.so
[oracle@rac1 ~]$ ./getdate
The date is :2012-08-19 14:14:34