Win a copy of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Need Help with pl/sql (Compilation Error)

 
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;
/
 
Ranch Hand
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 356
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Issue command
sql> show errors;
and then see what is the problem.
HIH,
 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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,

 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Looks like you haven't created the object type rectype correctly! Are you doing this under system??!!!
Beksy
 
bobby, morkos
Ranch Hand
Posts: 82
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic