File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Need Help with pl/sql (Compilation Error) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Need Help with pl/sql (Compilation Error)" Watch "Need Help with pl/sql (Compilation Error)" New topic
Author

Need Help with pl/sql (Compilation Error)

bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
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
Ranch Hand

Joined: May 08, 2001
Posts: 356
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<br />Sun Certified Programmer for Java 2 Platform<br />Oracle8i Certified Professional Database Administrator
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
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
Ranch Hand

Joined: May 08, 2001
Posts: 356
Issue command
sql> show errors;
and then see what is the problem.
HIH,
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
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
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy
bobby, morkos
Ranch Hand

Joined: Jan 04, 2002
Posts: 82
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.


Originally posted by Beksy Kurian:
Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need Help with pl/sql (Compilation Error)
 
Similar Threads
How to iterate in pure SQL?
Calling stored procedure problems
pl sql related problem
I have started this java program that passes an object to oracle pl/sql
Stored procedure which returns array