Custom DataType in Oracle Database.

-
Oracle provides the predefined types which developer can use in the PL/SQL. Those types are like NUMBER, ARRAYOFNUMBERS, VARCHAR2 and many more. 

Developer who consumes PL/SQL from JAVA can easily use those types in case of normal scenario. But consider a scenario where, developer want to send collections to PL/SQL, then how that would be possible. So, oracle have provided an option to create own custom types as per requirement and use that types in PL/SQL. That custom type support essay access to data types from JAVA and speed up execution and developer can achieve performance too.

Syntax of creation of custom type:-

#1 TABLEOF3STRINGS:-
create or replace TYPE "TRIPLETOFSTRINGS" as object (field1 varchar2(20), field2 varchar2(20), field3 varchar2(20));
create or replace TYPE "TABLEOF3STRINGS" as table of TripletOfStrings;

#2 Structure with number field:-
create or replace TYPE "LINEITEM" as object (item_name varchar2(30),quantity number, unit_price number(12,2) );

Once the above type are created in oracle then developer needs to build the collection accordingly and needs to send to PL/SQL. 

In Case of tableof3number, "Collection.toArray(new String[Collection.size()][2]);" would be the call to send data to PL/SQL function or procedure call. 

Developer needs to have own JDBCType implementation for that new custom data types. Existing implementation from JDBCType will be helpful to implement that custom datatype in JAVA. 



  

Comments

Post a Comment

Popular posts from this blog

Reverse String, Unchanged position of special characters.

-
Reverse a string keeping the position of special characters unchanged in reversed string. ex.          i/p -     a,b#d$e&d          o/p -     d,e#d$b&a Solution :- static boolean isAlphabet(char x) {         return (x >= 'A' && x <= 'Z' ) || (x >= 'a' && x <= 'z' );     }         public static void main(String[] args) {                 String s = "Ab,c,de!$";//a,b$c";         System.out.println("Original String:- " + s);         char[] str = s.toCharArray();         int l = 0;         int r = s.length() - 1;                 while (l<r) {             if(!isAlphabet(str[l])) {                 l++;             }else if(!isAlphabet(str[r])) {                 r--;             }else{                 char a = str[l];                 char b = str[r];                                 str[l] = b;                 str[r] = a;                                 l++;          
Read more

Java 8 Questions.

-
what is functional interface. Ans :- An interface defined with only on abstract method. That interface can have multiple default and static methods. Supplier, consumer and predicate are the functional interface provided by 8. Supplier FI can used as factory pattern to create object using get method of that interface. i.e. import jdk.nashorn.internal.objects.annotations.Setter; import java.util.UUID; @Setter public class Car { UUID id; String name; } public class MarutiCar extends car { } import jdk.nashorn.internal.objects.annotations.Setter; import java.util.UUID; import java.util.function.Supplier; public class CarSupplier { private static <T extends Car> T getCar( final Supplier<T> supplier, final String name) { T car = supplier.get(); car.setId(UUID.randomUUID()); car.setName(name); return car; } public static void main(String args[]) { MarutiCarr car = getCar(MarutiCar:: new , "Maruti C
Read more