Re: ORA-6550 PLS-707 parameter 2603 - internal error or unsupported structure - solved

Yong asked:
"Are the SQLs really just "insert into datatable values (..." or bulk  insert in Oracle's sense? Is datatable just a local regular table?  What is "insert buffer is 32K"? You get the error at runtime not  compile time, correct? You're running an anonymous block, not  compiling a stored program unit?"

It's really just insert into ...., and I stop when I see my insert buffer reached 32K.
Basically I have a string of size 32 k, and I add inserts into it until it's full, and
then I send it over to Oracle.

But I found my problem, and I fixed it!
One of the columns in the table is a varchar2(512) and I load binary info into it.
By doing "insert into db values(chr(127)||chr....||chr(65),col2,col3);

This works fine in 99% of cases, but it seems that this version of Oracle does not like too many chr's in a row.
I had to adapt my code to send less chr's in one insert statement.

If a string contains "abcde" - I leave it alone. If the string contain "mze<13,10>abcd<27,0,1,2>" I translate it to "mze"||chr(13)||chr(10)||"abcd"||chr(27)||chr(0)||chr(1)||chr(2) - and then send it over....
This worked fine, until I hit a snag where there is a lot of zeroes or nulls at back of string.
send chr(0)||chr(0)||.....times 20...chr(0)||chr(0) produce the error immediately, so I had to
devise another way to handle lots of nulls in a string.

SO Bottom line - be carefull of lot's of CHR concatenations in a insert statement.

ps. select dump(col1,1,2) from fingers; gives me Typ=1 Len=113: 0,0,0,35,0,46,54,125,.....

Are the any way I can insert stuff in the same method?

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0406241455.718e3a6d_at_posting.google.com... > "Wanda Zoe" <clarioneer_at_hotmail.com> wrote in message news:<cbe4dc$2ct6$1_at_newsreader02.ops.uunet.co.za>...
> > Hi there..
> >
> > I've been doing "bulk insert" into an oracle db for a few years, and
today I
> > hit an error that's got me stumped...
> >
> > My test environment is unix/solaris/oracle 9i, running app from Windows
2000
> > and XP
> > All work fine on it of course!!
> >
> > New database was created with 8.1.7 on Solaris, and when my
> > app do bulk insert on it, I get error :
> > ORA-6550 PLS-707 internal error or unsupported structure [2603]
> > My insert buffer is 32K. I load it with a begin clause, lots of inserts,
and
> > an end at the back, before I submit it.
> > ie:
> > begin
> > insert into datatable values(1,2,3,4);
> > insert into datatable values(2,3,4,5);
> > ....
> > end
> >
> >
> >
> > Currently the app work if the buffer has about 10 inserts in, but if I
let
> > it
> > grow bigger, this pesky error pops up.
> > I would like to get back to using the full 32K buffer.
> >
> > A search on metalink and google did not really help.
> > oerr ora 6550 is not of much help either - except saying :
> > "$ oerr ora 6550
> > 06550, 00000, "line %s, column %s:\n%s"
> > // *Cause: Usually a PL/SQL compilation error.
> > // *Action:"
> > (ps - the oracle message complain about line zero, column zero which is
not
> > of much help :-)
> >
> > I have a feelling the buffer get chopped of? But where do I see what the
max
> > buffer size is, and how do I change it?
> >
> > Any help would be appreciated.
> > Thanks, Wanda

>
> Hi Wanda,
>
> Are the SQLs really just "insert into datatable values (..." or bulk
> insert in Oracle's sense? Is datatable just a local regular table?
> What is "insert buffer is 32K"? You get the error at runtime not
> compile time, correct? You're running an anonymous block, not
> compiling a stored program unit?
>
> You can find error description for PLS-707 in documentation, which
> says
>
> ------- quote -------
> Cause: At run time, this is an internal error. At compile time, it
> indicates one of the following problems:
>
> A call was made to a remote subprogram that has a parameter type or
> default expression not supported at the calling site.
>
> An incomplete upgrade or downgrade was done to a database that has
> stored procedures. Perhaps incorrect versions of system packages such
> as STANDARD.SQL were installed.
>
> A compiler bug was encountered. In such cases, legal PL/SQL syntax
> will fail to compile.
> ...
> ------- quote -------
>
> Yong Huang