This content has been marked as final. Show 2 replies
Wheneve you have a problem, prlease post a complete test script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for any tables used, abnd the results you want it to produce.
SeshuGiri wrote:Are you really getting the error when you concatenate, or when you try to execute the resulting string?
I am getting numeric or value error when concatenating sql script..
Here is the sample, I have in the stored proc.. firstpart:_ works fine if I comment the secondpart:_. (I modified the query because I am posting in the public forums)Is that big enough? It looks like the string is right around 1000 characters; depending on your character set, that could be more than 2000 bytes. VARCHAR2s in PL/SQL can hold up to 32767 bytes.
V_SAMPLEQUERY := '
AB.ABCDID AS ABCDID,
CD.MEMBERID AS MEMBERID,
CD.FNAME AS FNAME,
CD.LNAME AS LNAME,
CD.ACCOUNT AS ACCOUNT,
EF.ZIP AS ZIP,
AB.INSERTTIME AS INSERTTIME,
AB.INSERTBY AS INSERTBY,
ES.NAME AS STATUS,
MYFUNCTION(E.ABCDID) AS AMOUNT,
PA.NAME AS SOUSENAME,
R.CODE AS BUDGETCODE,
WX.ACCOUNTID as ACCOUNTID,
AB.SOMETYPE AS SOMETYPE,
ED.TYPE AS TYPE,
AB.NOTES AS NOTES,
GH.AREACODE || '''' || GH.PHONE_NUMBER AS PHONENUMBER,
FROM PEOPLES AB,
WHERE AT.NAME = ''SAVINGS''
andCD.MEMBERID = PH.MEMBERID
and AT.ADDRESSTYPEID =EF.ADDRESSTYPEID
andAB.STATUS = ES.STATUS(+)Did something get cut off here?
andAB.ABCDID = ED.ABCDID
andAB.ABCDID = EP.ABCDID(+)
and EP.MEBER_ID = PA.MEBER_ID(+)
andAB.BID = R.BID(+)
andAB.SAMPLEID = PR.SAMPLEID(+)
andAB.ABCDID = BSG.ABCDID(+)
andAB.INFOCALLID = C.INFOCALLID(+)
and YZ.SOMETYPEID = ''10000''
ANDAB.INSERTTIME > SYSDATE - 35
ANDAB.INSERTTIME < ''
AND WX.NAME IN (''ABCD'',It looks like you're constructing a string for EXECUTE IMMEDIATE, but why not just use a static query?
AND MN.NAME = ''FINISHED'' '
secondpart:_ ***ERROR PART***USER is the name of a built-in function, which means it's not a good name for your own column. Try user_name instead.
V_SAMPLEQUERY := V_SAMPLEQUERY || ' ORDER BY AB.USER'; -- Here I am getting error saving ORA-06502 - numeric or value error