On Nov 14, 3:51 am, joxo <[EMAIL PROTECTED]> wrote: > 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
A. You must grant select on the view dba_data_files to the owner of the function MIDEVF_FREE_TABLESPACE. B. If your select statement returns more than one row, you cannot assign it to the variable res. Look up PIPELINED functions: Start with : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345 HTH Thomas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
