varchar size, bytes and chars - general prog ques

[YYYYUU]

I have some pl/sql which is falling over, I think because the size of the string is too long.
The string xml and the number of chars is 5155. If I take off about 1270 chars it works fine. Is there a way I can estimate the byte size. I know the limit for bytes is 32672, but is there an approx for chars.

 

[SantaMufasa]

Y,

Can you please post the code you are using that is causing you trouble?

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:45 (12Jul04) UTC (aka "GMT" and "Zulu"), 08:45 (12Jul04) Mountain Time)

 

[YYYYUU]

I've realised the problem isn't as simple as I thought. I have an oracle package with parameters in and out as follows:

PROCEDURE GetPhrases(pXMLin VARCHAR2,pXMLout OUT VARCHAR2) etc

If I run this in sql window and stipulate the size of the input and output at 30000 then there is no problem.

If I run the package via an sql call from vb, because I cannot stipulate the size of the output, the process errors with the following message.

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

 

[SantaMufasa]

Y,

Yes, In SQL*Plus, you should be able to output the maximum contents of a CLOB (2,147,483,647 bytes) without truncation. I am not, however, familiar with how to command VB to display strings longer than its default maximum.

The fact that you are receiving an Oracle error, "ORA-06502: PL/SQL: numeric or value error..." concerns me. VB is probably not going to be generating that error. Is it correct that your code runs successfully from SQL*Plus, but errors out with the above ORA-06502 error when you use VB? If so, can you confirm somehow that it is a VB issue?

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:58 (13Jul04) UTC (aka "GMT" and "Zulu"), 10:58 (13Jul04) Mountain Time)

 

[YYYYUU]

I really don't think it's a vb problem. I really think it's a oracle problem, as I have had strings longer than this coming into vb where there isn't this problem. I think because I can stipulate the size of the output when in the sql window this is overcoming the problem.

 

[YYYYUU]

Here is the code.

PROCEDURE GetPhrases(pXMLin VARCHAR2,pXMLout OUT VARCHAR2)IS
/*
<availablephrases>
<PHRASES>
<PHRASE id=x userinput=y>
<DESC/>
<APPENDAGES>
<PHRASE id=x>
<DESC/>
</PHRASE>
<EXCEPTIONS>
<PHRASE id=x>
<DESC/>
</PHRASE>
</PHRASE>
</PHRASES>
</availablephrases>
<availablepostscript>
<PHRASES>
<PHRASE id=x userinput=y>
<DESC/>
</PHRASE>
</availablepostscript>';
*/
/* CURSOR curPhrases(lDocID template_phrase_control.tpc_doc_id %TYPE) IS
SELECT tpc_phrase_id,phc_phrase,TRIM(phc_append_user_input) AS phc_append_user_input,phc_phrase_type
FROM template_phrase_control,phrase_control
WHERE tpc_phrase_id=phc_phrase_id AND tpc_doc_id = lDocID AND
phc_phrase_type IN ('PH','PS') AND
NOT EXISTS(SELECT 1 FROM template_phrase_rules
WHERE tpr_doc_id=tpc_doc_id AND tpr_phrase_id=tpc_phrase_id)
ORDER BY tpc_seq_id;
*/
CURSOR curPhrases(lDocID template_phrase_control.tpc_doc_id %TYPE) IS
SELECT tpc_phrase_id,phc_phrase,TRIM(phc_append_user_input) AS phc_append_user_input,phc_phrase_type
FROM template_phrase_control,phrase_control
WHERE tpc_phrase_id=phc_phrase_id AND tpc_doc_id = lDocID AND
phc_phrase_type IN ('PH','PS')
ORDER BY tpc_seq_id;

CURSOR curAppendAndExcept(lDocID template_phrase_control.tpc_doc_id %TYPE,
lPhraseID template_phrase_control.tpc_phrase_id %TYPE) IS
SELECT tpr_append_exception_phrase_id,tpr_type,phc_phrase,phc_append_user_input
FROM phrase_control,template_phrase_rules
WHERE phc_phrase_type='PH' AND phc_phrase_id=tpr_append_exception_phrase_id AND
tpr_phrase_id=lPhraseID AND tpr_doc_id=lDocID;
lDocID shareholder_ltr_text.slt_doc_id%TYPE;
lResult VARCHAR2(20000):='';
lExceptions VARCHAR2(10000):='';
lAppendages VARCHAR2(10000):='';
lPostScript VARCHAR2(5000):='';

BEGIN
GetPhraseParameters(pXMLin,lDocID);

--get phrases for document
FOR recPhrases IN curPhrases(lDocID) LOOP
--check if phrase or postscript
IF recPhrases.phc_phrase_type='PS' THEN
lPostScript:=lPostScript || GetPhraseText(recPhrases.tpc_phrase_id,
recPhrases.phc_append_user_input,
recPhrases.phc_phrase,'POSTSCRIPT') || '</POSTSCRIPT>';
ELSE
lResult:=lResult || GetPhraseText(recPhrases.tpc_phrase_id,
recPhrases.phc_append_user_input,
recPhrases.phc_phrase);

p('PHRASE=' || RECPHRASES.TPC_PHRASE_ID);

--find appendages and exceptions
FOR recAppendAndExcept IN curAppendAndExcept(lDocID,recPhrases.tpc_phrase_id) LOOP
IF recAppendAndExcept.tpr_type=mAPPENDAGE THEN
lAppendages:=lAppendages || GetPhraseText(recAppendAndExcept.tpr_append_exception_phrase_id,
recAppendAndExcept.phc_append_user_input,
recAppendAndExcept.phc_phrase) || '</PHRASE>';
ELSIF recAppendAndExcept.tpr_type=mEXCEPTION THEN
lExceptions:=lExceptions || GetPhraseText(recAppendAndExcept.tpr_append_exception_phrase_id,
recAppendAndExcept.phc_append_user_input,
recAppendAndExcept.phc_phrase) || '</PHRASE>';
END IF;
END LOOP;


--add to main xml structure
lResult:=lResult || '<APPENDAGES>' || lAppendages || '</APPENDAGES>' ||
'<EXCEPTIONS>' || lExceptions || '</EXCEPTIONS></PHRASE>' ;

lAppendages:='';lExceptions:='';
END IF;
END LOOP;



--get postscript for document
pXMLOut:='<availablephrases>' || lResult || '</availablephrases>' ||
'<availablepostscript>' || lPostscript || '</availablepostscript>';
END GetPhrases;