0
CURSOR recipe_attributes_cur(p_recipe_id IN recipe_data.recipe_id%TYPE) IS    

SELECT t.attribute_name, t.attribute_value,
     CASE
         WHEN ra.enum_list IS NOT NULL 
         THEN (SELECT choice_str
                 FROM recipe_enums_v
                WHERE enum_list = ra.enum_list
                  AND choice_id = rd.recipe_value_num)
         ELSE TO_CHAR (NULL)
       END AS enum_value,
       ra.datatype_id
***FROM table(acls_recipe.get_sic_attributes_value(p_recipe_id)) t***, recipe_attributes ra
where ra.attribute_name = t.attribute_name
order by  ra.attribute_id --t.attribute_name

UNION ALL
Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
Anshul Lavaniya
  • 11
  • 2
  • How is the function defined? Do you get an error when you try that? Or wrong results, or some other reason for asking? – Alex Poole Sep 30 '22 at 09:44
  • @Alex Poole It returns table, This is how its defined :- Function GET_SIC_ATTRIBUTES_VALUE (p_recipe_id IN NUMBER) RETURN RECIPE_VIEW_SET pipelined is ----------------------- ------------------------- for i in ( ------------------- ) loop pipe row(RECIPE_VIEW_REC_TYPE(i.attribute_name, i.attribute_value, null , null)); end loop; – Anshul Lavaniya Sep 30 '22 at 09:55
  • It's not allowing me to write full function here in comment, "too many characters" it says – Anshul Lavaniya Sep 30 '22 at 09:58
  • 2
    You should add information, particularly code, as an edit to the question - not as a comment. – Alex Poole Sep 30 '22 at 09:59
  • Because When I am compiling package with just the function added to package body, it's giving no compilation error, after I add this calling part in cursor query it gives compilation error, feels like the FROM clause has some problem – Anshul Lavaniya Sep 30 '22 at 10:01
  • 3
    Then include the full error you get in the question as well. We can't guess what you are seeing. Please include the type definitions as well. – Alex Poole Sep 30 '22 at 10:02
  • Adding function in another question, not able to add here even with editing ques – Anshul Lavaniya Sep 30 '22 at 10:13
  • Please remove irrelevant parts of the code (`case` expression, joins etc) and provide the definition of the function (or at least its signature and type definitions if it's not a scalar function). Function call is the same regardless of the place in the code: `function_name(arg1, arg2, arg3)`. For functions returning collections to be a row source - `from table(function_name(arg1,arg2,arg3)))` – astentx Sep 30 '22 at 10:29
  • 1
    Also from a previous question this is part of a package, so perhaps you are just seeing the generic 'created with compilation errors' message? If so you can do `show errors` in some clients, or query the `user_errors` view to see what those actual compilation errors are. And then include those in the question, if they don't give you enough information to fix the problem yourself. – Alex Poole Sep 30 '22 at 10:38
  • The `UNION ALL` after `order by` is most likely the source of your error. – Erich Kitzmueller Sep 30 '22 at 11:10
  • That illegal `order by` [was already discussed in your last question](https://stackoverflow.com/q/73901633/266304)... – Alex Poole Sep 30 '22 at 11:30
  • I've tried commenting the order by clause, still the same error persists :- – Anshul Lavaniya Sep 30 '22 at 15:53
  • You have still not updated the question to tell us what error you are getting. Or the type and function definitions. We can't help you if you don't include the information you've been asked for. – Alex Poole Sep 30 '22 at 16:25

1 Answers1

0

I'm afraid that you can't declare the cursor dynamicaly using something like in your question. But, there is another, pretty similar, way to do it.
You could generate the Select statement dynamicaly inside your package function and use that statement as a reference cursor. Here is a simple example of a testf2() function within a CMOD package:

FUNCTION testf2 (mWhat IN VarChar2) RETURN VarChar2 IS
    mySQL VarChar2(512);
    sq VarChar(1) := '''';  -- this is a single-quote character for mySQL string
    
    Begin
        mySQL := 'Select ' || sq || 'A' || sq || ' "A_LETTER", 1 "A_NUMBER" From ' || mWhat;
        RETURN mySQL;
    End testf2;

This function takes a VarChar2 parameter (it can be anything you need to generate your SQL) and returns generated Select statement. If the parammeter is 'DUAL' the function will return...

Select 'A' "A_LETTER", 1 "A_NUMBER" From DUAL

Now, to make it work get the Select statement generated by the function and use it as a reference cursor ...

SET SERVEROUTPUT ON
Declare
    c SYS_REFCURSOR;
    mySQL VarChar2(512) := CMOD.testf2('DUAL');
    c_letter VarChar2(1);
    c_number Number(1);
Begin 
    DBMS_OUTPUT.PUT_LINE(mySQL)
    --
    OPEN c FOR mySQL;
    FETCH c InTo c_letter, c_number;
    --
    DBMS_OUTPUT.PUT_LINE(c_letter || To_Char(c_number));
End;

--  R e s u l t :
--  anonymous block completed
--  Select 'A' "A_LETTER", 1 "A_NUMBER" From DUAL
--  A1

This way you can generate your own SQL - Open it as a reference cursor and loop through it to get your job done.
Regards...

d r
  • 3,848
  • 2
  • 4
  • 15