Posts

Showing posts from March, 2011

Custom type as input parameter in callable statement (PL/SQL)

Certain times in PL/SQL function, the input type could be of TYPE  example: table type example : CREATE OR REPLACE TYPE "SAMPLE_TYPE_TAB" as table of number(17,0); In oder to send the corresponding object in java, we need to construct the corresponding object which is of java.sql.Struct in java. the sample pl/sql could be ( skeleton shown) CREATE OR REPLACE PACKAGE  test_package IS FUNCTION inputAsType(type_tab sample_type_tab, creator VARCHAR2) RETURN NUMBER IS num NUMBER;   here the input parameter type_tab is of type "SAMPLE_TYPE_TAB" defined above. as per the defintion it has only one column of type number.   lets see how we can build the callable statement to invoke the above function.   1. build the connection   Class.forName(driver); Connection con = DriverManager.getConnection(url + db, user, pass); 2. define the package and function name as string String packagefunction = "{? = call test_package.inputAsType(?,