0

I have a table valued function:

create or replace
FUNCTION                                                                                  "GetPositionsWithSymbol" 
(
  par_Symbol "Symbols"."Symbol"%TYPE,
  ErrorCode OUT "ErrorDefs"."ErrorCode"%TYPE
)
RETURN "TableTypes"."PositionTable"

and the type definition is as follows:

create or replace
PACKAGE      "TableTypes" AS 

TYPE          "PositionTable" IS TABLE OF MainSchema."Positions"%ROWTYPE;

END "TableTypes";

when I call this function, I need to iterate on the result, something like this:

for rec in (select * from table(MainSchema."GetPositionsWithSymbol"(par_Symbol, ErrorCode)))
  loop
    var_TotalGivenAmount := var_TotalGivenAmount + rec."Amount";
  end loop;

but this syntax results in error and oracle claims that rec."Amount" is not recognized and must be declared. Am I using the wrong syntax for this?

EDIT: I call the function from another Schema.

SJ.Jafari
  • 1,236
  • 8
  • 27
  • 39
  • 1
    What is the definition of `MainSchema."Positions"`? Does it have a column `"Amount"`? Are you really sure that you want to be using case-sensitive identifiers all over the place? That will not make whoever has to support this code in the future very happy. – Justin Cave Nov 11 '13 at 09:02
  • the table "Positions" is placed on another schema, and it surely does have a column "Amount". case-sensitive identifiers is not the issue. I am skeptical that it might be a matter of granting. – SJ.Jafari Nov 11 '13 at 09:16
  • Can you post the definition of `MainSchema."Positions"`? Case sensitive identifiers may not be the issue but that is very unconventional PL/SQL so it will likely cause problems for future maintainers. – Justin Cave Nov 11 '13 at 09:34
  • Strangely enough, when I remove the quotes it works fine, while my definition of that field is like this: "Amount" NUMBER(15,0)! – SJ.Jafari Nov 12 '13 at 08:15
  • Is the column name you see in `all_tab_columns` in upper case or mixed case? `select column_name from all_tab_columns where owner ='MAINSCHEMA' and table_name = 'Positions' and upper(column_name) = 'AMOUNT'`? If removing the double quotes works, that implies that the identifier was not created in a case-sensitive manner and will be in all upper case in the data dictionary. That would mean that whatever definition you are looking at is not what was actually executed. And this is one more reason why case-sensitive identifiers can be problematic. – Justin Cave Nov 12 '13 at 15:04

0 Answers0