Home » SQL & PL/SQL » SQL & PL/SQL » how to use arrays in PL/SQL
how to use arrays in PL/SQL [message #205720] Mon, 27 November 2006 04:26 Go to next message
sreehari
Messages: 101
Registered: May 2006
Senior Member
Hi all

I have a procedure with the below statements..

SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('CERTIFICATE_ID', 'USER_ID', 'ADDRESS_ID', 'COMPANY_ID', 'PRODUCT_ID');
        IF a > 0 THEN
            CERTIFICATE_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('CONTRACT_ID');
        IF a > 0 THEN
            CONTRACT_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENTITLEMENT_ID', 'CONTRACT_PARTY_ID', 'USER_ID', 'ADDRESS_ID', 'COMPANY_ID');
        IF a > 0  THEN
            CONTRACT_PARTY_REFRESH(V_UPD_DATE);
        END IF;


		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENTITLEMENT_ID', 'CONTRACT_ID', 'CONTRACT_SITE_ID', 'ADDRESS_ID', 'PROGRAM_ID');
        IF a > 0 THEN
            ENTITLEMENT_REFRESH(V_UPD_DATE);
        END IF;


	    SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENT_PARTY_ID', 'USER_ID', 'ADDRESS_ID', 'COMPANY_ID');
	    IF a > 0 THEN
            ENT_PARTY_REFRESH(V_UPD_DATE);
        END IF;

        SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('EVENT_ID');
		IF a > 0 THEN
            EVENT_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('SALES_ORDER_DETAIL_ID', 'SALES_ORDER_ID', 'PROGRAM_ID', 'CONTRACT_SITE_ID', 'SALES_ORDER_LINE_SHIP_ID');
        IF a > 0 THEN
            SALES_ORDER_DETAIL_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('SALES_ORDER_ERROR_ID', 'SALES_ORDER_ID', 'PROGRAM_ID');
        IF a > 0 THEN
            SALES_ORDER_ERROR_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('SALES_ORDER_PARTY_ID');
        IF a > 0 THEN
            SALES_ORDER_PARTY_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('SALES_ORDER_ID', 'PROGRAM_ID', 'INVOICE_ID');
        IF a > 0 THEN
            SALES_ORDER_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('PRODUCT_ID', 'PRODUCT_SUPPLIER_ID', 'PHYSICAL_SUPPLIER_ID', 'RENEWAL_PRODUCT_ID');
        IF a > 0 THEN
            PRODUCT_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('FIXED_PRICE_LIST_ID', 'PROGRAM_ID');
        IF a > 0 THEN
            FIXED_PRICE_LIST_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENTITLEMENT_ID', 'CERT_PASSWORD_ID');
        IF a > 0 THEN
            FACT_CPAS_REPORT_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENTITLEMENT_ID', 'CERTIFICATE_ID');
        IF a > 0 THEN
            FACT_INSTALL_BASE_RPT_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('SALES_ORDER_DETAIL_POP_ID');
        IF a > 0 THEN
            SALES_ORDER_DETAIL_POP_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('ENT_TRANSACTION_ID');
        IF a > 0 THEN
            ENT_TRANSACTION_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('CERT_TRANSACTION_ID', 'COMPANY_ID');
        IF a > 0 THEN
            CERT_TRANSACTION_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('REQUEST_DETAIL_ID', 'PRODUCT_ID', 'COMPANY_ID', 'ADDRESS_ID');
        IF a > 0 THEN
            REQUEST_DETAIL_REFRESH(V_UPD_DATE);
        END IF;


I have used a variable called 'a' to store the count value in each SELECT statement, instead of that, is there any way that we can implement above statements using arrays in PL/SQL

For ex,

SELECT COUNT(*) into a[1] FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('CERT_TRANSACTION_ID', 'COMPANY_ID');
        IF a[1] > 0 THEN
            CERT_TRANSACTION_REFRESH(V_UPD_DATE);
        END IF;

		SELECT COUNT(*) into a[2] FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('REQUEST_DETAIL_ID', 'PRODUCT_ID', 'COMPANY_ID', 'ADDRESS_ID');
        IF a[2] > 0 THEN
            REQUEST_DETAIL_REFRESH(V_UPD_DATE);
        END IF;

Any help on this is greatly appreciated..

Thanks
Re: how to use arrays in PL/SQL [message #205727 is a reply to message #205720] Mon, 27 November 2006 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It is indeed possible. Here's a little example that gets the object type and the number that you own from user objects, and sticks them into a table.
  declare
    type ty_rec is record(col_1  varchar2(30),col_2  number);
   
    type ty_rec_Tab is table of ty_Rec;
    
    t_table  ty_rec_tab;
  
  begin
    select object_type,count(*)
    bulk   collect into t_table
    from   user_objects
    group by object_type;
    
    for idx in t_Table.first .. t_table.last loop
      dbms_output.put_line(t_Table(idx).col_1||' '||t_Table(idx).col_2);
    end loop;
  end;
  /
FUNCTION 8
INDEX 7
LOB 4
PACKAGE 7
PACKAGE BODY 6
PROCEDURE 12
SEQUENCE 2
TABLE 88
TRIGGER 5
TYPE 6
TYPE BODY 2
VIEW 5

PL/SQL procedure successfully completed.
Re: how to use arrays in PL/SQL [message #205759 is a reply to message #205720] Mon, 27 November 2006 07:20 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Just an observation:

You are using COUNT(*) to check an exsistance of rows in a table.
For each COUNT Oracle selects all rows answering a WHERE clause and counts them (usually calling SORT routine).

Try changing your statements to:
BEGIN
SELECT 1 into a FROM INSERT_UPDATE_DATA_TRACKING WHERE OBJECT_NAME IN ('CERTIFICATE_ID', 'USER_ID', 'ADDRESS_ID', 'COMPANY_ID', 'PRODUCT_ID') AND ROWNUM = 1;
-- If you got here it means that some data exists, so there is
-- no need for IF ... THEN ... END IF;

CERTIFICATE_REFRESH(V_UPD_DATE);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;


HTH.
Re: how to use arrays in PL/SQL [message #205764 is a reply to message #205759] Mon, 27 November 2006 08:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could use

SELECT 1 
INTO   a
FROM   dual
WHERE EXISTS (SELECT NULL
              FROM   INSERT_UPDATE_DATA_TRACKING 
              WHERE  OBJECT_NAME IN 'CERTIFICATE_ID', 'USER_ID', 'ADDRESS_ID', 'COMPANY_ID', 'PRODUCT_ID');
Previous Topic: Exception Handling_Unknown Error
Next Topic: Thumb Rule.
Goto Forum:
  


Current Time: Fri Dec 02 20:57:01 CST 2016

Total time taken to generate the page: 0.14451 seconds