Home » SQL & PL/SQL » SQL & PL/SQL » VARCHAR2 problem (Oracle 10g)
VARCHAR2 problem [message #423983] Tue, 29 September 2009 15:21 Go to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Hi,

I've been working on a stored proc, and there's one problem that I can't get my head around.

It's supposed to be very simple. I'm building a dynamic UPDATE statement by going through the rows in a table. Each row has a column whose value is basically the name of a column in another table.

The varchar2 variable dynsql is basically the problem. When I debug, everything goes fine initially but after about 40 rows, the variable becomes UNDECLARED. At first I thought I was running out of space for the varchar2 type, but I really don't think so. I played a bit with lengthb(dynsql), and it seems to bomb out at different sizes when I add some random characters (about 10kb in this case).

There's nothing wrong with the UPDATE statement. I used to build it with VB6 and submit it through ADO. In fact I have an identical stored proc running with a shorter UPDATE statement.

Here's the script (simplified version)


CREATE OR REPLACE PROCEDURE Spcalcprob_Type (RACECODE NUMBER DEFAULT NULL)
IS

BEGIN
DECLARE
RACECODE NUMBER;
dynsql VARCHAR2(32767) := '';
probsql VARCHAR2(32767) := '';
CURSOR cursor1 IS SELECT coefid, coef_sql, COUNT(*) OVER () tot_rows FROM US_MODEL_COEFS WHERE LIVE = 1 AND coefid > 0 AND coefgroup = PROBTYPE AND modelgroup = 1 ORDER BY COEFID;

BEGIN


FOR rec IN cursor1 LOOP
dynsql := CONCAT(dynsql, ' (SELECT coef_value FROM US_MODEL_COEF_EST MI WHERE MI.MODEL = MM.MODEL and coefgroup = ' || PROBTYPE || ' AND COEFID = ' || rec.coefid || ') * (' || rec.coef_sql || ')');
IF cursor1%rowcount < rec.tot_rows THEN
dynsql := CONCAT(dynsql, ' + ');
END IF;
END LOOP;
probsql := 'UPDATE US_PROBS P SET RP_GMR = (SELECT EXP(' || dynsql || ')/(SELECT SUM(EXP(' || dynsql || '))';

...

DBMS_OUTPUT.PUT_LINE(probsql);
EXECUTE IMMEDIATE probsql;

END;
END;
/

[Updated on: Tue, 29 September 2009 15:37]

Report message to a moderator

Re: VARCHAR2 problem [message #423984 is a reply to message #423983] Tue, 29 September 2009 15:37 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Here's the script (simplified version)
Here's a response (simplified version)

Without DDL for tables & DML for test data and complete code,
no answer is possible.

Re: VARCHAR2 problem [message #423987 is a reply to message #423984] Tue, 29 September 2009 16:04 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
that's impossible to provide

there's about 10 million rows!
Re: VARCHAR2 problem [message #423988 is a reply to message #423987] Tue, 29 September 2009 16:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rolifantje wrote on Tue, 29 September 2009 22:04
that's impossible to provide

there's about 10 million rows!

Come on! You don't seriously think that BlackSwan is asking for all the data do you? You have to provide a working test case froom which the issue can be replicated.
Re: VARCHAR2 problem [message #423990 is a reply to message #423988] Tue, 29 September 2009 16:17 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
I may have a look tomorrow. It's not straightforward. The total update statement is about 20 pages long I reckon.

I think it may have something to do with the varchar2 type anyway. Depending on your characterset, the max size can be about 10kb.



Re: VARCHAR2 problem [message #423991 is a reply to message #423990] Tue, 29 September 2009 16:27 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
The context of the UPDATE is as follows. I'm calculating the probabilities according to a multinomial logit model.

This is

Y = exp(a*X1 + b*X2 + ...)/sum(exp(a*X1 + b*X2 + ...))

Or

UPDATE Y =
(
exp((SELECT a1 FROM weights) * X1 + ...)/sum(exp(...))
)

There are about 80 X's. The reason why I'm doing it this way and not in a loop is because the loop takes much much longer.

[Updated on: Tue, 29 September 2009 16:27]

Report message to a moderator

Re: VARCHAR2 problem [message #423993 is a reply to message #423990] Tue, 29 September 2009 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>I think it may have something to do with the varchar2 type anyway. Depending on your characterset, the max size can be about 10kb.


PL/SQL VARCHAR2 can exceed 32,000

I would also like to point out that you have yet to actually provide the whole error code & message!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: VARCHAR2 problem [message #424009 is a reply to message #423983] Tue, 29 September 2009 21:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am taking a stab in the dark.

1) something in your data that you are using to build a query is a reserved word?

2) there are uninterpretable characters in the data you are using the build the sql statement?

3) there is an error in the data used to build you dynamic statements and it is causing a syntax error?

Ditto to BlackSwan, where is the error message.

Kevin
Re: VARCHAR2 problem [message #424061 is a reply to message #423983] Wed, 30 September 2009 02:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We really do need to see the actual error message.
UNDECLARED, which is what you say the variable dynsql becomes, isn't a word with a defined meaning for Oracle. Do you mean the variable gets set to null?

Are you using a multibyte character set?

@BlackSwan - I think, given that the OPs code has his DynSql variable defined as a Varchar2(32767), they've probably figured out the maximum size for a varchar2 in Pl/sql
Re: VARCHAR2 problem [message #424316 is a reply to message #424061] Thu, 01 October 2009 09:37 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
My characterset: WE8MSWIN1252

I have added a .png file to illustrate the problem.

This is the PL/SQL code block that generates the problem:

FOR rec IN cursor1 LOOP
counter := counter + 1;
varsize := LENGTHB(dynsql);
dynsql := dynsql || ' (SELECT coef_value FROM US_MODEL_COEF_EST MI WHERE MI.MODEL = MM.MODEL and coefgroup = ' || PROBTYPE || ' AND COEFID = ' || rec.coefid || ') * (' || rec.coef_sql || ')';

END LOOP;



To get to the error:

a) I am using TOAD to debug
b) the cursor will go through 54 rows
c) everything is fine iterations 1...8
d) at iteration 9 dynsql becomes 'UNDECLARED IDENTIFIER"

It doesn't have anything to do with the row in the cursor. If I use different data, the same happens.

[Updated on: Thu, 01 October 2009 09:39]

Report message to a moderator

Re: VARCHAR2 problem [message #424325 is a reply to message #423983] Thu, 01 October 2009 09:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
ah... and now the first culprit rears its ugly head. For all the value Toad brings to the table, it can sometimes be the problem for issues like these.

This may be a Toad issue. Run your code in SQLPLUS not Toad. Then show us the error.

Also, when you paste code, format that code.

Kevin
Re: VARCHAR2 problem [message #424330 is a reply to message #424325] Thu, 01 October 2009 10:23 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Here's the output from SQL*PLUS

ERROR:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 1

[Updated on: Thu, 01 October 2009 10:24]

Report message to a moderator

Re: VARCHAR2 problem [message #424333 is a reply to message #424330] Thu, 01 October 2009 10:33 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Hmm, that error could actually be related to the DBMS_OUTPUT.PUT_LINE that is in there. Will do some more testing and post back asap.
Re: VARCHAR2 problem [message #424334 is a reply to message #423983] Thu, 01 October 2009 10:35 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is that the full error stack? Seems odd it'd report at line 1.

Are there any exception handlers in the procedure?

Bottom line you've got some form of size issue. I think you're going to have to debug further before we can help.

Use sqlplus and add some more dbms_outputs. Suggest outputing the dynamic sql string and a loop counter - see if it falls over at the same point it does in toad.
Re: VARCHAR2 problem [message #424335 is a reply to message #424333] Thu, 01 October 2009 10:42 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
rolifantje wrote on Thu, 01 October 2009 16:33
Hmm, that error could actually be related to the DBMS_OUTPUT.PUT_LINE that is in there. Will do some more testing and post back asap.


If you're blowing out the dbms_output stack - which is possible - you might be better served by knocking up a quick debugging procedure.
Create a table with an id and a text field. Write a little procedure that inserts into the table using a sequence for the id. Make it an autonomus_transaction. Call that instead of dbms_output.
You might have to add some code to strip your strings into chunks that'll fit into a varchar column (since 4000 is the max allowed in tables and you've 32767 ones) or use a clob instead.
Re: VARCHAR2 problem [message #424336 is a reply to message #423983] Thu, 01 October 2009 10:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the fastest way to see if DBMS_OUTPUT is the problem is just to remove DBMS_OUTPUT.

Kevin
Re: VARCHAR2 problem [message #424337 is a reply to message #423983] Thu, 01 October 2009 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 12410
Registered: September 2008
Location: Rainy Manchester
Senior Member
Having said that I'm fairly sure the buffer size of dbms_output is unlimited in 10g.
Re: VARCHAR2 problem [message #424339 is a reply to message #424333] Thu, 01 October 2009 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
rolifantje wrote on Thu, 01 October 2009 08:33
Hmm, that error could actually be related to the DBMS_OUTPUT.PUT_LINE that is in there. Will do some more testing and post back asap.


Instead of trying to display the content of the string, you could output the LENGTH() of the string instead.
Re: VARCHAR2 problem [message #424341 is a reply to message #424337] Thu, 01 October 2009 11:04 Go to previous message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
I'm running 10.2 - I also thought it was unlimited. Maybe I still have an old version of SQL*PLUS.

ANYWAY.

I found the problem. Indeed, it had to do with TOAD. There was a small problem with the update statement, which didn't always occur (to do with the coef_sql field, it sometimes contains an alias which doesn't work in the UPDATE).

However, because TOAD said it was an UNDECLARED IDENTIFIER, I had wrongly assumed that there was a problem with the actual building of the update string, rather than the contents of the update string. Talk about barking up the wrong tree.

Thank you for all your help, I would not have fixed this issue anytime soon without it.
Previous Topic: How to Calculate the number of periods for the given Start date and End date
Next Topic: How to join 2 update statements
Goto Forum:
  


Current Time: Wed Dec 07 04:39:49 CST 2016

Total time taken to generate the page: 0.15481 seconds