往sql*plus传递参数时碰到的问题

zhyuh

我写的一段shell, 想从数据库获取某个存储过程名，然后又将该存储过程名作为参数传给另外一个存储过程作，
......
PROC_NAME=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
                SET TERM OFF
                SET HEAD OFF
                SET FEED OFF
                SELECT proc_nam FROM eden_refr_objt WHERE lower(obj_nam)=lower('$OBJECT_NAME');
EOF`
    echo $PROC_NAME
    sqlplus $USERNAME/$PASSWORD@$DATABASE<<EOF
      execute EDEN_REFRESH.REFRESH_TAB('$USERNAME', '$OBJECT_NAME', '.' ,'$PROC_NAME');
      exit;
EOF
......

根据echo $PROC_NAME返回结果看，变量PROC_NAME取到了正确的值，但是将该变量传给另外一个存储过程时，总是出现运行错误：
SQL> ERROR:
ORA-01756: quoted string not properly terminated


SQL> SP2-0734: unknown command beginning "EDEN_FLAT_..." - rest of line ignored.
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

如果将$PROC_NAME用一个真实的存储过程名代替，即
execute EDEN_REFRESH.REFRESH_TAB('$USERNAME', '$OBJECT_NAME', '.' ,'$PROC_NAME');
改为
execute EDEN_REFRESH.REFRESH_TAB('$USERNAME', '$OBJECT_NAME', '.' ,'eden_flat_vhc_live_rate_fr');

其中 eden_flat_vhc_live_rate_fr 为某个存储过程名称
则执行正确。

请各位高人帮我看看是什么原因，如何解决？

谢谢！

一梦如是

在创建PROC_NAME后，添加一句export  PROC_NAME试试看？

guangzongy

不了解oracle，说说自己的看法。
可能原因：
$PROC_NAME是shell中的变量，应该在shell中完成字符替换，但是在你的语句中用单引号引用致使shell无法变量替换。

zhyuh

我们也这么测试了，不过好像还是不对。

为了方便说明问题，我写了另外一个测试shell test1。

数据库钟的表和数据：
SQL> desc aa
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
COL1 VARCHAR2(10) Y                        

SQL> select * from aa;

COL1
----------
b
a

test的内容：
/export/home/deverdb/p482/scripts>cat test1
#!/usr/bin/bash

DB_CHAR=`sqlplus -s edentest/edentest@perdb<<EOF
        SET HEAD OFF
        SET TERM OFF
        SET FEED OFF
        SELECT col1 FROM aa WHERE rownum<2;
EOF`

echo $DB_CHAR
export DB_CHAR

sqlplus edentest/edentest@perdb<<EOF
  set serveroutput on
  execute dbms_output.put_line('before');
  execute dbms_output.put_line('$DB_CHAR');
  exit;
EOF

执行过程和错误：
/export/home/deverdb/p482/scripts>test1
b

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Dec 14 09:05:14 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> SQL> before

PL/SQL procedure successfully completed.

SQL> ERROR:
ORA-01756: quoted string not properly terminated


SQL> SP2-0042: unknown command "b')" - rest of line ignored.
SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Productio
n
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

请各位继续支招，谢谢！

zhyuh

原帖由 "guangzongy" 发表：
不了解oracle，说说自己的看法。
可能原因：
$PROC_NAME是shell中的变量，应该在shell中完成字符替换，但是在你的语句中用单引号引用致使shell无法变量替换。

$USERNAME $OBJECT_NAME 也是一样的调用方法，为什么这两个参数就可以呢？

其中USERNAME是一开始就赋值的 USERNAME=edentest， OBJECT_NAME是从数据库中取出来的
for OBJECT_NAME in `sqlplus -s $USERNAME/$PASSWORD@$DATABASE<<EOF
        SET FEED OFF
        SET HEAD OFF
        SET TERM OFF
        SELECT obj_nam FROM eden_refr_objt;
EOF`
do
...
done

一梦如是

[ $PROC_NAME='eden_flat_vhc_live_rate_fr' ]
测试一下！

zhyuh

从错误看变量是替换了的
SQL> SP2-0734: unknown command beginning "EDEN_FLAT_..." - rest of line ignored.


SQL> SP2-0042: unknown command "b')" - rest of line ignored.

guangzongy

将语句：
execute EDEN_REFRESH.REFRESH_TAB('$USERNAME', '$OBJECT_NAME', '.' ,'$PROC_NAME');
使用这种策略试试：
在使用sql语句之前先拼装好：
sqlStr="execute EDEN_REFRESH.REFRESH_TAB('"${USERNAME}"','"${OBJECT_NAME}"','.','"${PROC_NAME}"');"

在引用的地方：${sqlStr}完成。

zhyuh

原帖由 "一梦如是"]AME='eden_flat_vhc_live_rate_fr'  发表：

测试一下！

这样赋值一下是可以的
shell内容：
/export/home/deverdb/p482/scripts>cat test1
#!/usr/bin/bash

#DB_CHAR=`sqlplus -s edentest/edentest@perdb<<EOF
#       SET HEAD OFF
#       SET TERM OFF
#       SET FEED OFF
#       SELECT col1 FROM aa WHERE rownum<2;
#EOF`

DB_CHAR='eden_flat_vhc_live_rate_fr'
echo $DB_CHAR
export DB_CHAR

sqlplus edentest/edentest@perdb<<EOF
  set serveroutput on
  execute dbms_output.put_line('before');
  execute dbms_output.put_line('''$DB_CHAR''');
  exit;
EOF

执行：
/export/home/deverdb/p482/scripts>test1
eden_flat_vhc_live_rate_fr

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Dec 14 09:28:47 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SQL> SQL> before

PL/SQL procedure successfully completed.

SQL> 'eden_flat_vhc_live_rate_fr'

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

guangzongy

而且，你可以很容易通过输出${sqlStr}来判断你的sql语句中是否含有多余的字符，比如换行符之类的。