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

Thanks for posting a "problem solved" message. We all benefit from it.

It's interesting to know it's the long string of "insert ... chr..." that caused the problem. I thought it was due to SQL string length in 8i. But 8i documentation (References - Logical Database Limits) says

SQL Statement Length
 Maximum length of statements
 64K maximum; particular tools may impose lower limits.  

exactly the same as in 9i. So that can't be it. I guess if your insert statement is a 32k regular text string without any CHR in it or a string with many "||" in it, it still works.

Yong Huang

"Wanda Zoe" <clarioneer_at_hotmail.com> wrote in message news:<cbrj8t$eun$1_at_newsreader02.ops.uunet.co.za>...
> 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
Received on Tue Jun 29 2004 - 15:30:55 CDT