Hi Jeff,
I found, using SQR v.3 and I forget what flavor Oracle (PS 5, 6, 7 depending on which module was in use) that we had to use DataMover to handle Long columns.
Bob
-----Original Message-----
From: Jeff_Way OneWay_Consulting_Group [mailto:JJWAY@AOL.COM]
Sent: Tuesday, October 05, 1999 4:34 PM
To: Multiple recipients of list SQR-USERS
Subject: Using SQR to INSERT a row with a LONG Column
Hello all,
The metrics:
=========
SQR Version: 4.3.2
Oracle Version: 7.3.4
OS: Windows 95/98 (tried on two client machines)
The problem:
=========
I am attempting to INSERT into table 'A' from table 'A' while substituting
KEY values based on user input. The SQL works in SQL-Plus, but causes an
error when executed in SQR.
Error received:
(SQR 5528) ORACLE OEXEC error -1401 in cursor 8:
ORA-01401: inserted value too large for column
The code:
=======
BEGIN-SQL
INSERT
INTO PS_GARN_SPEC
(
EMPLID
,COMPANY
,GARNID
{....fields deleted...}
,PRORATE_OVRD_AMT
,DEDCD
,GARN_REMARKS
)
SELECT
(
'[$VALUE1]'
,A.COMPANY
,A.GARNID
{....fields deleted...}
,A.PRORATE_OVRD_AMT
,A.DEDCD
,A.GARN_REMARKS
FROM PS_GARN_SPEC A
WHERE {...blah-blah-blah...}
END-SQL
- - - - - - - - - - - - - - - - - - - - - - - -
I am very confident that the error is with the GARN_REMARKS column, which in
this case is defined in Oracle with a TYPE of [LONG].
As you can see, I am simply attempting to copy one row of data to the next.
I have tried many permutations...all with various forms of error (or the same
error).
Some of the things I have tried:
- Not specifying the LONG column in the VALUES clause and the SELECT clause
- Using a NULL in the SELECT
- Using ''
- Using ' '
- Using an encoded value
- Using a literal 'X'
I have looked in various 'books' and 'manuals' at my disposal, and can't seem
to find the answer. I have also searched other SQR libraries and cannot find
an example of this anywhere. My guess is that this should be
straightforward...but I fear that I am missing something (other than
significant amounts of sleep and old friends).
If anyone has ever INSERT'ed into a table from another table (or the same
table), and the target table has a LONG column. Please let me know how you
did it.
Thanks in advance.
~Jeff Way
OneWay Consulting Group, Inc.
Phone: 978-764-2928
Email: jjway@aol.com
"The reputation of a thousand years may be determined by the conduct of one
hour." -Japanese Proverb