求助:光標(biāo)中如何使用同義詞

gz_xuhaoz

原帖由 numenhuang 于 2007-9-10 11:27 發(fā)表
xxxx,yyyy前分別帶上schema名稱，再測(cè)試

  加上了也不行。

numenhuang

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

能否創(chuàng)建

gz_xuhaoz

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

能否創(chuàng)建

不能創(chuàng)建

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 發(fā)表
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;

如果報(bào)錯(cuò)
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 發(fā)表
可以的，沒有問題。

如果這樣的話
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  /
還會(huì)報(bào)錯(cuò)？