Home » SQL & PL/SQL » SQL & PL/SQL » malformed cursor (oracle 12c)
malformed cursor [message #667708] Thu, 11 January 2018 08:44 Go to next message
desmond30
Messages: 33
Registered: November 2009
Member
I am stuck at a unique situation, In a package, I have a cursor , and i am using a cursor, and then i have a function that returns pipeline based on the cursor.

Now the problem is, within the cursor, i am using the function and so I get malformed cursor error, i cannot declare the function as the 1st one in the package, pls see structure below


CREATE OR REPLACE PACKAGE MYPKG AS


CURSOR CUR(PDATE DATE DEFAULT NULL) IS
 WITH CL1 as
    
    (
      select .....
        from table (MYPKG.F_MYFUN(TO_DATE('09-JAN-2018','DD-MON-YYYY')) ) -- USING THE FUNCTION HERE 
    
      );
           
     TYPE CUR_type IS TABLE OF CUR%ROWTYPE;      
           
           FUNCTION F_MYFUN
        (PDATE DATE DEFAULT NULL)
    RETURN CUR_type PIPELINED;

END MYPKG;


SO here is the problem, i cannot decalre the function ahead of the cursor because of this
  TYPE CUR_type IS TABLE OF CUR%ROWTYPE;     
           

how do i resolve this ? this feels like a recursive infinite loop :9
Re: malformed cursor [message #667709 is a reply to message #667708] Thu, 11 January 2018 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you've got a cursor
And a type based on the cursor
And a function that returns the type.
And that function is used in the cursor

So the cursor is referencing itself.

That's infinite recursion yes.
You need to stop doing that.

Why are you trying to use this setup?
How does it make any sense?
Why isn't the function returning a type that isn't based on the cursor?
Re: malformed cursor [message #667710 is a reply to message #667709] Thu, 11 January 2018 08:55 Go to previous messageGo to next message
desmond30
Messages: 33
Registered: November 2009
Member
Why isn't the function returning a type that isn't based on the cursor?

The function is in fact returning the type.

I am using this set up because I need the results the function results for another date apart from the parameter date...so instead of maintaining another object, i thought to resolve this in sql and plsql
Re: malformed cursor [message #667711 is a reply to message #667709] Thu, 11 January 2018 08:55 Go to previous messageGo to next message
BlackSwan
Messages: 26708
Registered: January 2009
Location: SoCal
Senior Member
What business problem are you trying to solve?
Re: malformed cursor [message #667712 is a reply to message #667710] Thu, 11 January 2018 09:00 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can see what you're doing.
It very obviously can't work.
Why do you think you need to?
What actual problem are you trying to solve?
Re: malformed cursor [message #667713 is a reply to message #667712] Thu, 11 January 2018 09:02 Go to previous messageGo to next message
desmond30
Messages: 33
Registered: November 2009
Member
So the pipeline function returns a set of data given a date (date parameter)

I have to compare 2 sets of data, the given date parameter, and get the pipeline results for another date (hence I wrote a with clause, and called the pipeline function with the 2nd date)
Re: malformed cursor [message #667714 is a reply to message #667713] Thu, 11 January 2018 09:05 Go to previous messageGo to next message
desmond30
Messages: 33
Registered: November 2009
Member
I mean i can go for a table and store the results of date2, and use that table etc, but just trying not to maintain another object if possible
Re: malformed cursor [message #667715 is a reply to message #667713] Thu, 11 January 2018 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26708
Registered: January 2009
Location: SoCal
Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: malformed cursor [message #667716 is a reply to message #667715] Thu, 11 January 2018 09:10 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't do a circular reference like that, no ifs, no buts, it's not possible.

We could probably help you come up with a working solution to whatever the business problem is, but you would have to describe the business problem.
Previous Topic: PL/SQL procedure issue
Next Topic: duplicate records with different date_from and date_to
Goto Forum:
  


Current Time: Tue Jun 02 13:06:30 CDT 2020