Home » SQL & PL/SQL » SQL & PL/SQL » query giving error of spool space  () 1 Vote
query giving error of spool space [message #241231] Mon, 28 May 2007 23:32 Go to next message
iram_11181
Messages: 3
Registered: May 2007
Junior Member
create volatile table med_call_usage as
(
Select
substr(trim(med.del_no),1,11)del_no,
substr(trim(city_code),1,5)city_code,
substr(trim(city),1,10)city,
substr(trim(med.circle_code),1,5)circle_code,
substr(trim(med.circle),1,10)circle,
substr(trim(med.call_type_code),1,5)call_type_code,
substr(trim(med.call_service_type_code),1,5)call_service_type_code,
substr(trim(med.call_distance_band_code),1,5)call_distance_band_code,
substr(trim(med.outgoing_trunk_id),1,8)outgoing_trunk_id,
substr(trim(med.call_switch_id),1,8)call_switch_id,
substr(trim(mou),1,8)mou,
trim(Pulses)Pulses,
trim(calls)calls,
trim(call_start_date)call_start_date,
substr(trim(cu.CALL_DIRECTION),1,10)CALL_DIRECTION,
cu.CALL_USAGE_TYPE_DESC,
cu.CALL_DISTANCE_BAND_DESC,
cu.TERMINATING_OPERATOR_TYPE,
CASE
WHEN TRIM(med.OUTGOING_OPERATOR) = 'TT_F'
AND TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'FIXED'
THEN 'AIRTEL_FIXED'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'GSM' AND
TRIM(med.OUTGOING_OPERATOR) = 'AIRTEL_GSM' THEN 'AIRTEL_GSM'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'FIXED' THEN 'OTHERS_FIXED'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'GSM' THEN 'OTHERS_GSM'
WHEN TRIM(cu.TERMINATING_OPERATOR_TYPE) = 'WLL' THEN 'WLL'
ELSE 'OTHERS'
END "OPER_NAME"
from mediation_det med
left join b_usersharedb.callUsageCode cuc
on med.call_type_code = cuc.call_type_code
and med.call_service_type_code = cuc.call_service_type_code
and
(( case when trim(med.call_distance_band_code) = 'NF' then '0'
else trim(med.call_distance_band_code) end ) = coalesce(trim(cuc.call_distance_band_code) ,'0')
or ( cuc.call_distance_band_code is null and not exists ( sel 1 from b_usersharedb.callUsageCode cuc1
where cuc1.call_type_code = med.call_type_code
and cuc1.call_service_type_code = med.call_service_type_code
and cuc1.call_distance_band_code = med.call_distance_band_code) ))
inner join bp_viagg.call_usage cu
on cuc.call_code = cu.call_code
) with data on commit preserve rows ;
Re: query giving error of spool space [message #241242 is a reply to message #241231] Mon, 28 May 2007 23:52 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Don't Report your query.
Re: query giving error of spool space [message #241256 is a reply to message #241231] Tue, 29 May 2007 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
reported message
Reported By: iram_11181 On: Tue, 29 May 2007 06:47 In: SQL & PL/SQL SQL Experts query giving error of spool space
Reason as this is a very critical query and i have to run thi son daily basis.But till now i have recieved nio response.


Reporting messages is meant to report abusive messages, spam, etc. NOT to get attention.
Please don't do that.
Re: query giving error of spool space [message #241886 is a reply to message #241231] Wed, 30 May 2007 18:46 Go to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
You need to post the complete exact error message that you are getting and all of the commands that you are running when this happens. It sounds like you are probably running the spool command before your sql statement and have run out of space to spool to. If you run "spool off", then run your sql statement, do you still got the same error? It has probably run out of space to spew out error messages, because "create VOLATILE table ..." is not valid Oracle SQL syntax. Since you are using "on commit preserve rows", I am going to guess that maybe you are looking for "CREATE GLOBAL TEMPORARY TABLE ...".
Previous Topic: query
Next Topic: CASE help
Goto Forum:
  


Current Time: Mon Dec 05 19:00:46 CST 2016

Total time taken to generate the page: 0.25587 seconds