1 Reply Latest reply: Oct 11, 2013 3:57 AM by Marwim RSS

    SYS_CONNECT_BY_PATH should be defined


      Hi all,

      We aim to use the “SYS_CONNECT_BY_PATH” in Forms Builder.

      İn Reports Builder, it works properly. It returns the path of a column value from root to node as shown below.

      Here is the SQL clause from the report query.


      SELECT     bom_mlz_stok_kod_no_on,:mlz_kodu

                 || '(' || :fabkod || ')'

                 || SYS_CONNECT_BY_PATH (   bom_mlz_stok_kod_no_on

                                         || DECODE (mlz_fabrika_kodu, NULL, NULL,

                                                    '(' || mlz_fabrika_kodu || ') )

                                         || ' - '|| mlz_adi, ' > '

                                        ) PATH

            FROM t24uskuplbom, t24uskstkmlz

           WHERE mlz_stok_kod_no = bom_mlz_stok_kod_no_on AND CONNECT_BY_ISLEAF = 1

      CONNECT BY PRIOR bom_mlz_stok_kod_no_on = bom_mlz_stok_kod_no

      START WITH bom_mlz_stok_kod_no = :mlz_kodu


      but this does not work in Forms Builder **** (error : SYS_CONNECT_BY_PATH should be defined)


      Forms Builder being used (

      Database Forms Builder is connecting to. (

      OS and Version of where Forms Builder is installed. (Linux Itanium Red Hat Enterprise Linux Server release 4)



        • 1. Re: SYS_CONNECT_BY_PATH should be defined



          Reports sends its SQL directly to the DB, therefore you can use any language construct that is supported by your DB version.


          Forms has its own interpreter for PL/SQL and usually is a little behind the DB in supported features.


          As a workaround you can usually create a view in your DB and use a select on this view in Forms.