....
if I suppose create a proc with clob input parameter, the question remains as to how will the procedure accept the "big" string.
If it accepts this string in a clob parameter of the proc, will it throw an error?.
....
You could pass CLOB as input parameter from any client program, given that it should of right datatype to match the inputs.
demo@ORA12C> create table t(x clob);
Table created.
demo@ORA12C>
demo@ORA12C> create or replace procedure
2 load_data(p_inputs clob) as
3 begin
4 insert into t(x)
5 values(p_inputs);
6 commit;
7 end;
8 /
Procedure created.
demo@ORA12C>
To invoke this procedure from PL/SQL it can be done like this
demo@ORA12C> declare
2 l_inputs clob;
3 begin
4 for i in 1..10
5 loop
6 l_inputs := l_inputs || rpad('*',4000,'*');
7 end loop;
8 dbms_output.put_line( ' length =' ||length(l_inputs) );
9 load_data(l_inputs);
10 end;
11 /
length =40000
PL/SQL procedure successfully completed.
demo@ORA12C>
demo@ORA12C> select dbms_lob.getlength(x) from t;
DBMS_LOB.GETLENGTH(X)
---------------------
40000
1 row selected.
demo@ORA12C> truncate table t;
Table truncated.
Similarly to invoke this procedure from a JAVA program, it goes like this.
D:\>set path=%path%;C:\Program Files\Java\jdk1.8.0_77\bin
D:\>set classpath=%classpath%;D:\app\sqldeveloper\jdbc\lib\ojdbc7.jar;
D:\>set classpath=%classpath%;.;
D:\>java -version
java version "1.8.0_77"
Java(TM) SE Runtime Environment (build 1.8.0_77-b03)
Java HotSpot(TM) 64-Bit Server VM (build 25.77-b03, mixed mode)
D:\>javac MyClobVariableTest.java
D:\>java MyClobVariableTest
O.K
D:\>exit
demo@ORA12C> select dbms_lob.getlength(x) from t;
DBMS_LOB.GETLENGTH(X)
---------------------
51000
1 row selected.
demo@ORA12C>
D:\>type MyClobVariableTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Clob;
import java.util.*;
import java.lang.*;
public class MyClobVariableTest {
public static String padRight(String s, int n) {
return String.format("%0$-"+n+"s", s);
}
public static void main(String[] args) {
Connection conn = null;
PreparedStatement prepStmt = null;
ResultSet rs = null;
String sqlStmt = null;
Clob myclob = null;
try {
Class.forName("oracle.jdbc.OracleDriver").newInstance();
String connectionUrl ="jdbc:oracle:thin:@LT035221:1522:ORA12c";
String connectionUser = "demo";
String connectionPassword = "demo";
conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
sqlStmt = " begin load_data(?); end; ";
myclob = conn.createClob();
prepStmt = conn.prepareStatement(sqlStmt);
myclob.setString(1,padRight("*",1000));
for (int i=1; i<=50; i=i+1){
myclob.setString( (1000*i)+1 ,padRight("*",1000));
}
prepStmt.setClob(1,myclob);
rs = prepStmt.executeQuery();
prepStmt.close();
System.out.println("O.K");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (prepStmt != null)
prepStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
So you could invoke this Procedure with CLOB as inputs from any client program, by providing the right arguments to it.