3 Replies Latest reply: May 10, 2013 7:23 AM by Christian Erlinger RSS

    Coalesce with Cursor in a form?

      Hi everyone,

      I have a problem when i try to use a coalesce method inside a cursor with a 11g form.

      my select liook like this

      SELECT c.sendingdate,
      coalesce((select no_action from action_type where no_action = c.no_action), c.no_action) no_action,

      i don't put the rest of the select, but when i compile the PL/SQL, i got error with the "," inside the coalesce coalesce((select no_action from action_type where no_action = c.no_action) _,_ c.no_action) no_action

      but this sql works in pl/sql developer

      and works on other form without cursor.

      thanks for your help.
        • 1. Re: Coalesce with Cursor in a form?

          It is possible the pl/sql engine in forms is not recognizing coalesce function. To be honest I haven't tried it. If that is the reason the simplest solution is to create database view and in forms you simply query that view.

          Best regards.
          • 2. Re: Coalesce with Cursor in a form?
            Hi, thanks for your answer,

            i think pl/sql in a form recognize Coalesce because i used it in a form inside DBMS text query execution. but if i try to use it with a CURSOR, i get the error. Same thing with VLM.. that strange a bit!

            my query works if i don't use CURSOR, if i use it, it doesnt works.

            thanks for your help.

            have a nice day
            • 3. Re: Coalesce with Cursor in a form?
              Christian Erlinger
              Neimad wrote:
              DBMS text query execution.
              I don't know what that means or is, probably Dynamic SQL?!?
              Anyway; coalesce doesn't seem to be the problem, your correlated subquery however is:
              select coalesce(1,2)
              into dummy
              from dual;
              works whereas
              select coalesce(1,2), coalesce((select 1 from dual), 2)
              into dummy
              from dual;
              does not compile.

              reason and solution is the same as already stated: the forms PL/SQL engine simply doesn't know correlated subquerys, and if you use a view you will be able to use a correlated subquery. Or you simply could rewrite your query to not use correlated subqueries.