0

I'm trying to make a chart in a database dashboard reporting software(Logi Info). I've got a PL SQL package that returns a ref cursor with multiple values but it seems the Logi Info does not support this and gives me an error ORA-00904: "DASHBOARD_PACKAGE"."GETSUMMARYDATA": invalid identifier. I think its either not supported or that my querty is wrong. This is my query:

select dashboard_package.getSummaryData(1,sysdate) from dual

Is that how to call a function that returns multiple values? if so, is there a solution to this problem (return type not supported)?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
124697
  • 22,097
  • 68
  • 188
  • 315

2 Answers2

0

If you are using SQL*Plus, you need to use a special syntax in order to access REF CURSORS.

This is well explained in the SQL*Plus manual:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1127

So it would be something like this in your case:

VARIABLE cv REFCURSOR
EXECUTE dashboard_package.getSummaryData(1, sysdate, :cv)
print cv

Note that the position of the :cv variable depends on the definition of your procedure.
But as you did not show us the source code...

Edit
To cover all possibilies (as mentioned by APC): If the function indeed returns a ref cursor, then the syntax is slightly different as explained in the next chapter of the manual:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch5.htm#sthref1128

VARIABLE cv REFCURSOR
execute :cv := dashboard_package.getSummaryData(1, sysdate);
print cv
  • The OP says they have defined a function which returns a ref cursor, and their posted SQL call seems to substantiate this. SQL*Plus supports ref cursors in SELECT statements without the need for variables - at least in more recent versions. See my response to their other question: http://stackoverflow.com/questions/4614475/how-to-call-a-function-in-a-package/4615302#4615302 – APC Jan 06 '11 at 14:16
  • I certainly agree that your proposal is a highly speculative solution to an ORA-00904 error ;) – APC Jan 06 '11 at 17:33
0

This is a compilation error. Your GETSUMMARYDATA() function is referencing an invalid object name, a table, a column or whatever. If you're using dynamic SQL you won't get this compilation error until runtime.

So, you need to code through the source of your function and find the misnamed thing. The line number which goes with the error message should help you here.

APC
  • 144,005
  • 19
  • 170
  • 281