limit on the number of characters you can have in a SQL query

[Scunningham99]

Is there a limit on the number of characters you can have in a SQL query against oracle? This query will not run against anything from a SQL plus window.

SELECT AL1.ITEM_MASTER_ID, AL1.TRANS_TYPE_CODE, AL1.REGISTER_SHIFT_ID, AL1.CALL_DURATION, AL1.HOTEL_DATE, AL1.EXP_DATE, AL1.FOLIO_ID, AL1.PAYMENT_TYPE, AL1.DOCUMENT_REF, AL1.CREATED_BY, AL1.SALES_ITEM_ID, AL1.PROPERTY_ID, AL1.TRNSFER_FOLIO_ID, AL2.BASE_AMT_PAID, AL2.SALES_DETAIL_DESC, AL2.SALES_DETAIL_CODE, AL2.CHG_AMT, AL1.FOLIO_STATUS_CODE, AL3.RESV_NUM, AL4.ARRIVAL_DATE, AL4.DEPARTURE_DATE, AL4.RESV_TYPE_CODE, AL5.RESV_TYPE_DESC, AL6.PROPERTY_DESC, AL2.ADV_DEP_APPLY_DATE, AL2.DIST_AMT_APPLY_DATE FROM PREMIER.P_SALES_ITEM AL1, PREMIER.P_SALES_ITEM_DETAIL AL2, PREMIER.P_RESV_GUEST AL3, PREMIER.P_RESERVATION AL4, PREMIER.P_RESV_TYPE AL5, PREMIER.P_PM_PROPERTY AL6
WHERE (AL2.SALES_ITEM_ID=AL1.SALES_ITEM_ID AND AL1.FOLIO_ID=AL3.FOLIO_ID AND AL3.RESV_NUM=AL4.RESV_NUM AND AL4.RESV_TYPE_CODE=AL5.RESV_TYPE_CODE AND AL1.PROPERTY_ID=AL6.PROPERTY_ID) AND (AL1.HOTEL_DATE>='01-10-2005' AND AL1.HOTEL_DATE<'01-01-2050' AND AL1.FOLIO_STATUS_CODE IN ('NEW', 'PNA', 'PST') AND AL1.PROPERTY_ID NOT IN ('BCG', 'EVM', 'GSR', 'PFC', 'PHR') AND AL1.TRANS_TYPE_CODE NOT IN ('DEP', 'DPA'))




Sy UK

 

[Scunningham99]

i have sorted this... lease do not post/ thanks!

Sy UK

 

[SantaMufasa]

Sy,

If you found a specific answer that relates to your original question, please post the answer, as many would benefit from your findings. Otherwise, may I suggest that it would probably be appropriate to click the "Inappropriate" button since without your findings, your thread doesn't provide on-going value added.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Scunningham99]

HI Santa

Yer I totally agree there is nothing worse finding the same post to your problem with no solution.

Item Limit
filename length system dependent
username length 30 bytes
user variable name length 30 bytes
user variable value length 240 characters
command-line length 2500 characters
length of a LONG value entered through SQL*Plus LINESIZE value
LINESIZE system dependent
LONGCHUNKSIZE value system dependent
output line size system dependent
line size after variable
substitution 3,000 characters (internal only)
number of characters in a COPMUTE command label 500 characters
number of lines per SQL command 500 (assuming 80 characters per line)
maximum PAGESIZE 50,000 lines
total row width 60,000 characters for VMS; otherwise, 32,767 characters
maximum ARRAYSIZE 5000 rows
maximum number of nested command files 20 for VMS, CMS, Unix; otherwise, 5
maximum page number 99,999
maximum PL/SQL error message size 2K
maximum ACCEPT
character string length 240 Bytes
maximum number of DEFINE variables 2048



appologies
rgds


Sy UK