Need Help with pl/sql (Compilation Error)

[bobby, morkos]

This is my program that does not compile correctly, could anyone tell me what's wrong eith my pl/sql program. Thanks.
CREATE OR REPLACE Function show(iorec in out rectype) IS
DECLARE
v_Table NumTab := iorec.col1;
v_Count BINARY_INTEGER :=1;
BEGIN
LOOP
IF v_Table.EXISTS(v_Count) THEN
DBMS_OUTPUT.PUT_LINE('v_Table(' || '); ' || v_Table(v_Count));
v_Count := v_Count + 1;
ELSE
EXIT;
END IF;
END LOOP;
END;
END show;
/

[Muhammad Farooq]

Try this,may be this work out. Sorry as I don't have all the data on which you are working
CREATE OR REPLACE Function show(iorec in out rectype)
return number
IS
v_Table NumTab := iorec.col1;
v_Count BINARY_INTEGER :=1;
BEGIN
LOOP
IF v_Table.EXISTS(v_Count) THEN
DBMS_OUTPUT.PUT_LINE('v_Table(' || '); ' || v_Table(v_Count));
v_Count := v_Count + 1;
ELSE
EXIT;
END IF;
END LOOP;
END;
/

HIH

[bobby, morkos]

SQL> CREATE OR REPLACE Function show(iorec in out rectype)
2 return number
3 IS
4 v_Table NumTab := iorec.col1;
5 v_Count BINARY_INTEGER :=1;
6 BEGIN
7 LOOP
8 IF v_Table.EXISTS(v_Count) THEN
9 DBMS_OUTPUT.PUT_LINE('v_Table(' || '); ' || v_Table(v_Count));
10 v_Count := v_Count + 1;
11 ELSE
12 EXIT;
13 END IF;
14 END LOOP;
15 END;
16 /
Warning: Function created with compilation errors.

Originally posted by Muhammad Farooq:
Try this,may be this work out. Sorry as I don't have all the data on which you are working
CREATE OR REPLACE Function show(iorec in out rectype)
return number
IS
v_Table NumTab := iorec.col1;
v_Count BINARY_INTEGER :=1;
BEGIN
LOOP
IF v_Table.EXISTS(v_Count) THEN
DBMS_OUTPUT.PUT_LINE('v_Table(' || '); ' || v_Table(v_Count));
v_Count := v_Count + 1;
ELSE
EXIT;
END IF;
END LOOP;
END;
/

HIH

[Muhammad Farooq]

Issue command
sql> show errors;
and then see what is the problem.
HIH,

[bobby, morkos]

SQL> show errors;
Errors for FUNCTION SHOW:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/28 PLS-00905: object SYSTEM.RECTYPE is invalid

Originally posted by Muhammad Farooq:
Issue command
sql> show errors;
and then see what is the problem.
HIH,

[Beksy Kurian]

Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy

[bobby, morkos]

yes, I'm creatring this under system. I have a very important question to ask you. In java, I want to send to pl/sql a method that takes the following:
pl/sqlmethod(p1obj1.toArray(), p1obj2.toArray(), number, varchar2)
and in pl/sql I receive the results as an Array, that I loop in java.
How do you do the above in java? I started it and could you please take a look if I'm doing it right.

private static ArrayList  p1obj1 = new ArrayList();
    private static ArrayList  p1obj2 = new ArrayList();
    
 public Long[] findNuids(String ownerid, PropertyInfo infos[]) throws RemoteException {
                
      int num = infos.length;
      for(int m=0; m < num; m++){
        p1obj1.add( infos[m].getPropertyCode() ); 
        p1obj2.add( infos[m].getPropertyCode() ); 
      }        
            
      OracleDb ora = new OracleDb();
      ora.DriverConnected();
                                
       try {
         ora._con = ora.getConnection();
         // Calling the Oracle procedure
        
         StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",ora._con);      
         STRUCT p1struct = new STRUCT(desc1, ora._con, p1obj1.toArray());      
         STRUCT p2struct = new STRUCT(desc1, ora._con, p1obj2.toArray());   
               
         OracleCallableStatement  ocs = (OracleCallableStatement )
                                        ora._con.prepareCall("{call " + 
                                        "pegasustypes.show_pegasus(?,?," +
                                        ownerid + ", " + num + ")}");      
         
         ocs.setOracleObject(1, p1struct);    
         ocs.setOracleObject(2, p2struct); 
         ocs.registerOutParameter(1,OracleTypes.STRUCT,"RECTYPE");      
         ocs.registerOutParameter(2,OracleTypes.STRUCT,"RECTYPE");    
         ocs.registerOutParameter(3,OracleTypes.CURSOR);
         ocs.execute();     
    
         p1struct = ocs.getSTRUCT(1);    
         //p2struct = ocs.getSTRUCT(2);      
         p1obj = p1struct.getAttributes();     
         //Object [] p2obj = p2struct.getAttributes();        
         
         //one
         Object[] ar = p1struct.getAttributes();    //p1obj1.toArray();
         int oranum= ar.length;
         for(int s=0; s < oranum; s++){
            System.out.println("Ao 1 demo  >>" + ar[s] );
         }
      
         //two
         ora._rs=(ResultSet)ocs.getObject(1);
         
         for(int k = 0; k < infos.length; k++){
           System.out.println(ora._rs.getString("pegasus_id").toString()); 
           System.out.println("Record: " + p1obj[k]); //+ "  " + p2obj[k]);
         }
         ora._con.close();
        } catch (SQLException e){
          //System.out.println("Application error.");
          //e.printStackTrace();
          _log.info("Application error.  SQLException : "  +
                    e.toString());
        }
                    
      Long[] nuids = new Long[infos.length];
      for(int i = 0; i < infos.length; i++){
          try {
            nuids[i] = new Long( p1obj[i].toString() );
          } catch(Exception e) {  // NotFoundException
            nuids[i] = null;
          }
      }   
      return nuids;         
    }

Originally posted by Beksy Kurian:
Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy