troubleshooting Question

how to fix " bind variables not allowed for data definition operations"

Avatar of wasabi3689
wasabi3689Flag for United States of America asked on
Oracle Database
8 Comments2 Solutions6764 ViewsLast Modified:
When I just run select... without creating view, it works fine

When I run my query ( create view...) I have the following error
ORA-01027: bind variables not allowed for data definition operations

How to fix it?


DROP VIEW xxcdb.xx_assets_depreciation_v;
 
CREATE OR REPLACE VIEW xxcdb.xx_assets_depreciation_v
 
(
 
          asset_id, asset_number, serial_number,  tag_number, po_number, model_number,
          description, attribute_category_code, segment2, manufacturer_name,
          date_placed_in_service, original_cost, current_cost, 
          adjusted_cost, deprn_amount, acmulated_deprn, net_book_value, deprn_run_date, 
          CALENDAR_PERIOD_OPEN_DATE, CALENDAR_PERIOD_CLOSE_DATE,
          DATE_RETIRED,
          full_name, EMPLOYEE_NUMBER,
          units_assigned, state, location, retirement_pending_flag, 
          book_type_code
 
)
 
As  
 
 
 
 
(
 
SELECT    distinct
          fav.asset_id, 
          fav.asset_number, fav.serial_number,  fav.tag_number, 
          fiv.po_number, fav.model_number,
          fav.description, fav.attribute_category_code, fc.segment2, fav.manufacturer_name,
          fb.date_placed_in_service, fb.original_cost, fb.cost current_cost, 
          fb.adjusted_cost, x.deprn_amount, x.acmulated_deprn, 
          fb.original_cost-x.acmulated_deprn net_book_value, x.deprn_run_date,
          x.CALENDAR_PERIOD_OPEN_DATE, x.CALENDAR_PERIOD_CLOSE_DATE,
          ret.DATE_RETIRED,
          ppx.full_name, ppx.EMPLOYEE_NUMBER,
          fdh.units_assigned, fl.segment2 state,
          fl.segment1|| '.' ||fl.segment2|| '.' ||fl.segment3 || '.' ||fl.segment4 location, 
          fb.retirement_pending_flag,
          x.book_type_code
     FROM apps.fa_categories fc,
          apps.fa_invoice_details_v fiv,
          apps.per_people_x ppx,
          apps.fa_distribution_history fdh,
          apps.fa_books_v fb,
          apps.fa_additions_v fav,
          apps.fa_retirements ret,
          apps.fa_locations fl,
         
          (
           
           select  fd.JE_HEADER_ID,
                   fd.COST, fd.deprn_amount, 
                    deprn_reserve acmulated_deprn,
                    fd.deprn_run_date, 
                    fdp.CALENDAR_PERIOD_OPEN_DATE, fdp.CALENDAR_PERIOD_CLOSE_DATE,
                   fd.asset_id, fd.book_type_code
                   from apps.fa_deprn_detail fd 
                   ,fa.fa_deprn_periods fdp
                   where fd.deprn_amount<>0 
                   and fdp.period_counter = fd.PERIOD_COUNTER
                   and fdp.BOOK_TYPE_CODE=fd.BOOK_TYPE_CODE
                   order by fd.deprn_run_date desc
           
           ) x  
           
          
 
    WHERE fav.asset_category_id = fc.category_id
      AND fb.asset_id(+) = fav.asset_id
      AND fdh.asset_id(+) = fav.asset_id
      AND TRUNC (SYSDATE) >= TRUNC (fdh.date_effective(+))
      AND fdh.date_ineffective IS NULL
      AND fdh.assigned_to = ppx.person_id(+)
      AND fiv.asset_id(+) = fav.asset_id
      and fb.book_type_code = 'WRI CORPORATE' 
      and fav.asset_id = x.asset_id 
      and fl.location_id = fdh.location_id
      --and fb.retirement_pending_flag='NO'
      and ret.asset_id (+) =fav.asset_id
      and x.CALENDAR_PERIOD_CLOSE_DATE between :begin_date and :end_date
      
      --and fb.asset_id ='1025'
      -- and fav.ASSET_NUMBER ='1025'
      --and fb.asset_id between '2600' and '8500'
      --and fb.date_placed_in_service between to_date('1/1/2007', 'mm/dd/yyyy') and to_date('12/31/2007', 'mm/dd/yyyy')
      --and x.deprn_run_date between to_date('1/1/2007', 'mm/dd/yyyy') and to_date('5/31/2007', 'mm/dd/yyyy')
     
 union
 
 SELECT    distinct
          fav.asset_id, 
          fav.asset_number, fav.serial_number,  fav.tag_number, 
          fiv.po_number, fav.model_number,
          fav.description, fav.attribute_category_code, fc.segment2, fav.manufacturer_name,
          fb.date_placed_in_service, fb.original_cost, fb.cost current_cost, 
          fb.adjusted_cost, x.deprn_amount, x.acmulated_deprn, 
          fb.original_cost-x.acmulated_deprn net_book_value, x.deprn_run_date,
          x.CALENDAR_PERIOD_OPEN_DATE, x.CALENDAR_PERIOD_CLOSE_DATE,
          ret.DATE_RETIRED,
          ppx.full_name, ppx.EMPLOYEE_NUMBER,
          fdh.units_assigned, fl.segment2 state,
          fl.segment1|| '.' ||fl.segment2|| '.' ||fl.segment3 || '.' ||fl.segment4 location, 
          fb.retirement_pending_flag,
          x.book_type_code
     FROM apps.fa_categories fc,
          apps.fa_invoice_details_v fiv,
          apps.per_people_x ppx,
          apps.fa_distribution_history fdh,
          apps.fa_books_v fb,
          apps.fa_additions_v fav,
          apps.fa_retirements ret,
          apps.fa_locations fl,
         
          (
           
           select  fd.JE_HEADER_ID,
                   fd.COST, fd.deprn_amount, 
                    deprn_reserve acmulated_deprn,
                    fd.deprn_run_date, 
                    fdp.CALENDAR_PERIOD_OPEN_DATE, fdp.CALENDAR_PERIOD_CLOSE_DATE,
                   fd.asset_id, fd.book_type_code
                   from apps.fa_deprn_detail fd 
                   ,fa.fa_deprn_periods fdp
                   where fd.deprn_amount<>0 
                   and fdp.period_counter = fd.PERIOD_COUNTER
                   and fdp.BOOK_TYPE_CODE=fd.BOOK_TYPE_CODE
                   order by fd.deprn_run_date desc
           
           ) x  
           
          
 
    WHERE fav.asset_category_id = fc.category_id
      AND fb.asset_id(+) = fav.asset_id
      AND fdh.asset_id(+) = fav.asset_id
      AND TRUNC (SYSDATE) >= TRUNC (fdh.date_effective(+))
      AND fdh.date_ineffective IS NULL
      AND fdh.assigned_to = ppx.person_id(+)
      AND fiv.asset_id(+) = fav.asset_id
      and fb.book_type_code = 'WRI CORPORATE' 
      and fav.asset_id = x.asset_id 
      and fl.location_id = fdh.location_id
      --and fb.retirement_pending_flag='NO'
      and ret.asset_id (+) =fav.asset_id
      and ret.DATE_RETIRED is null
      and fb.original_cost-x.acmulated_deprn=0
      and x.CALENDAR_PERIOD_CLOSE_DATE <= :begin_date
      
     )
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros