0

I have the following procedure in oracle : 

CREATE OR REPLACE PROCEDURE
INSERT_BYTE(pID IN Number,pData IN CLOB )
IS
  reallybigtextstring CLOB := pData; 
    i                   INT; 
BEGIN
   WHILE Length(reallybigtextstring) <= 60000 LOOP 
        reallybigtextstring := reallybigtextstring 
                             || '000000000000000000000000000000000'; 
   END LOOP; 

    INSERT INTO TESTCLOB 
                (ID ,C,D) 
    VALUES     (pID, 
                reallybigtextstring, 
                'done'); 
END;
/

I am passing a string of more than 40000 bytes to this procedure by this code :

Dim Conn
  Set Conn = Server.Createobject("ADODB.Connection")  
  Conn.Open "Driver={Oracle in XE};DBQ=xe;UID=biotpl;PWD=biotpl;"  
  strSQL = "INSERT_BYTE(1,'"& str &"') " 

  Set objExec = Conn.Execute(strSQL)  
  If Err.Number = 0 Then  
Response.write("Process completed successfully...")  
  Else  
 Response.write("Error Save ["&strSQL&"] ("&Err.Description&")")  
 End IF  
 Conn.Close()  
 Set objExec = Nothing  
 Set Conn = Nothing

But the code crashes . If I have written the stored procedure as following :

CREATE OR REPLACE PROCEDURE
INSERT_BYTE(pID IN Number  )
IS
  reallybigtextstring CLOB := '123'; 
    i                   INT; 
BEGIN
   WHILE Length(reallybigtextstring) <= 60000 LOOP 
        reallybigtextstring := reallybigtextstring 
                             || '000000000000000000000000000000000'; 
   END LOOP; 

    INSERT INTO TESTCLOB 
                (ID ,C,D) 
    VALUES     (pID, 
                reallybigtextstring, 
                'done'); 
END;
/

Then the code works fine . The main problem is passing more than 4000 characters to clob data . How to pass more than 4000 characters to clob field > Please help me .

Dominic Cotton
  • 789
  • 10
  • 34
Christopher Marlowe
  • 2,098
  • 6
  • 38
  • 68
  • What's the "crash?" What error do you get and where? – Shannon Holsinger Sep 08 '16 at 05:19
  • I have got the error : "5000 Internal server error " . The error remains in this line : strSQL = "INSERT_BYTE(1,'"& str &"') " Set objExec = Conn.Execute(strSQL). The error is "String literal is too long " . – Christopher Marlowe Sep 08 '16 at 05:25
  • "500 Internal Server Error" means that your ASP.NET code threw an unhandled exception. But we don't know what that exception is. Try debugging the *server* or enabling detailed exception logs. – Avner Shahar-Kashtan Sep 08 '16 at 06:11
  • this link can help you to solve exception 1. http://stackoverflow.com/questions/13945710/error-ora-01704-string-literal-too-long 2. http://stackoverflow.com/questions/8801814/how-to-insert-update-larger-size-of-data-in-the-oracle-tables – priyansh bhaliya Sep 19 '16 at 03:05
  • I am calling stored procedure which is pl/sql code . – Christopher Marlowe Sep 19 '16 at 03:09
  • The error is : "Active Server Pages error 'ASP 0115' Unexpected error /data/2/famenroll2.asp A trappable error (C0000005) occurred in an external object. The script cannot continue running." – Christopher Marlowe Sep 19 '16 at 05:13

0 Answers0