Solved

SYS_CONNECT_BY_PATH in PL/SQL Block

Posted on 2004-04-20
13
2,146 Views
Last Modified: 2007-12-19
hello experts,

  Can I know whether we can use hierarchical queries with SYS_CONNECT_BY_PATH inside a PL/SQL block. Coz, when I tried to use I am getting error message "Invalid Column Name".

I appriciate if any body can help me on this.


Regards
Pradeep
0
Comment
Question by:pradeepbt279
  • 6
  • 5
  • 2
13 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10875689
I think that there must be another problem in your code there. It would be better if you post your procedure here.
0
 

Author Comment

by:pradeepbt279
ID: 10875980
hi Henka,

 Thanks for ur immediate reply. Well, I am pasting my sample code.

Declare  cursor c1 is
                                select item_code, sys_connect_by_path(qty, '*')qty
                                from  article_bom
                                where item_bom_type = 'I'
                                          start with bom_no = 'BOM5000'
                                          connect by bom_no = prior sub_bom_no
                                 group by item_code;
   begin
       for test in c1 loop
          insert into item_req values (test.item_code, test.qty);
      end loop;
   end;

This sample code is basically to explore Bill of Material and store them dynamically into a temporary table for each of the items retreived from this query. I am not just inserting values into the temp table. I have to do lot many calculations with in the loop.
But I find the SYS_CONNECT_BY_PATH is not being recognized at all.

Here is the structure for ur reference.

SQL> desc article_bom
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BOM_DET_ID                                         NUMBER
 BOM_NO                                    NOT NULL VARCHAR2(8)
 REVISION_NO                               NOT NULL NUMBER
 BOM_DET_HIST_AMEND_NO                              NUMBER
 COLOR_CODE                                NOT NULL VARCHAR2(6)
 SIZE_CODE                                 NOT NULL VARCHAR2(3)
 ITEM_BOM_TYPE                                      CHAR(1)
 ITEM_CODE                                          VARCHAR2(20)
 ITEM_TYPE                                          CHAR(1)
 SUB_BOM_NO                                         VARCHAR2(8)
 QTY                                       NOT NULL NUMBER
 UOM_CODE                                  NOT NULL VARCHAR2(3)
 RM_SIZE_1                                          NUMBER
 RM_SIZE_2                                          NUMBER
 RM_QTY                                             NUMBER
 RM_UOM_CODE                                        VARCHAR2(3)
 TRANSACTION_ID                            NOT NULL NUMBER
 STATUS                                             CHAR(1)
 ATTRIBUTE_1                                        VARCHAR2(30)
 ATTRIBUTE_2                                        VARCHAR2(30)
 ATTRIBUTE_3                                        VARCHAR2(30)
 ATTRIBUTE_4                                        VARCHAR2(30)
 ATTRIBUTE_5                                        VARCHAR2(30)
 ORG_ID                                    NOT NULL NUMBER
 UNIT_ORG_ID                               NOT NULL NUMBER
 COMPANY_ORG_ID                            NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
 CREATED_BY                                NOT NULL VARCHAR2(10)
 LAST_UPDATED_DATE                                  DATE
 LAST_UPDATED_BY                                    VARCHAR2(10)

SQL> desc item_req
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ITEM_CODE                                          VARCHAR2(20)
 QTY                                                NUMBER

When try to execute the PL/SQL it is giving the following error.

        select item_code, sys_connect_by_path(qty, '*')qty
                          *
ERROR at line 3:
ORA-06550: line 3, column 22:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 49:
PLS-00364: loop index variable 'TEST' use is invalid
ORA-06550: line 10, column 54:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored

Hope this gives u clear picture about my requirement..........

thanks in advance...

Pradeep

0
 

Author Comment

by:pradeepbt279
ID: 10876001
when I pasted the error the astriek (*) is trimmed under item_code. Actually it below Sys_connect_by_path(qty, '*') qty.

        select item_code, sys_connect_by_path(qty, '*')qty
                                  *
