CODEWITHSUNDEEP
xmloracle11gclob
user3807736
user3807736

Reputation:

How to insert CLOB values into oracle table using stored procedure

I am using Oracle 11g. I created a table test_id with id(int), test_xml(clob) columns. And I just created a simple insert procedure, and try to insert some values to the table using the procedure. But it make some errors. I don't know how to fix it. I'm newer one to Oracle. Can anyone help me to fix this.

My Stored procedure is:

CREATE OR REPLACE 
PROCEDURE sp_insert_xml
(
p_id IN INT,
p_xml IN clob
)
AS
BEGIN
INSERT INTO TEST_ID VALUES (p_id, p_xml);
END;

And it throws the error:

[SQL] execute SP_INSERT_XML(1, '<root><CUSTOMERS><CustomerName>Company, Inc.1</CustomerName><GroupNumber>9340</GroupNumber><memberCount>6291</memberCount><OutlierPercentDifference>20</OutlierPercentDifference><ReportingPeriod>Q4 2012 - Q3 2013</ReportingPeriod><PreviousReportingPeriod>Q4 2011 - Q3 2012</PreviousReportingPeriod><ReportingYear>2013</ReportingYear></CUSTOMERS></root>')
[Err] ORA-00900: invalid SQL statement

Thanks in advice

Upvotes: 1

Views: 4581

Answers (2)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

There is no execute command. Try: 

call SP_INSERT(...);

or

begin
   sp_insert(...);
end;
/

Upvotes: 0

Aramillo
Aramillo

Reputation: 3216

I simulated your case and it works for me. Maybe is the way as you are calling your procedure.Try like this:

begin
sp_insert_xml(1, '<root><CUSTOMERS><CustomerName>Company, Inc.1</CustomerName>
<GroupNumber>9340</GroupNumber><memberCount>6291</memberCount>
<OutlierPercentDifference>20</OutlierPercentDifference>
<ReportingPeriod>Q4 2012 - Q3 2013</ReportingPeriod>
<PreviousReportingPeriod>Q4 2011 - Q3 2012</PreviousReportingPeriod>
<ReportingYear>2013</ReportingYear></CUSTOMERS></root>');
end;

Upvotes: 2

Related Questions