SQL> create or replace procedure coba
2 is
3 owner varchar2(30);
4 begin
5 select object_owner
6 into owner
7 from v$sql_plan
8 where object_owner = 'HAKIM';
9 if SQL%NOTFOUND then
10 DBMS_OUTPUT.PUT_LINE('coba lagi');
11 else
12 DBMS_OUTPUT.PUT_LINE(owner);
13 end if;
14 end;
15 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE COBA:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
7/6 PL/SQL: ORA-00942: table or view does not exist
　
SQL> declare
2 owner varchar2(30);
3 begin
4 select object_owner
5 into owner
6 from v$sql_plan
7 where object_owner = 'HAKIM';
8 if SQL%NOTFOUND then
9 DBMS_OUTPUT.PUT_LINE('coba lagi');
10 else
11 DBMS_OUTPUT.PUT_LINE(owner);
12 end if;
13 end;
14 /
HAKIM
PL/SQL procedure successfully completed.
SQL> select owner, object_type from dba_objects where object_name='V$SQL_PLAN';
OWNER OBJECT_TYPE
------------------------------ -------------------
PUBLIC SYNONYM
saya mau nanya,,kenapa pada procedure tersebut error???
ketika procedure tersebut iseng-iseng saya ganti ke dalam bentuk blok PL/SQL,
ternyata perintah tersebut running...
waaaah makin bingung,,,apakah metadata tidak bisa running dalam procedure??
padahal V$SQL_PLAN bersifat PUBLIC,,,saya memberikan hak akses DBA kepada
HAKIM...
thanx all.
