Lanjutan dari posting saya sebelumnya, yang membuat anda tidak bisa mengakses table user lain dalam PL/SQL kemungkinan anda melakukan grant tidak secara langsung (grant select daftaremail to afri) tapi pakai role.
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > create user dedy identified by dedy 2 default tablespace users 3 quota unlimited on users; User created. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > create user afri identified by afri 2 default tablespace users 3 quota unlimited on users; User created. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > grant create session, create table to dedy; Grant succeeded. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > grant create session, create procedure to afri; Grant succeeded. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > conn dedy/dedy; Connected. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > create table daftaremail as 2 select object_name email from all_objects 3 where rownum <= 5; Table created. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > conn system Enter password: ****** Connected. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > create role viewdata; Role created. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > grant select on dedy.daftaremail to viewdata; Grant succeeded. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > grant viewdata to afri; Grant succeeded. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > conn afri/afri; Connected. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > select * from dedy.daftaremail; EMAIL ------------------------------ DUAL DUAL SYSTEM_PRIVILEGE_MAP SYSTEM_PRIVILEGE_MAP TABLE_PRIVILEGE_MAP 5 rows selected. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > create or replace procedure count_email 2 is 3 l_count int; 4 begin 5 select count(*) into l_count 6 from dedy.daftaremail; 7 dbms_output.put_line(l_count); 8 end; 9 / Warning: Procedure created with compilation errors. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > show err; Errors for PROCEDURE COUNT_EMAIL: LINE/COL ERROR -------- ----------------------------------------------------------------- 5/3 PL/SQL: SQL Statement ignored 6/13 PL/SQL: ORA-00942: table or view does not exist --- In [email protected], rudi kristanto <[EMAIL PROTECTED]> wrote: > > Kalau kedua user dalam satu database, contohnya > seperti ini: > > <code> > > [EMAIL PROTECTED] create user dedy identified by dedy > 2 default tablespace users > 3 quota unlimited on users; > > User created. > > [EMAIL PROTECTED] create user afri identified by afri > 2 default tablespace users > 3 quota unlimited on users; > > User created. > > [EMAIL PROTECTED] grant create session, create table to > dedy; > > Grant succeeded. > > [EMAIL PROTECTED] grant create session, create procedure to > afri; > > Grant succeeded. > > [EMAIL PROTECTED] conn dedy/dedy; > Connected. > > [EMAIL PROTECTED] create table daftaremail as > 2 select object_name email from all_objects > 3 where rownum <= 5; > > Table created. > > [EMAIL PROTECTED] grant select on daftaremail to afri; > > Grant succeeded. > > [EMAIL PROTECTED] conn afri/afri; > Connected. > > [EMAIL PROTECTED] select * from dedy.daftaremail; > > EMAIL > ------------------------------ > DUAL > DUAL > SYSTEM_PRIVILEGE_MAP > SYSTEM_PRIVILEGE_MAP > TABLE_PRIVILEGE_MAP > > 5 rows selected. > > [EMAIL PROTECTED] create or replace procedure count_email > 2 is > 3 l_count int; > 4 begin > 5 select count(*) into l_count > 6 from dedy.daftaremail; > 7 > 8 dbms_output.put_line(l_count); > 9 end; > 10 / > > Procedure created. > > [EMAIL PROTECTED] set serveroutput on; > [EMAIL PROTECTED] exec count_email; > 5 > > PL/SQL procedure successfully completed. > > </code> > --- dedy afriyanto [EMAIL PROTECTED] wrote: > > > ada yang tahu caranya bikin procedure? > > dengan melink table user lain.. > > > > contohnya begini ini hanya permisalan lhoo.. > > ada > > user > > DEDY dan AFRI > > > > DEDY punya tabel : daftaremail, daftartoko > > AFRI punya tabel : daftartokoemail > > > > si AFRI membuat PROCEDURE akses_moving() > > tapi dia butuh tabel daftaremail.. > > > > padahal udah di DEDY.daftaremail > > tapi gak mau kalau diluar prosedure mau. > > > > Thanks > > > > > > [Non-text portions of this message have been > > removed] > > > > > > > Send instant messages to your online friends http://uk.messenger.yahoo.com > [Non-text portions of this message have been removed]