ERROR at line 3:
ORA-06550: line 3, column 22:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 49:
PLS-00364: loop index variable 'TEST' use is invalid
ORA-06550: line 10, column 54:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10876065
I think that problem is caused by QTY column - it is NUMBER.
This is from documentation (registration is free - http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions121a.htm#1038133)
SYS_CONNECT_BY_PATH (column, char)
Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in the same character set as column.
0
 

Author Comment

by:pradeepbt279
ID: 10876230
but the query works fine in the SQL Prompt. Here is the output for ur reference.

  1  select bom_no,item_code, sys_connect_by_path(qty, '*')qty
  2  from  article_bom
  3  where item_bom_type = 'I' start with bom_no = 'BOM5000'
  4* connect by bom_no = prior sub_bom_no
SQL> /

BOM_NO   ITEM_CODE            QTY
-------- -------------------- --------------------------------
BOM5004  C01020003.C02ZZZ.25Z *1*1*4
BOM5005  C01020002.C02ZZZ.25Z *1*1*3
BOM5001  C01020003.C02ZZZ.25Z *1*10
BOM5002  C01020001.ZZZZZZ.ZZZ *1*5
BOM5002  C01020001.ZZZZZZ.ZZZ *1*10
BOM5003  C01020001.ZZZZZZ.ZZZ *1*3
BOM5003  C01020001.ZZZZZZ.ZZZ *1*10
BOM5002  C01020001.ZZZZZZ.ZZZ *3*5
BOM5002  C01020001.ZZZZZZ.ZZZ *3*10
BOM5003  C01020001.ZZZZZZ.ZZZ *2*3
BOM5003  C01020001.ZZZZZZ.ZZZ *2*10

For testing I have changed the qty column to Varchar2. Still its same.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10876426
It is strange, I would try to use this:

Declare  cursor c1 is
   select item_code, sys_connect_by_path(qty, '*') qty1
   from  article_bom
   where item_bom_type = 'I'
   start with bom_no = 'BOM5000'
   connect by prior bom_no = sub_bom_no;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:pennnn
ID: 10881770
I don't know if it's a copy/paste error, but the original pl/sql block has a GROUP BY clause which should cause it to fail (although it should have been an ora-00979). Everything else looks fine to me...
Hope that helps!
0
 

Author Comment

by:pradeepbt279
ID: 10906125
Explanation of Requirement
--------------------------------

1. There is article_master_test table where different articles and their BOM (Bill of Material) is   stored.

2. For a given article 'A1' the BOM Number is selected and stored into a variable bom.

3. We then want to explode the BOM to arrive at the list of items and their quantities. For this purpose we are using SYS_CONNECT_BY_PATH and passing the path string to a function return_number which returns the qty in number.
   
4. The List of Items and their quantities are stored in a cursor and for every row returned by the cursor we are inserting the article_code 'A1', Item_code and Total Item Qty (Item qty x art_quantity) into a temporary table item_req.

5. The purpose as explained above is to have the complete requirement of Items and their total quantities for a given Article. And later we use these values in the same procedure for other calculations.
   
6. The PLSQL Block and the return_number function what we are using is again enclosed for reference.

Declare
      art_code varchar2(3) := 'A1';
      bom varchar2(8);
      art_quantity number := 100;
Begin
      select bom_no into bom from article_master_test where article_code = art_code;
            Declare
            cursor c1 is select item_code,
                                sum(return_number(Ltrim(sys_connect_by_path(qty, '*'), '*'))) qty
            from  article_bom where item_bom_type = 'I' start with bom_no = bom
            connect by bom_no = prior sub_bom_no
            group by item_code;
                 begin
                for test in c1 loop
                insert into item_req values (art_code, test.item_code,
                                    test.qty *art_quantity);
                end loop;
                end;            
end;
/



create or replace function return_number(val in varchar2) return number
is
result number;
      begin
            execute immediate 'select ' || val || ' from dual ' into result;
            return result;
      exception
            when others then
            return null;
      end;




Regards
Pradeep
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10915660
I think that you ought to move declaration of cursor to the DECLARATION part. I have removed also GROUP BY clause.

Declare
     art_code varchar2(3) := 'A1';
     bom varchar2(8);
     art_quantity number := 100;

     cursor c1 is select item_code,
                                sum(return_number(Ltrim(sys_connect_by_path(qty, '*'), '*'))) qty
          from  article_bom where item_bom_type = 'I' start with bom_no = bom
          connect by bom_no = prior sub_bom_no;
Begin
     select bom_no into bom from article_master_test where article_code = art_code;
     begin
          for test in c1 loop
              insert into item_req values (art_code, test.item_code,
                                    test.qty *art_quantity);
          end loop;
     end;          
end;
0
 

Author Comment

by:pradeepbt279
ID: 10915755
Hi Henka,

 I tried executing ur code. But here is the result.

Declare
     art_code varchar2(3) := 'A1';
     bom varchar2(8);
     art_quantity number := 100;
     cursor c1 is select item_code,
                                sum(return_number(Ltrim(sys_connect_by_path(qty, '*'), '*'))) qty
          from  article_bom where item_bom_type = 'I' start with bom_no = bom
          connect by bom_no = prior sub_bom_no;
Begin
     select bom_no into bom from article_master_test where article_code = art_code;
     begin
          for test in c1 loop
              insert into item_req values (art_code, test.item_code,
                                    test.qty *art_quantity);
          end loop;
     end;
end;


SQL> /
                                sum(return_number(Ltrim(sys_connect_by_path(qty, '*'), '*'))) qty
                                                                       *
ERROR at line 6:
ORA-06550: line 6, column 48:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 5, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 13, column 27:
PL/SQL: ORA-00913: too many values
ORA-06550: line 13, column 15:
PL/SQL: SQL Statement ignored
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10915894
What happens if there is no return_number function there ?
I am rather confused, I hope someone else can help you.
0
 
LVL 11

Accepted Solution

by:
pennnn earned 50 total points
ID: 10918973
My first thought when you mentioned that it works in SQL, but not in PL/SQL was that you're on Oracle 8i which has a lower version of the SQL engine in PL/SQL so some of the new stuff that works in SQL doesn't work in PL/SQL and you have to use dynamic SQL as a workaround. But then I checked the documentation and it seems that the sys_connect_by_path was introduced in 9i (at least I couldn't find it in the 8i docuemntation).
I tested the scenario using a hierarchical table that I have in place and it works perfectly fine on Oracle 9.2.0.3. If you are on a lower version the only thing I can think of is that there might have been a bug with the sys_connect_by_path function that was later fixed.
Here's the code I used and which works fine. The table structure is not the same as yours, but that shouldn't be a problem. I also played around with different column aliases and using different columns for the GROUP BY clause but it worked fine in all cases.
BEGIN
   FOR rec IN (
     SELECT related_num x,
         SUM(return_number(LTRIM(sys_connect_by_path(num,'*'),'*'))) num
       FROM related_contract
      START WITH num = 1511
      CONNECT BY PRIOR related_num = num
      GROUP BY related_num)
   LOOP
      dbms_output.put_line(rec.x||rec.num);
   END LOOP;
END;

Hope that helps!
0
 

Author Comment

by:pradeepbt279
ID: 10924967
Hi Penn,

 U got it right. I was using the version 9.0.1.1. Now that I tried in 9.2.0.1 it worked fine. Like u mentioned it might be a bug or the feature is limited to SQL.

Anyway thanks.

Regards
Pradeep
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
percentage remaining 6 40
PL/SQL Two statements 6 50
Last record chosen in Oracle Query 3 35
Dataware house query tuning 9 31
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now