Home » SQL & PL/SQL » SQL & PL/SQL » Trigger - bad bind PLS-00049 (10g)
Trigger - bad bind PLS-00049 [message #298291] Tue, 05 February 2008 10:17 Go to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
I have the following trigger:

/* Formatted on 2008/02/05 10:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TRIGGER fscstu.fs_housing_apps
   AFTER INSERT OR UPDATE
   ON fscstu.fs_housing_apps
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   tmppidm       NUMBER;
   tmpappno      NUMBER;
   tmpappyear    NUMBER;
   tmphapscode   VARCHAR2 (20);
   tmpartpcode   VARCHAR2 (20);
   tmpfromdate   VARCHAR2 (20);
   tmptodate     VARCHAR2 (20);
   tmpmrcdcode   VARCHAR2 (20);
   housingcnt    NUMBER;
BEGIN
   tmppidm := :NEW.fs_pidm;
   tmpappno := :NEW.fs_appno;
   tmpappyear := :NEW.fs_app_year;

   SELECT (CASE
              WHEN fs_housing_type = 'Residence Hall'
                 THEN 'IP'
              ELSE '9P'
           END) fs_haps_code,
          (CASE
              WHEN fs_housing_type = 'Residence Hall'
              AND fs_meal_plan IS NOT NULL
                 THEN 'HOME'
              WHEN fs_housing_type = 'College Park Apartments'
              AND fs_meal_plan IS NOT NULL
                 THEN 'APTM'
              WHEN fs_housing_type = 'College Park Apartments'
              AND fs_meal_plan IS NULL
                 THEN 'APTR'
              ELSE ''
           END
          ) fs_artp_code,
          (CASE
              WHEN fs_housing_type = 'Residence Hall'
                 THEN (CASE
                          WHEN fs_housing_date1r IS NOT NULL
                             THEN fs_housing_date1r
                          WHEN fs_housing_date2r IS NOT NULL
                             THEN fs_housing_date2r
                          WHEN fs_housing_date3r IS NOT NULL
                             THEN fs_housing_date3r
                          WHEN fs_housing_date4r IS NOT NULL
                             THEN fs_housing_date4r
                          ELSE ''
                       END
                      )
              ELSE (CASE
                       WHEN fs_housing_date1c IS NOT NULL
                          THEN fs_housing_date1c
                       WHEN fs_housing_date2c IS NOT NULL
                          THEN fs_housing_date2c
                       WHEN fs_housing_date3c IS NOT NULL
                          THEN fs_housing_date3c
                       WHEN fs_housing_date4c IS NOT NULL
                          THEN fs_housing_date4c
                       ELSE ''
                    END
                   )
           END
          ) fs_from_date,
          (CASE
              WHEN fs_housing_type = 'Residence Hall'
                 THEN (CASE
                          WHEN fs_housing_date4r IS NOT NULL
                             THEN fs_housing_date4r
                          WHEN fs_housing_date3r IS NOT NULL
                             THEN fs_housing_date3r
                          WHEN fs_housing_date2r IS NOT NULL
                             THEN fs_housing_date2r
                          WHEN fs_housing_date1r IS NOT NULL
                    THEN SUBSTR (fs_housing_date1r, 0, 4) || 20
                          ELSE ''
                       END
                      )
              ELSE (CASE
                       WHEN fs_housing_date4c IS NOT NULL
                          THEN fs_housing_date4c
                       WHEN fs_housing_date3c IS NOT NULL
                          THEN fs_housing_date3c
                       WHEN fs_housing_date2c IS NOT NULL
                          THEN fs_housing_date2c
                       WHEN fs_housing_date1c IS NOT NULL
                    THEN SUBSTR (fs_housing_date1c, 0, 4) || 20
                       ELSE ''
                    END
                   )
           END
          ) fs_to_date,
          DECODE (fs_meal_plan,
                  '12 Meal Plan', '12TR',
                  '15 Meal Plan', '15TR',
                  '15 (7 day) Meal Plan', '15T7',
                  '19 Meal Plan', '19TR',
                  ''
                 ) fs_mrcd_code
     INTO tmphapscode,
          tmpartpcode,
          tmpfromdate,
          tmptodate,
          tmpmrcdcode
     FROM fs_housing_apps
    WHERE fs_preference = 'PRIMARY'
      AND fs_pidm = tmppidm
      AND fs_app_year = tmpappyear
      AND fs_appno = tmpappno;

   SELECT COUNT (*)
     INTO housingcnt
     FROM slbrmap
    WHERE slbrmap_pidm = tmppidm AND slbrmap_from_term = tmpfromdate;

   IF housingcnt = 0
   THEN
      INSERT INTO slbrmap
                  (slbrmap_pidm, slbrmap_activity_date, slbrmap_artp_code,
                   slbrmap_from_term, slbrmap_to_term,
                   slbrmap_appl_priority, slbrmap_mrcd_code,
                   slbrmap_haps_code, slbrmap_haps_date, slbrmap_add_date,
                   slbrmap_data_origin, slbrmap_user_id
                  )
           VALUES (tmppidm, SYSDATE, tmpartpcode,
                   tmpfromdate, tmptodate,
                   '99999999', tmpmrcdcode,
                   tmphapscode, SYSDATE, SYSDATE,
                   'self-service', 'www_user'
                  );
   ELSE
      UPDATE slbrmap
         SET slbrmap_activity_date = SYSDATE,
             slbrmap_artp_code = tmpartpcode,
             slbrmap_to_term = tmptodate,
             slbrmap_appl_priority = '99999999',
             slbrmap_mrcd_code = tmpmrcdcode,
             slbrmap_haps_code = tmphapscode,
             slbrmap_haps_date = SYSDATE,
             slbrmap_user_id = 'www_user'
       WHERE slbrmap_pidm = tmppidm AND slbrmap_from_term = tmpfromdate;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END fs_housing_apps;
/


I receive a bad bind error message on the following three lines:
   tmppidm := :NEW.fs_pidm;
   tmpappno := :NEW.fs_appno;
   tmpappyear := :NEW.fs_app_year;

I'm not sure why, the fs_housing_apps exists and does contain those columns.

Thanks,
Shane
Re: Trigger - bad bind PLS-00049 [message #298292 is a reply to message #298291] Tue, 05 February 2008 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the result of "desc fscstu.fs_housing_apps"

Regards
Michel
Re: Trigger - bad bind PLS-00049 [message #298297 is a reply to message #298292] Tue, 05 February 2008 10:42 Go to previous messageGo to next message
sjordan
Messages: 19
Registered: October 2007
Junior Member
Alright, by you asking that question, you gave me my answer. When I did the desc fscstu.fs_housing_apps, I received the trigger I was creating. I gave the trigger the same name as the table. I changed the name of the trigger and it executed without any problems.

Thank you,
Shane
Re: Trigger - bad bind PLS-00049 [message #298298 is a reply to message #298297] Tue, 05 February 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for your feedback, I wondered if it was possible but I expected another error message.

Regards
Michel
Re: Trigger - bad bind PLS-00049 [message #298301 is a reply to message #298298] Tue, 05 February 2008 10:55 Go to previous message
sjordan
Messages: 19
Registered: October 2007
Junior Member
I would have expected a different error message also. Since, the table was already created and then I was doing a create or replace trigger with the same name. I would have expected a message informing me that the name is already in use or something of that nature.

Thank you for the help though,
Shane
Previous Topic: Retrieving data from table which has higher precedence
Next Topic: PL/SQL Problem
Goto Forum:
  


Current Time: Wed Dec 07 14:57:13 CST 2016

Total time taken to generate the page: 0.10530 seconds