Re: Using SQR to INSERT a row with a LONG Column

[Vivek Pandian <vp25@OIT.GATECH.EDU>]

1. You can not select and insert a long column. I don't know how you did it
in SQL plus. This is one problem.

2. You said you replace some values with user inputs. I guess the user
might enter a number which is stored in SQR in 6 decimal
precision(default). Your table numeric column must not have that many
precision.

3. Do the Select and insert separately if you want to insert long data type.

Good luck

Vivek Pandian

At 04:34 PM 10/5/99 , you wrote:
>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
>