Skip to Main Content
  • Questions
  • Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srikant.

Asked: January 14, 2017 - 6:56 am UTC

Last updated: January 23, 2017 - 2:54 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Team,

We have one requirement where:
1) we have to write one proceudre which will insert into a clob column meant for email body.
This procedure should accept text more than 32K.
2) the application or routine will execute this procedure by providing this "entire text" as the parameter to the procedure

Now I know that there is limit for PLSQL variable i.e 32K.

If the text is out of 32k limit then, the procedure would throw an error.

Please could you help me how I can design my procedure to accept text more than 32K.



Regards,
Srikant

and Connor said...

There is no 32k limit on *plsql parameters*, it is only a limit on the varchar2 datatype. You simply need to use a clob, eg

SQL> create or replace
  2  procedure P(c clob) is
  3  begin
  4    dbms_output.put_line(dbms_lob.getlength(c));
  5  end;
  6  /

Procedure created.

SQL>
SQL> variable local_var clob;
SQL>
SQL> exec :local_var := 'Some text';

PL/SQL procedure successfully completed.

SQL> exec for i in 1 .. 20 loop :local_var := :local_var || :local_var; end loop;

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> exec p(:local_var);
9437184

PL/SQL procedure successfully completed.


So there I have passed a 9meg clob from SQL Plus to a database procedure

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Please help with the actual implementation

Srikant Swamy, January 15, 2017 - 2:35 pm UTC

Hi Connor,
Thanks for explaining.

In your example you have assigned the "big" string to the clob type variable in SQLPlus environment.

But in actual scenario, 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?. Because I am not generating the data. The team which will provide this string is suppose to call my procedure. This will be implemented in unix.
--Procedure Creation

create or replace procedure Proc_accept_string(p_str IN CLOB)
IS
(

--code to insert string into clob column
);
/

--Calling the procedure

Proc_accept_string('Very large string');

The procedure will be called in unix shell script and parameter that will be passed will be like 'cat file.txt'.

Will the above work because the procedure is accepting the string in a clob input param ?

Regards,
Srikant Swamy







Connor McDonald
January 17, 2017 - 1:19 am UTC

Passing the data correctly is the responsibility of the *client* software. See Rajeshwaran's demos in the followup below.

Cast

Gh, January 16, 2017 - 7:48 am UTC

In your shell sql session your cast the string into clob - to_clob ; then pass it as parameter

Cast

Gh, January 16, 2017 - 7:48 am UTC

In your shell sql session your cast the string into clob - to_clob ; then pass it as parameter

Passing CLOB as input parameter

Rajeshwaran, Jeyabal, January 16, 2017 - 8:06 am UTC

....
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.
Connor McDonald
January 17, 2017 - 1:19 am UTC

nice input

Numeric or Value error when assigning to clob

Srikant, January 21, 2017 - 5:24 am UTC

Hi Connor,

I am getting the numberic or value error when I try to do the below. I am trying to assign 40000 length string to a clob variable, it should allow me right ?

I a loop if try to append chunks of string then there is no issue, the example that you demonstrated, but if I try to assign "more than 32K string" at one go then there is an error.



SET SERVEROUTPUT ON

DECLARE

my_var CLOB;

BEGIN

my_var:=RPAD('*',40000,'*');

DBMS_OUTPUT.PUt_LINE(length(my_var));

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUt_LINE(SQLCODE||' '||SQLERRM);

END;




-6502 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Connor McDonald
January 22, 2017 - 2:54 am UTC

rpad returns a varchar2, so it is limited to 32767. But the clob length is not limited to that, eg

SQL> declare
  2    my_var clob;
  3  begin
  4    my_var:=rpad('*',40000,'*');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


SQL>
SQL>
SQL> declare
  2    my_var clob;
  3  begin
  4    my_var:=rpad('*',32000,'*');
  5    my_var := my_var||my_var||my_var||my_var||my_var||my_var;
  6  end;
  7  /

PL/SQL procedure successfully completed.


Can we assign string mre than 32K to a clob varibale via sqlplus

Srikant, January 22, 2017 - 9:02 am UTC

Hi Connor,

Exactly, below also works:

my_var:=RPAD(TO_CLOB('*'),40000,'*');

Now I am sure that in plsql enviroenment we are limited to 32K if we speak of string.

Procedure with input clob parameter will be called in unix via sqlplus and assigned the string as a parameter.

Before assigning string directly can we declare a clob variable and assign the 40K string to it and then pass this clob variable to the procedure.

Can you pls show how can it be done via Unix shell script (sqlplus)

sqlplus -s admin/password << EOF
DECLARE

var clob;

BEGIN

var:=`cat 40kfile.txt`

ins_clob(var);


END;
exit;
EOF


Will the above scenario work?





Connor McDonald
January 23, 2017 - 2:54 am UTC

Unlikely, because

a) sqlplus has an input line limit (2500 chars)
b) it will still be interpreted as a varchar2

For that kind of scenario, I'd look at processing '40kfile' as an external table.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here