| Compilation error in Function [message #645437] |
Mon, 07 December 2015 00:11  |
|
|
Hi
Following code showing compilation error:
CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2)
RETURN NUMBER
AS
l_return NUMBER;
BEGIN
SELECT ms.value
INTO l_return
FROM v$mystat ms,
v$statname sn
WHERE ms.statistic# = sn.statistic#
AND sn.name = p_stat;
RETURN l_return;
END get_stat;
/
The error is :
Error(6,3): PL/SQL: SQL Statement ignored
Error(9,10): PL/SQL: ORA-00942: table or view does not exist
But if I execute only the SQL as below, it shows the output
SELECT ms.value
FROM v$mystat ms,
v$statname sn
WHERE ms.statistic# = sn.statistic#
AND sn.name = 'session pga memory';
How to resolve it?
Regards
|
|
|
|
|
|
| Re: Compilation error in Function [message #645441 is a reply to message #645440] |
Mon, 07 December 2015 02:02  |
|
|
Hi Michel Cadot
I changed the function definition as below. Still it shows same error.
CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2)
RETURN NUMBER
AUTHID current_user
AS
l_return NUMBER;
BEGIN
SELECT ms.value
INTO l_return
FROM v$mystat ms,
v$statname sn
WHERE ms.statistic# = sn.statistic#
AND sn.name = p_stat;
RETURN l_return;
END get_stat;
/
|
|
|
|
|
|
| Re: Compilation error in Function [message #645443 is a reply to message #645442] |
Mon, 07 December 2015 02:20 |
|
|
Below is the Spool file:
SQL> CREATE OR REPLACE FUNCTION get_stat (p_stat IN VARCHAR2)
2 RETURN NUMBER
3 AUTHID current_user
4 AS
5
6 l_return NUMBER;
7
8 BEGIN
9 SELECT ms.value
10 INTO l_return
11 FROM v$mystat ms,
12 v$statname sn
13 WHERE ms.statistic# = sn.statistic#
14 AND sn.name = p_stat;
15
16 RETURN l_return;
17
18 END get_stat;
19 /
Warning: Function created with compilation errors.
SQL> show err
Errors for FUNCTION GET_STAT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/3 PL/SQL: SQL Statement ignored
12/10 PL/SQL: ORA-00942: table or view does not exist
SQL> spool off
|
|
|
|
|
|
| Re: Compilation error in Function [message #645448 is a reply to message #645446] |
Mon, 07 December 2015 03:32 |
|
|
Below is the spool file of above all commands:
SQL> show user
USER is "SCOTT"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE USER
PRIVILEGE
----------------------------------------
BECOME USER
ALTER USER
DROP USER
CREATE ROLLBACK SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT
CREATE TABLE
CREATE ANY TABLE
ALTER ANY TABLE
BACKUP ANY TABLE
DROP ANY TABLE
PRIVILEGE
----------------------------------------
LOCK ANY TABLE
COMMENT ANY TABLE
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
CREATE CLUSTER
CREATE ANY CLUSTER
ALTER ANY CLUSTER
DROP ANY CLUSTER
CREATE ANY INDEX
PRIVILEGE
----------------------------------------
ALTER ANY INDEX
DROP ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE VIEW
CREATE ANY VIEW
DROP ANY VIEW
CREATE SEQUENCE
PRIVILEGE
----------------------------------------
CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP PUBLIC DATABASE LINK
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
PRIVILEGE
----------------------------------------
AUDIT ANY
ALTER DATABASE
FORCE TRANSACTION
FORCE ANY TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER ANY PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE ANY TRIGGER
PRIVILEGE
----------------------------------------
ALTER ANY TRIGGER
DROP ANY TRIGGER
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
ALTER RESOURCE COST
ANALYZE ANY
GRANT ANY PRIVILEGE
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
PRIVILEGE
----------------------------------------
DROP ANY MATERIALIZED VIEW
CREATE ANY DIRECTORY
DROP ANY DIRECTORY
CREATE TYPE
CREATE ANY TYPE
ALTER ANY TYPE
DROP ANY TYPE
EXECUTE ANY TYPE
UNDER ANY TYPE
CREATE LIBRARY
CREATE ANY LIBRARY
PRIVILEGE
----------------------------------------
ALTER ANY LIBRARY
DROP ANY LIBRARY
EXECUTE ANY LIBRARY
CREATE OPERATOR
CREATE ANY OPERATOR
ALTER ANY OPERATOR
DROP ANY OPERATOR
EXECUTE ANY OPERATOR
CREATE INDEXTYPE
CREATE ANY INDEXTYPE
ALTER ANY INDEXTYPE
PRIVILEGE
----------------------------------------
DROP ANY INDEXTYPE
UNDER ANY VIEW
QUERY REWRITE
GLOBAL QUERY REWRITE
EXECUTE ANY INDEXTYPE
UNDER ANY TABLE
CREATE DIMENSION
CREATE ANY DIMENSION
ALTER ANY DIMENSION
DROP ANY DIMENSION
MANAGE ANY QUEUE
PRIVILEGE
----------------------------------------
ENQUEUE ANY QUEUE
DEQUEUE ANY QUEUE
CREATE ANY CONTEXT
DROP ANY CONTEXT
CREATE ANY OUTLINE
ALTER ANY OUTLINE
DROP ANY OUTLINE
ADMINISTER RESOURCE MANAGER
ADMINISTER DATABASE TRIGGER
MERGE ANY VIEW
ON COMMIT REFRESH
PRIVILEGE
----------------------------------------
RESUMABLE
SELECT ANY DICTIONARY
DEBUG CONNECT SESSION
DEBUG ANY PROCEDURE
FLASHBACK ANY TABLE
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
ALTER ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
EXECUTE ANY EVALUATION CONTEXT
PRIVILEGE
----------------------------------------
CREATE RULE SET
CREATE ANY RULE SET
ALTER ANY RULE SET
DROP ANY RULE SET
EXECUTE ANY RULE SET
EXPORT FULL DATABASE
IMPORT FULL DATABASE
CREATE RULE
CREATE ANY RULE
ALTER ANY RULE
DROP ANY RULE
PRIVILEGE
----------------------------------------
EXECUTE ANY RULE
ANALYZE ANY DICTIONARY
ADVISOR
CREATE JOB
CREATE ANY JOB
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
MANAGE SCHEDULER
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
ALTER ANY SQL PROFILE
PRIVILEGE
----------------------------------------
ADMINISTER SQL TUNING SET
ADMINISTER ANY SQL TUNING SET
CREATE ANY SQL PROFILE
MANAGE FILE GROUP
MANAGE ANY FILE GROUP
READ ANY FILE GROUP
CHANGE NOTIFICATION
CREATE EXTERNAL JOB
CREATE ANY EDITION
DROP ANY EDITION
ALTER ANY EDITION
PRIVILEGE
----------------------------------------
CREATE ASSEMBLY
CREATE ANY ASSEMBLY
ALTER ANY ASSEMBLY
DROP ANY ASSEMBLY
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
CREATE MINING MODEL
CREATE ANY MINING MODEL
DROP ANY MINING MODEL
SELECT ANY MINING MODEL
ALTER ANY MINING MODEL
PRIVILEGE
----------------------------------------
COMMENT ANY MINING MODEL
CREATE CUBE DIMENSION
ALTER ANY CUBE DIMENSION
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
DROP ANY CUBE DIMENSION
INSERT ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
CREATE CUBE
ALTER ANY CUBE
CREATE ANY CUBE
PRIVILEGE
----------------------------------------
DROP ANY CUBE
SELECT ANY CUBE
UPDATE ANY CUBE
CREATE MEASURE FOLDER
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
DROP ANY MEASURE FOLDER
INSERT ANY MEASURE FOLDER
CREATE CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DROP ANY CUBE BUILD PROCESS
PRIVILEGE
----------------------------------------
UPDATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
ADMINISTER SQL MANAGEMENT OBJECT
FLASHBACK ARCHIVE ADMINISTER
202 rows selected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
ROLE
------------------------------
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
PLUSTRACE
XDBADMIN
XDB_SET_INVOKER
17 rows selected.
SQL> select ms.value
2 from v$mystat ms,
3 v$statname sn
4 where ms.statistic# = sn.statistic#
5 and sn.name = 'session pga memory';
VALUE
----------
2461624
SQL> spool off
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
