5 Replies Latest reply: Sep 11, 2013 9:49 AM by 979801 RSS

    Forms_DDL

    979801

      Hello experts,              I am using Oracle fusion middleware 11.1.2 (oracle forms 11g) at windows 7.

      I am trying to execute a query in oracle forms but unable.I have to store the query result into a integer type variable.

      The Sql is:

      select REGISTRATIONID  from (select registrationid ,dense_rank() over(order by REGISTRATIONID asc) ranking from TBL_REGISTRATION where ADMITTED=0)where ranking=1;
      --this query gives a registration ID(a numeric value) in sql developer but not in oracle forms.
      
      

      For this I have tried it with oracle forms but i think over clause flash some problem in oracle forms. Plesae give me solution. thank you regards aaditya .

        • 1. Re: Forms_DDL
          Priyasagi

          Hi,

           

          You have to use select into statement (or)  use select statement as a cursor definition other wise you can not use select statements directly in forms as mentioned in your example.

          • 2. Re: Forms_DDL
            979801

            Hi, Thanx to reply,                     Actually I have tried the query in both(using into and using cursor) but in both time there is an error Error 103 at line12,column 70 Encountered the symbol"[" when expecting from. the full querry I am using is :

            select REGISTRATIONID into REG from (select registrationid ,dense_rank() over(order by REGISTRATIONID asc) ranking from TBL_REGISTRATION where ADMITTED=0)where ranking=1;

             

            I think dense_rank analytical function is not supported in oracle forms 11g.,

            If So please suggest  me an alternative

            regards aaditya.

            • 3. Re: Forms_DDL
              Priyasagi

              Yes you are correct Aaditya dense_rank not supported by forms.

               

              Try this

               

              SQL>

              create view tbl_reg_vu as

              select registrationid ,dense_rank() over(order by REGISTRATIONID asc) ranking

              from TBL_REGISTRATION where ADMITTED=0

              /

              View created.

              SQL> select * from tbl_reg;

              REGISTRATIONID   RANKING
              -------------- ---------
                           9         1
                          10         2
                          10         2
                          25         3
                          47         4
                         500         5

              6 rows selected.

              In your forms change the statement as follows,

               

              select REGISTRATIONID into REG from tbl_reg_vu where ranking=1;

               

              I hope this will help you.

              • 4. Re: Forms_DDL
                979801

                You are right I have tried it with view n it works.I just wanted to know that dense_rank (or its alternative) function is applicable in forms or not. thank you

                • 5. Re: Forms_DDL
                  Christian Erlinger

                  Analytic functions (like dense_rank) are not supported in forms. The only way would be to use a view as already suggested or use dynamic SQL.

                  The view is the way better aproach, and basing your example there is no reason why you should use dynamic SQL instead.

                   

                  cheers