求助:光标中如何使用同义词

gz_xuhaoz

原帖由 numenhuang 于 2007-9-10 11:27 发表
xxxx,yyyy前分别带上schema名称，再测试

  加上了也不行。

numenhuang

create or replace procedure test
as
cursor cc is select * from YYYY;
begin
null;
end;
/

能否创建

gz_xuhaoz

原帖由 numenhuang 于 2007-9-10 15:11 发表
create or replace procedure test
as
cursor cc is select * from YYYY;
begin
null;
end;
/

能否创建

不能创建

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/14     PL/SQL: SQL Statement ignored
3/28     PL/SQL: ORA-00942: table or view does not exist



SQL> select count(*) from YYYY;

  COUNT(*)
----------
     10423

numenhuang

sqlplus system
sql>select * from dba_synonyms where synonym_name='YYYY'
贴出来看看

create or replace procedure test
as
cursor cc is select * from 同义词基表用户.同义词基表;
begin
null;
end;

gz_xuhaoz

原帖由 numenhuang 于 2007-9-10 15:51 发表
sqlplus system
sql>select * from dba_synonyms where synonym_name='YYYY'
贴出来看看

create or replace procedure test
as
cursor cc is select * from 同义词基表用户.同义词基表;
begin
null;
end;

SQL> SHOW USER
USER is "FSZW_LSGJ"

select * from dba_synonyms where synonym_name='IM_PR_NOSECT'

OWNER                          TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
FSZW_LSGJ                      COMMON_LSGJ
IM_PR_NOSECT


SQL>
SQL> create or replace procedure test
  2  as
  3  cursor cc is select * from COMMON_LSGJ.IM_PR_NOSECT;
  4  begin
  5  null;
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/14     PL/SQL: SQL Statement ignored
3/40     PL/SQL: ORA-00942: table or view does not exist

numenhuang

SQL> SHOW USER
USER is "FSZW_LSGJ"

sql>select * from COMMON_LSGJ.IM_PR_NOSECT where rownum<10;

如果报错
connect system
grant select on COMMON_LSGJ.IM_PR_NOSECT to FSZW_LSGJ;

gz_xuhaoz

SQL> select count(*) from  COMMON_LSGJ.IM_PR_NOSECT;

  COUNT(*)
----------
     10423

SQL>

numenhuang

sql>select * from COMMON_LSGJ.IM_PR_NOSECT where rownum<10;

gz_xuhaoz

可以的，没有问题。

numenhuang

原帖由 gz_xuhaoz 于 2007-9-10 16:13 发表
可以的，没有问题。

如果这样的话
SQL> create or replace procedure test
  2  as
  3  cursor cc is select * from COMMON_LSGJ.IM_PR_NOSECT;
  4  begin
  5  null;
  6  end;
  7  /
还会报错？