Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00653: aggregate/table functions are not allowed in PL/SQL scope (merged) (oracle sql developer 2.1)
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope (merged) [message #464569] Thu, 08 July 2010 07:30 Go to next message
titusm
Messages: 1
Registered: July 2010
Junior Member
Hello,

I have a function that returns the situation for one month for some database.
I need to implement it in some report medium for one year.
The one year function works ok.

My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".

I am trying to get around some restrictions and somehow until this part things seem to be ok.

I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.

Is there any work around?

thank you

here is the code (there might be some name misuse since i had to change the names of the original code - i hope i didn't make any mistakes):


CREATE OR REPLACE
FUNCTION anual(monthh in varchar2, year IN VARCHAR2)
return anual_REP_var PIPELINED is

BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL);
contor INT(2); 
an_in VARCHAR2(4);

Begin
contor  := to_number(trim(monthh));  --the number of iterations 
year_in   := trim(year); --
For tmp in 1..contor
loop
  br:=anual_monthh('28-'||tmp||'-'||year_in); --function that returns the situation for one month
	--(must run it 12 times with different parameter) - i need to get cumulated situation
	--error trown  
	--PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
        --usually i use "table(anual_monthh(parameter))" -it is not recognised in this case 
  pipe ROW(BR); --not sure if it is the case 
END loop;
return;
END anual;


  • Attachment: err.sql
    (Size: 0.77KB, Downloaded 1044 times)
Re: PLS-00653: aggregate/table functions are not allowed in PL/SQL scope (merged) [message #464580 is a reply to message #464569] Thu, 08 July 2010 07:54 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What is anual_monthh?
Re: PLS-00653: aggregate/table functions are not allowed in PL/SQL scope (merged) [message #464585 is a reply to message #464569] Thu, 08 July 2010 08:00 Go to previous message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: all create and insert statements that allow us to reproduce what you have.

Use SQL*Plus and copy and paste your session.

Also always post your Oracle version with 4 decimals.

Regards
Michel

Previous Topic: random number generate (merged 3)
Next Topic: Image in XML and used in PL SQL
Goto Forum:
  


Current Time: Sat Aug 09 04:01:14 CDT 2025