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

"To do what you're looking for use a system context in place of the variable, or a function in a package that returns the value of a package variable."

How? can you tell more specific?

create a package "your_package"  with get/set routines for each "bind variable" you want to use.

Then use the "get" functions from your package instead of the variables.

To query your view with parameters, your first call the appropriate "set" procedures, then query the view.

CREATE OR REPLACE PACKAGE your_package
IS
    PROCEDURE set_begin_date(p_date IN DATE);
 
    PROCEDURE set_end_date(p_date IN DATE);
 
    FUNCTION get_begin_date
        RETURN DATE;
 
    FUNCTION get_end_date
        RETURN DATE;
END;
 
CREATE OR REPLACE PACKAGE BODY your_package
IS
    g_begin_date   DATE := NULL;
    g_end_date     DATE := NULL;
 
    PROCEDURE set_begin_date(p_date IN DATE)
    IS
    BEGIN
        g_begin_date := p_date;
    END set_begin_date;
 
    PROCEDURE set_end_date(p_date IN DATE)
    IS
    BEGIN
        g_end_date := p_date;
    END set_end_date;
 
    FUNCTION get_begin_date
        RETURN DATE
    IS
    BEGIN
        RETURN g_begin_date;
    END get_begin_date;
 
    FUNCTION get_end_date
        RETURN DATE
    IS
    BEGIN
        RETURN g_end_date;
    END get_end_date;
END;
 
 
 
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 your_package.get_begin_date
                                                      AND your_package.get_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 <= your_package.get_begin_date)

Select allOpen in new window

I am not familiar with package use. Just a couple of times I used it in my past

Do you mean once I create this package, i will have the view and can use the view xx_assets_depreciation_v, right?

To be honestly, this view is for a crystal report use. I want to pass begin-date and end_date into crystal report parameters. Is this you are familiar? and can give me advice?

In that case, I suggest you don't use a view at all, but instead, just put the query into your report.

If you "must" use a view, then you'll need to do something like I suggested above.

And yes, if you run the code I posted above, your view should be created and work just fine.  Inside crystal reports you'll need some kind of "before-step" that calls the set_being_date and set_end_date procedures.  I'm not familiar with crystal though to tell you how to do that .

I agree that not use view may be the best. However, I have two problems I don't know how to handle

For these 2 queries, must select the date range each first first before they union

1. I don't know how to put the entire query into crystal including the union portion. It seems crystal doesn't have SQL edit board
2. I don't know how to pass the Parameter values into crystal parameter values.

Any advice

I suggest you post those questions to the crystal reports zone.  They're outside the scope of this question and outside my knowledge base in any case.  I'm sure there are plenty of crystal reports experts there that can help with those.