Procedure quits when length of parameter passed changes

[RogersJ]

I am puzzled. When I change the value passed to my package.procedure from a single character to two characters the trigger calling the package fails.

I have an after insert trigger calling ins_support('I') - this works, when you change it to ins_support('ID') it doesn't work.

Ins_support(action_in in varchar2) is a procedure declared in a package.

The body of ins_support looks like this:

if action_in = 'ID' then
dbms_output.put_line('input was ID');
elsif action_in = 'I' then
dbms_output.put_line('input was I');
end if;

Anyone have any thoughts ?

Thanks

 

[BJCooperIT]

Is action_in moved to a declared variable? If so, how is it defined?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[RogersJ]

I found my problem.

Immediately prior to the if statement I had an insert into a table that had action declared as a varchar2(1). Two in to one doesn't go.

I didn't have an exception section defined, so I wasn't seeing the true error.

Thanks.

 

[SantaMufasa]

J,

There is a problem beyond the facts that you have posted. Please post your package header and package body definitions.

Here is a proof of concept that what you have told us that you are doing is fine:

SQL> create or replace package Rogers is
  2      procedure Ins_support(action_in in varchar2);
  3  end;
  4  /

Package created.

SQL> create or replace package body Rogers is 
  2    procedure Ins_support(action_in in varchar2) is
  3        begin
  4           if action_in = 'ID' then
  5              dbms_output.put_line('input was ID');
  6           elsif action_in = 'I' then
  7              dbms_output.put_line('input was I');
  8           end if;
  9        end;
 10    end;
 11  /

Package body created.

SQL> create or replace trigger Rogers_trig
  2   after INSERT on s_region
  3     for each row
  4  begin
  5      if :new.name < 'M' then
  6          rogers.ins_support('ID');
  7      else
  8          rogers.ins_support('I');
  9      end if;
 10  end;
 11  /

Trigger created.

SQL> set serveroutput on
SQL> insert into s_region values (6,'Polynesia');
input was I

1 row created.

SQL> insert into s_region values (7,'Arctic');
input was ID

1 row created.

Looking forward to you next post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

Sorry...I was building my post while you were posting yours. (Wouldn't it be nice if Tek-Tips had a "Thread-Updated Since Last Refresh" feature? )

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.