Link to home
Start Free TrialLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

asked on

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

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
      
     )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wasabi3689

ASKER

"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?
Avatar of Sean Stuber
Sean Stuber

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)

Open 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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial