Hi, The query works fine as a stand alone sql statment; SELECT 'TABLESPACE -> [' || tablespace_name || '] ALLOCATED_MB ->[' || SUM(ALLOCATED_MB) || '] FREE_MB -> [' || SUM(FREE_MB) || ']FREE_% -> [' || agent_score_card.asc_correct_format(SUM(FREE_MB)/SUM (ALLOCATED_MB),'PERCENT') || ']' "RESULT"FROM (SELECT a.tablespace_name, a.file_name, (a.bytes / 1048576 ) "ALLOCATED_MB", (b.free_bytes/ 1048576 ) "FREE_MB" FROM dba_data_files a , (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space GROUP BY file_id) b WHERE a.file_id=b.file_id AND (a.tablespace_name='CC_DATA') ORDER BY a.tablespace_name) GROUP BY tablespace_name
But when i try to create a function to return the value i get acompilation error of table or view does not exist (wheredba_free_space is the highlighted part) the function code is; create or replace FUNCTION MIDEVF_FREE_TABLESPACE RETURN VARCHAR2 AS RES VARCHAR2(200); BEGIN SELECT 'TABLESPACE -> [' || tablespace_name || '] ALLOCATED_MB ->[' || SUM(ALLOCATED_MB) || '] FREE_MB -> [' || SUM(FREE_MB) || ']FREE_% -> [' || agent_score_card.asc_correct_format(SUM(FREE_MB)/SUM (ALLOCATED_MB),'PERCENT') || ']' "RESULT" INTO RES FROM (SELECT a.tablespace_name, a.file_name, (a.bytes / 1048576 ) "ALLOCATED_MB", (b.free_bytes/ 1048576 ) "FREE_MB" FROM dba_data_files a , (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space GROUP BY file_id) b WHERE a.file_id=b.file_idAND (a.tablespace_name='CC_DATA') ORDER BY a.tablespace_name) GROUP BY tablespace_name; RETURN RES; END; any ideas why it's say the table doesn't exist?? Cheers --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
