How to insert CLOB values into oracle table using stored procedure

Question

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

Aramillo · Accepted Answer · 2014-12-19 13:38:54Z

2

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;

answered Dec 19, 2014 at 13:38

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3807736 Over a year ago

Yes, It's working. I think you tell me to call the PL/SQL block. Thanks for your advice Aramillo

Marcin Wroblewski · Accepted Answer · 2014-12-19 14:55:16Z

0

There is no execute command. Try:

call SP_INSERT(...);

or

begin
   sp_insert(...);
end;
/

answered Dec 19, 2014 at 14:55

Marcin Wroblewski

3,57020 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to insert CLOB values into oracle table using stored procedure

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related