Home » SQL & PL/SQL » SQL & PL/SQL » Stored Procedure using SQL insert for clob data > 4k
Stored Procedure using SQL insert for clob data > 4k [message #191714] Thu, 07 September 2006 19:53 Go to next message
ghanson
Messages: 4
Registered: September 2006
Junior Member
Hello folks,

I have googled this to my heart's content without solving this problem. I am an expert in OOP, OOA and a "5" when it comes to SQL DBA stuff. My Problem:

I am trying to create a Stored Procedure on an Oracle Database, that basically just inserts into another table a bunch of parameters.

The problem, is there are Clob Parameters specified (needed because the data is clob-like and can be > 4K). The "Insert into table…etc." works for Clob objects less than 4K in size. But gives "literal to long" for Clob objects greater than 4K.

I have spent all day on this, and could not find a solution. I have googled all over the Internet in search, as well as went to the bookstore to find a book that talks about this with no Luck.

I am a neophyte when it comes to SQL/Oracle, pardon my inquiry if it insults your intelligence, however in your answer if you could specify the Stored Procedure, and a good book on Oracle, you have my blessings.

best,
Greg
Re: Stored Procedure using SQL insert for clob data > 4k [message #191771 is a reply to message #191714] Fri, 08 September 2006 01:56 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Have You looked into the docs on clobs?
Have You inserted an empty clob first?

Show us Your code! (trimmed down to something easily readible)

Br
Kim
Re: Stored Procedure using SQL insert for clob data > 4k [message #191914 is a reply to message #191714] Fri, 08 September 2006 10:56 Go to previous messageGo to next message
ghanson
Messages: 4
Registered: September 2006
Junior Member
Ok, here is my latest attempt at this:

create or replace PROCEDURE INSERT_VENDOR_POOL_AC
(m_reportid IN CHAR,
m_guid IN VARCHAR2,
m_version IN CHAR,
m_officecode IN CHAR,
m_clientcode IN CHAR,
m_vendorcode IN NUMBER,
m_productcode IN CHAR,
m_optionalproductcode IN VARCHAR2,
m_accesscode IN CHAR,
m_passthrough IN CHAR,
m_encode IN CHAR,
m_myrequesttimestamp IN TIMESTAMP,
m_forcepull IN CHAR,
m_myvendorrequest IN CLOB,
m_status IN CHAR,
m_istestcase IN CHAR,
m_vendorrequesttimestamp IN TIMESTAMP,
m_vendorresponsetimestamp IN TIMESTAMP,
m_vendorrawrequest IN CLOB,
m_vendorrawresponse IN CLOB,
m_normalizedresponse IN CLOB,
m_message IN VARCHAR2,
m_servicename IN VARCHAR2,
m_host IN VARCHAR2,
m_datecreated IN DATE) is

myclob clob;

begin

dbms_output.put_line('Stored Procedure: Insert_Vendor_Pool_AC');
dbms_output.put_line('Today is ' || TO_CHAR(SYSDATE) || '.');

insert into VENDOR_POOL_AC (
REPORTID,
GUID,
VERSION,
OFFICECODE,
CLIENTCODE,
VENDORCODE,
PRODUCTCODE,
OPTIONALPRODUCTCODE,
ACCESSCODE,
PASSTHROUGH,
ENCODE,
MYREQUESTTIMESTAMP,
FORCEPULL,
MYVENDORREQUEST,
STATUS,
ISTESTCASE,
VENDORREQUESTTIMESTAMP,
VENDORRESPONSETIMESTAMP,
VENDORRAWREQUEST,
VENDORRAWRESPONSE,
NORMALIZEDRESPONSE,
MESSAGE,
SERVICENAME,
HOST,
DATECREATED )
VALUES(
m_reportid,
m_guid,
m_version,
m_officecode,
m_clientcode,
m_vendorcode,
m_productcode,
m_optionalproductcode,
m_accesscode,
m_passthrough,
m_encode,
m_myrequesttimestamp,
m_forcepull,
EMPTY_CLOB(), --m_myvendorrequest,
m_status,
m_istestcase,
m_vendorrequesttimestamp,
m_vendorresponsetimestamp,
EMPTY_CLOB(), --m_vendorrawrequest,
EMPTY_CLOB(), --m_vendorrawresponse,
EMPTY_CLOB(), --m_normalizedresponse,
m_message,
m_servicename,
m_host,
m_datecreated);

SELECT MYVENDORREQUEST INTO myclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;
UPDATE VENDOR_POOL_AC SET MYVENDORREQUEST = m_myvendorrequest WHERE REPORTID = m_reportid and GUID = m_guid;

SELECT VENDORRAWREQUEST INTO myclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;
UPDATE VENDOR_POOL_AC SET VENDORRAWREQUEST = m_vendorrawrequest WHERE REPORTID = m_reportid and GUID = m_guid;

SELECT VENDORRAWRESPONSE INTO myclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;
UPDATE VENDOR_POOL_AC SET VENDORRAWRESPONSE = m_vendorrawresponse WHERE REPORTID = m_reportid and GUID = m_guid;

SELECT NORMALIZEDRESPONSE INTO myclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;
UPDATE VENDOR_POOL_AC SET NORMALIZEDRESPONSE = m_normalizedresponse WHERE REPORTID = m_reportid and GUID = m_guid;

COMMIT;
END INSERT_VENDOR_POOL_AC;
Re: Stored Procedure using SQL insert for clob data > 4k [message #191940 is a reply to message #191914] Fri, 08 September 2006 15:50 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hmmm..... lots of code not needed for a test case....
Try to trim it down to 5-10 lines.

Why do You select into myclob, when You do not use it?

Please give the exact error.
It should also display the line where it fails.

The 4 update statements should be only one:

UPDATE VENDOR_POOL_AC
SET MYVENDORREQUEST = m_myvendorrequest
, SET VENDORRAWREQUEST = m_vendorrawrequest
, SET VENDORRAWRESPONSE = m_vendorrawresponse
, SET NORMALIZEDRESPONSE = m_normalizedresponse
WHERE REPORTID = m_reportid and GUID = m_guid;

Br
Kim Anthonisen
Re: Stored Procedure using SQL insert for clob data > 4k [message #191945 is a reply to message #191914] Fri, 08 September 2006 17:01 Go to previous messageGo to next message
ghanson
Messages: 4
Registered: September 2006
Junior Member
Why "SELECT"? I believed the syntax below was necessary in order put > 4k into myvendorrequestclob for example.

SELECT MYVENDORREQUEST INTO myvendorrequestclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;

UPDATE VENDOR_POOL_AC SET MYVENDORREQUEST = myvendorrequestclob WHERE REPORTID = m_reportid and GUID = m_guid;

The exact error is
"An error was encountered performing the requested operation"
"ORA-01704: string literal too long
Vendor code 1704

When I reduce the size of the clobs to insert to < 4k, the sql insert completes, but none of the clobs have data in them.
thanks.
Re: Stored Procedure using SQL insert for clob data > 4k [message #191977 is a reply to message #191945] Sat, 09 September 2006 08:43 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
I think You misunderstand the concept of selecting into.
When You select into, You retrieve a value FROM the database INTO a variable IN YOUR CODE.
And my point is, that You do not use that variable anywhere.

And the 4 update statements should be 1, as the where clause are the same.
Re: Stored Procedure using SQL insert for clob data > 4k [message #192066 is a reply to message #191945] Sun, 10 September 2006 18:38 Go to previous message
ghanson
Messages: 4
Registered: September 2006
Junior Member

Yes, I know I was trying to select into the wrong variable, it was a typo before that I did not catch. But did you see my posting before your reponse?

Ok here it is repeated and using less words,

<quote>
SELECT MYVENDORREQUEST INTO myvendorrequestclob FROM BMARTIN.VENDOR_POOL_AC WHERE REPORTID = m_reportid and GUID = m_guid FOR UPDATE;

UPDATE VENDOR_POOL_AC SET MYVENDORREQUEST = myvendorrequestclob WHERE REPORTID = m_reportid and GUID = m_guid;

The exact error is
"An error was encountered performing the requested operation"
"ORA-01704: string literal too long
Vendor code 1704
<endquote>

Now, notice the last statement please. In other words, the update is working for literals < 4k. But it does not work for literals > 4k. The error is as above and in my previous posting as well.

thanks.
Previous Topic: Need help in updating blob column
Next Topic: HOW TO DISPLAY THE SUMMARY LINE ONLY
Goto Forum:
  


Current Time: Sat Dec 03 14:14:22 CST 2016

Total time taken to generate the page: 0.09108 seconds