This is not a DBI/DBD::Oracle restriction but an Oracle one. If you try the same query from SQL*Plus you will get the same error.
If your Oracle version is >= 8.1, you could change the data type of XML_TEXT from LONG to CLOB, in which case your query will work. Even using a stored procedure is not completely straight forward (unless the contents of XML_TEXT is guaranteed to be < 32K in length) as the max length of a VARCHAR2 variable is 32K. So, you would need to fetch chunk by chunk using dbms_sql. The first thing you need to do is get the PL/SQL procedure working from (say) SQL*Plus. Once you are at this point, incorporating it into a Perl script is very simple and described very well in the perldoc. HTH, Steve -----Original Message----- From: NRK [mailto:[EMAIL PROTECTED] Sent: Monday, 10 January 2005 10:13 AM To: [email protected] Subject: How to run Insert...SELECT SQL for LONG Datatype in Perl Hello Perl Gurus, I have a perl script that does the following kind of a query: insert into MY_TABLE (LABEL_ID, MY_NAME, MY_ADDRESS, XML_TEXT) select 2, MY_NAME, MY_ADDRESS, XML_TEXT from MY_TABLE where label_id=1; The desciption of the table is as follows: LABEL_ID NOT NULL NUMBER(38) MY_NAME NOT NULL VARCHAR(32) MY_ADDRESS VARCHAR(252) XML_TEXT LONG When I run this query, it returns an error saying ORA-0997 Illegal use of LONG datatype Is there a way I can run a query that does the same? Please note that LABEL_ID is something that I pass in the Perl script, so it is to some extent dynamic query (I have prepare statement that has something like $label_id). The only thing I can think of is writing a PL-SQL/Stored Procedure for this. However, again, I don't know how the PL-SQL will look like for this kind of query where I can pass in the label_id as a parameter. You help is highly appreciated. Thanks. NRK
