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.
|| '(' || :fabkod || ')'
|| SYS_CONNECT_BY_PATH ( bom_mlz_stok_kod_no_on
|| DECODE (mlz_fabrika_kodu, NULL, NULL,
'(' || mlz_fabrika_kodu || ') )
|| ' - '|| mlz_adi, ' > '
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 ( 10.1.2.0.2)
Database Forms Builder is connecting to. (10.2.0.5)
OS and Version of where Forms Builder is installed. (Linux Itanium Red Hat Enterprise Linux Server release 4)
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.