Stored procedure query

From: <RanganathK_at_lgcommerznow.com>
Date: Wed, 20 Dec 2000 15:28:55 +0530
Message-Id: <10716.124995@fatcity.com>

Dear DBA Gurus,

I have a table by name prod_master whose structure as is below:

 MODEL_CD NOT NULL      VARCHAR2(20)
 CAT_LEVEL      NUMBER(3)
 PARENT             VARCHAR2(20)
 PROD_NAME   VARCHAR2(80)
 MAIN_CAT         VARCHAR2(20)
 PROD_DESC    VARCHAR2(1000)
 SCALE                 VARCHAR2(3)
 IMAGE_NAME   VARCHAR2(60)
 IMAGE_NAME1 VARCHAR2(60)

 IMAGE_NAME2 VARCHAR2(60)
 IMAGE_NAME3 VARCHAR2(60)

 CREATE_DT      VARCHAR2(8)
 MODIFY_DT       VARCHAR2(8)

 AVG_WEIGHT NUMBER(13,2) This is a hierarchical table in which the hierarchy based upon prod_name is as below:

PROD_NAME                              CAT_LEVEL
---------------------                              -------------------

Leather Accessories                          1
    Unisex Leather Accesories          2
        Unisex Office Bags                      3
        Unisex Travel Bags                    3
    Womens Leather Accesories      2
        Womens Handbags                   3
        Womens Office Bags                 3
    Mens Leather Accessories          2
        Mens Office Bags                       3
Shoes                                                     1
    Women Shoes                                 2
    Men Shoes                                       2
        Party Wear                                   3
        Leisure Wear                              3
        Formal Wear                               3

I want to create a stored procedure in which model_cd of a product is passed as input parameter. Upon executing the stored procedure it should return its parent, grand parent and great grand parent under that category (Assuming there are only 4 cat_levels). For example, if I pass the model_cd of Formal Wear it should return the prod_names Men Shoes and Shoes each separated by a delimiter say, Men Shoes||Shoes. Here the delimiter is '||'.

I hope you all got my point. Please help me in creating the stored procedure using PL/SQL as I could not do the filtering using where clause in
'connect by' clause. Any help in this regard will be highly appreciated. If you have got any clarifications please do get back to me. Received on Wed Dec 20 2000 - 03:58:55 CST