help in creating procedure

[Namrta Pandey]

Hi,

I am creating a procedure which takes an array and a numeric value as input and returns 2 arrays and a resultset , signature is as follows :

CREATE OR REPLACE

PROCEDURE Inven_Alloc_Refreshsummary

  (bar_codes IN TA_ITEM_CODE ,

   dms_dtr_code IN NUMERIC,

   not_avail_items OUT TA_ITEM_CODE ,

   avail_items OUT TA_ITEM_CODE ,

   prodList  OUT TA_PROD_LIST)

where TA_ITEM_CODE is a type :

CREATE OR REPLACE

TYPE             TA_ITEM_CODE AS TABLE OF  OBJ_ITEM_CODE

/

CREATE OR REPLACE

TYPE             OBJ_ITEM_CODE AS OBJECT

(

  ITEM_CODE VARCHAR2(50)

)

/

In procedure I want to retrieve some values according to some condition, Query which i want to execute is :

 CURSOR cur_avail_items IS SELECT itm.ITEM_NO FROM ITEM itm WHERE itm.ALLOCATED = 'N' AND itm.AVAILABLE = 'Y' AND itm.STATUS = 'ACTIVE'


AND itm.IS_SALEABLE = 'Y'

AND itm.PRODUCT_CATEGORY<>'E-Load'

AND itm.DMS_DTR_CODE =  dms_dtr_code

AND itm.ITEM_NO IN ([b]bar_codes[/b]) ;   

In above code bar_codes is an array which i'll send from java.
When I'm trying to compile the procedure in Oracle it is giving me exception "Expected number but found TA_ITEM_CODE". I'm not able to understand why its giving an error
because itm.ITEM_NO is of type Varchar in Item table. Please help me where I'm going wrong. Just for the reference following is my java code
which i'll use to call this procedure once it will compile properly :

   
ArrayDescriptor objBarCodeDesctr = null;
ARRAY objBarCodeArray = null;
Object[][] objItem = null;
 if (barCodesToScan == null)

                  {

                        objItem = new Object[length][1];

                        for (int i = 0; i < barCodesToScan.length; i++)

                        {

                              String value = barCodesToScan[i];

                              objItem[i][0] = value;

                        }

                        objBarCodeDesctr = ArrayDescriptor.createDescriptor("TA_BARCODE", connection);

                        objBarCodeArray = new ARRAY(objBarCodeDesctr, connection, objItem);

                  }

                 

                  cs = (OracleCallableStatement) connection.prepareCall("Call inven_alloc_refreshsummary(?,?,?,?,?)");

                  cs.setARRAY(1, objBarCodeArray);

                  cs.setLong(2, dmsDtrCode);

                  cs.registerOutParameter(3, OracleTypes.ARRAY);

                  cs.registerOutParameter(4, OracleTypes.ARRAY);

                  cs.registerOutParameter(5, OracleTypes.CURSOR);

                  cs.executeQuery();
 

Thanks in Advance,
Namrta.

[Agador Paloi]

I think it doesnt like your In part of your where clause:

 CURSOR cur_avail_items IS SELECT itm.ITEM_NO FROM ITEM itm 
     WHERE itm.ALLOCATED = 'N' 
          AND  itm.AVAILABLE = 'Y' 
          AND  itm.STATUS = 'ACTIVE'   
           AND itm.IS_SALEABLE = 'Y'    
           AND itm.PRODUCT_CATEGORY<>'E-Load'    
           AND itm.DMS_DTR_CODE =  dms_dtr_code    
           AND itm.ITEM_NO IN ([b]bar_codes[/b]) ;    <-- not liking this

you will need to use the table function to create a vararray from the bar_codes table.

[Namrta Pandey]

You are right that the problem is with bar_codes, I have already created this of table type. But the problem is this that bar_codes contains values of array and when i'm using this variable in Select statement using "in" then it is not accepting that. Although I have resolved the problem by iterating the bar_codes and putting Select statement in loop, but still for other purpose i need to use "In" clause in select statement. Is there anyway to use array passed by java in Select statement.

Thanks,
Namrta.

[Namrta Pandey]

Hi,

Ref. to the above code where i am passing array to the procedure :

if (barCodesToScan != null && barCodesToScan.length > 0) 
{
      int i = 0;
      objItem = new Object[barCodesToScan.length][1];

      for (String value : barCodesToScan) {
	objItem[i][0] = value;
	i++;					
      }
    objBarCodeDesctr = ArrayDescriptor.createDescriptor("TA_ITEM_CODE",connection);
    objBarCodeArray = new ARRAY(objBarCodeDesctr, connection, objItem);
    cs = (OracleCallableStatement) connection.prepareCall("Call Inven_Alloc_Refreshsummary(?,?,?,?)");
     cs.setARRAY(1, objBarCodeArray);
     cs.setLong(2, dmsDtrCode);
     cs.registerOutParameter(3, OracleTypes.ARRAY, "TA_ITEM_CODE");
     cs.registerOutParameter(4, OracleTypes.ARRAY, "TA_ITEM_CODE");
     cs.executeQuery();

I am getting performance issue when two users simultaleously try to run the application my weblogic server is getting down. I have more than 7000 values in array, please tell me the efficient way to pass array in procedure from java code.

Waiting for the reply.

Thanks & Regards,
Namrta.

[Agador Paloi]

If you are passing in an array of 7000 to use in your in clause , that would kill your performance.
You could put your 7000 entries into a table with an index and do a join instead of using the in clause.
The in clause would work fine for smaller volume .

[Namrta Pandey]

Thanks for the reply Agador, In my procedure I am not using in clause rather i am using the following code :

 FOR i IN 1..bar_codes.LAST
    LOOP
    
    
    vr_barcode := bar_codes(i).ITEM_CODE; 
    
     BEGIN     
             SELECT itm.ITEM_NO into vr_avail_items FROM ITEM itm WHERE itm.ALLOCATED = 'N' AND itm.AVAILABLE = 'Y' AND itm.STATUS = 'ACTIVE' AND itm.IS_SALEABLE = 'Y' AND itm.PRODUCT_CATEGORY<>'E-Load' AND itm.DMS_DTR_CODE =  dms_dtr_code and itm.ITEM_NO = vr_barcode;
      EXCEPTION

         WHEN OTHERS THEN
              vr_avail_items := NULL;
         END;
     
    if vr_avail_items is null or vr_avail_items = ' '
    THEN
     
       vr_NOT_TA_ITEM_CODE := NEW OBJ_ITEM_CODE('');
       vr_NOT_TA_ITEM_CODE.ITEM_CODE := vr_barcode ;
      
       ta_not_avail_items.EXTEND;
        ta_not_avail_items(ta_not_avail_items.LAST) := vr_NOT_TA_ITEM_CODE; 
      
    else if(vr_avail_items is not null)
    then
     
          vr_TA_ITEM_CODE := NEW OBJ_ITEM_CODE('');
          vr_TA_ITEM_CODE.ITEM_CODE := vr_barcode ;
      
            ta_avail_items.EXTEND;
            ta_avail_items(ta_avail_items.LAST) := vr_TA_ITEM_CODE; 
        
    ENd if;
    end if;
    
    
    
    
    end loop;

but my problem is not with the procedure, It is at the java end, when I am passing array values to the procedure from java code at that time server is getting down.