Oracle 8i Dynamic SQL in a Procedure; Varchar2 Size Limit?

[Ahliana]

Thank you for your time.

I have a union query that is quite large. It is a compilation of 5 queries that are largely the same, with small differences in the SELECT, FROM, and WHERE clauses. I have created variables to hold various pieces. For example, the SELECT for each of the 5 subqueries should be built from a main piece, then the specific fields for that query, and then another common piece.

I have successfully created the union query as straight text, but every time the user wants a change, I currently have to change it in all 5 queries. I have tried to break this into dynamic SQL, and set pieces as variables, and then reassemble the pieces (reusing all the common sections) and create a union query from the variables.

I can successfully compile my shiny little procedure, but when I try to execute, I get:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8

I am trying to output the variables to the output, and it seems like they are too long? I have kept each variable under 8000.

Please ask me questions so that I may provide better details without clogging this with irrelevant junk. I can provide code examples if requested, but as I said, it is rather monstrous.

Pointers to good places to read up on this would also be appreciated. I have a couple of books, but I am not finding the answers.

Thank you very much for your thoughts, musings, pointers, and even rantings about how I should have read X if it will help me learn something useful.

Ahliana
Argue for your limitations and, sure enough, they're yours! - Richard Bach

 

[sfvb]

It looks like the error is on or near line 8, so the first 20 to 30 lines should do.

My guess is that you are trying to assign a string value to a variable that isn't declared large enough to hold the string, or it is trying to assign a number to a variable that wasn't declared large enough to hold the value.

Examples would be:

DECLARE
   myString    varchar2(5);
BEGIN
   SELECT   'abcdefg'
   INTO     myString
   FROM     dual;
END;

on Oracle 9i this error message would be:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

DECLARE
   myNum    number(3);
BEGIN
   SELECT   4558
   INTO     myNum
   FROM     dual;
END;

on Oracle 9i this error message would be:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4

Steve