子程序中,包括存儲過程是不允許select語句單獨執行的,要加上into子句,如 select count(1) into v_count from zzs@mes; 其中v_count是聲明的一個number變量
回复2楼:存储过程里肯定写了 select count(1) into v_count from zzs@mes
要么是防火墙的问题或者是数据库链接的问题 试试tnsping 远程的实例名通否
SQL> create or replace procedure ttt_1 2 as 3 v_num number(10); 4 begin 5 select count(*) into v_num from tds.info_org@nsj820 ; 6 dbms_output.put_line(v_num); 7 end; 8 /
Procedure created
SQL> set serveroutput on SQL> exec ttt_1;
5
PL/SQL procedure successfully completed
SQL>
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED ------------------------------ ------------------------------ ------------ ------------ ---------- SCOTT AQ_ADMINISTRATOR_ROLE NO YES NO SCOTT CONNECT NO YES NO SCOTT DBA NO YES NO SCOTT EXP_FULL_DATABASE NO YES NO SCOTT IMP_FULL_DATABASE NO YES NO SCOTT MGMT_USER NO YES NO SCOTT RESOURCE NO YES NO
select count(1) into v_count from zzs@mes;
其中v_count是聲明的一個number變量
2 as
3 v_num number(10);
4 begin
5 select count(*) into v_num from tds.info_org@nsj820 ;
6 dbms_output.put_line(v_num);
7 end;
8 /
Procedure created
SQL> set serveroutput on
SQL> exec ttt_1;
5
PL/SQL procedure successfully completed
SQL>
Connected as scott
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
------------------------------ ------------------------------ ------------ ------------ ----------
SCOTT AQ_ADMINISTRATOR_ROLE NO YES NO
SCOTT CONNECT NO YES NO
SCOTT DBA NO YES NO
SCOTT EXP_FULL_DATABASE NO YES NO
SCOTT IMP_FULL_DATABASE NO YES NO
SCOTT MGMT_USER NO YES NO
SCOTT RESOURCE NO YES NO
7 rows selected
SQL>