-1

I am receiving error when loading SQL Plan into plan table. Can anyone help me? Here are my codes :

APPS@PROD1> @xplan.sql
          ((t2.productgroup_id = 15520)   AND  (t1.productgroup_id = 15520) /*
                               *
ERROR at line 22:
ORA-00907: missing right parenthesis


APPS@PROD1> host cat xplan.sql
    explain plan into

plan_table
for
SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */     'B' || 
          t2.pg_featurevalue_13_id pg_featurevalue_13_id,     'B' || 
          t2.pg_featurevalue_02_id pg_featurevalue_02_id,     'r' || 
          t4.elementrange_id pg_featurevalue_15_id,     'B' || 
          t2.pg_featurevalue_08_id pg_featurevalue_08_id,     'B' || 
          t2.pg_featurevalue_01_id pg_featurevalue_01_id,     'r' || 
          t5.elementrange_id price_eur_id,     'B' || t2.productgroup_id 
          productgroup_id,     'G' || t6.elementgroup_id period_id,     
          SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) 
          salesvalueeur FROM     lu_item_293 t2,     lu_pg_featurevalue_15 t3,    
           lu_elementrange_rel t4,     fact_pd_out_itm_293 t1,     
          lu_elementgroup_rel t6,     lu_elementrange_rel t5 WHERE /* Attribute 
          Joins */       ((t1.item_id = t2.item_id /* Customizing Begin */   AND  
           t1.productgroup_id = t2.productgroup_id) /* Customizing End */   AND  
          (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)   AND  
          (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)   AND  
          (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)   AND  
          (t1.period_id = t6.value_id)       ) /* Attribute Filters */   AND 
          ((t2.productgroup_id = 15520)   AND  (t1.productgroup_id = 15520) /* 
          Push Down Filters */   AND  (t2.pg_featurevalue_01_id IN 
          (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958
          ,27445,297,3891,71,76,89,92,95))   AND  (t2.pg_featurevalue_08_id IN 
          (716,717))   AND  (t2.pg_featurevalue_02_id IN (4165,4166))   AND  
          (t2.pg_featurevalue_13_id = 5424)   AND  (t4.elementrange_id IN 
          (3091,3092))   AND  (t5.elementrange_id IN 
          (8658,8659,8660,8661,8662,8663,8664))   AND  (t6.elementgroup_id = 
          14659)   AND  (t1.period_id IN (20030699999060,20030799999030,2003079999
          9060,20030799999120)) /* Resolved ElementGroup Filters */       ) /* 
          Fact Filters */   AND (t1.project_type_id = '1'       ) GROUP BY     
          t2.pg_featurevalue_13_id,     t2.pg_featurevalue_02_id,     
          t4.elementrange_id,     t2.pg_featurevalue_08_id,     
          t2.pg_featurevalue_01_id,     t5.elementrange_id,     
          t2.productgroup_id,     t6.elementgroup_id;

APPS@PROD1>

There can't be any parenthesis problems because I copy pasted the select statement from Oracle EM. I've heard about changing the sql_txt column using set linesize, because in default mode in command line, the v$sql can't catch all the sql_txt since the sqls statement is too long. But I don't know exactly how to change it, can someone help me out? Thanks a lot!

Lily
  • 35
  • 1
  • 1
  • 6
  • 2
    Could you modify your question to include only the SQL you are trying to execute. Remove any environment console output, or move it into a different block. Also, I hope that you are not actually trying to run a query that you do not understand directly on your production machine as indicated by your output. – gmiley Dec 13 '16 at 12:58
  • No, This is in my own lab environment, not a production database. I am not trying to execute any SQL statement, I am trying to load the optimized SQL execution plan into plan table. – Lily Dec 13 '16 at 13:09
  • You do not need a *production database* to simplify your question, as to make it approachable. Right now all we see is a jumbled mess. Please make the question more concise. – Jerrybibo Dec 13 '16 at 13:11
  • One problem is that there's a line break in the middle of a number in the clause which reads `t1.period_id IN (20030699999060,20030799999030,2003079999 9060,20030799999120)`. You might want to have a look at that. – Bob Jarvis - Слава Україні Dec 13 '16 at 13:12
  • Also, the hints at the beginning of your query will have no effect. Hints must start with `/*+` or `--+` to be effective. Best of luck. – Bob Jarvis - Слава Україні Dec 13 '16 at 13:16
  • Thanks a lot, that's the problem. – Lily Dec 13 '16 at 13:18

1 Answers1

0

After reorganizing your query, I noticed there was a line break and spaces in one of your numeric sequences, try the following reformatted query:

SELECT /* ORDERED INDEX(t1) USE_HASH(t1) */     
     'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
     'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
     'r' || t4.elementrange_id pg_featurevalue_15_id,
     'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
     'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
     'r' || t5.elementrange_id price_eur_id,
     'B' || t2.productgroup_id productgroup_id,
     'G' || t6.elementgroup_id period_id,     
     SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur 
FROM lu_item_293 t2,
     lu_pg_featurevalue_15 t3,    
     lu_elementrange_rel t4,
     fact_pd_out_itm_293 t1,     
     lu_elementgroup_rel t6,
     lu_elementrange_rel t5 
WHERE       
    ((t1.item_id = t2.item_id 
     AND t1.productgroup_id = t2.productgroup_id) 
     AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
     AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
     AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
     AND (t1.period_id = t6.value_id)
    )
    AND 
    ((t2.productgroup_id = 15520)   
     AND  (t1.productgroup_id = 15520)
     AND (t2.pg_featurevalue_01_id IN 
          (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958
          ,27445,297,3891,71,76,89,92,95))   
     AND (t2.pg_featurevalue_08_id IN 
          (716,717))   
     AND (t2.pg_featurevalue_02_id IN (4165,4166))   
     AND (t2.pg_featurevalue_13_id = 5424)   
     AND (t4.elementrange_id IN (3091,3092))   
     AND (t5.elementrange_id IN (8658,8659,8660,8661,8662,8663,8664))   
     AND (t6.elementgroup_id = 14659)   
     AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120))       
    )
    AND (t1.project_type_id = '1') 
    GROUP BY t2.pg_featurevalue_13_id,     
        t2.pg_featurevalue_02_id,     
        t4.elementrange_id,     
        t2.pg_featurevalue_08_id,     
        t2.pg_featurevalue_01_id,     
        t5.elementrange_id,     
        t2.productgroup_id,     
        t6.elementgroup_id;
gmiley
  • 6,531
  • 1
  • 13
  • 25