Home » SQL & PL/SQL » SQL & PL/SQL » declare and set a variable within a trigger? (oracle 10g)
declare and set a variable within a trigger? [message #333333] Fri, 11 July 2008 05:05 Go to next message
Jayla
Messages: 5
Registered: July 2008
Location: Suffolk, UK
Junior Member

Hi

(my first post here so please go easy! )

I'm new to plSQL and I've inherited a project that needs some modification, and I can't quite get it to work

I have a trigger on a table, a before insert. This trigger will generate a new reference number from a sequence if the current is blank or starts with two particular characters, after this it will call a store procedure and pass through those values

This stored procedure basically sends out an email saying what the value previously was, and what it has now been set to

However, I can't get it to compile in SQLDeveloper, can anyone please point out why?

CREATE OR REPLACE TRIGGER "TEST_SCHEMA"."TS_TESTTABLE_T_INSERT" BEFORE INSERT ON TS_TESTTABLE_T
FOR EACH ROW
  declare 
    v_testchar varchar2;
begin
IF :new.ID is null
   THEN select TS_TESTTABLE_T_ID_SEQ.nextval into :new.ID from dual;
END IF;
IF :new.REF is null or instr(upper(:NEW.REF),'XX')!=0
   THEN 
  begin
  v_testchar := :new.REF;
   select 'BA'||ltrim(to_char(TS_TESTTABLE_T_REF_SEQ.nextval,'000000')) into :new.REF from dual;
   mailout(' "emailSender" ','recipient@email.com','ccRecipient@email.com','Subject line' , 'Body of the email',:v_testchar,:new.REF);
   end;
END IF;

end; 
/


Regards

Re: declare and set a variable within a trigger? [message #333336 is a reply to message #333333] Fri, 11 July 2008 05:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What error do you get when you try to compile it?

Do you also get this error when you compile it in SQL*Plus?
Re: declare and set a variable within a trigger? [message #333338 is a reply to message #333336] Fri, 11 July 2008 05:20 Go to previous messageGo to next message
Jayla
Messages: 5
Registered: July 2008
Location: Suffolk, UK
Junior Member

Hi

When I compile the trigger I get...

Warning: execution completed with warning
TRIGGER "TEST_SCHEMA"."TS_TESTTABLE_T_INSERT" Compiled.


Then when I run an insert, being...

INSERT into TS_TESTTABLE_T (EMAIL, OWNER_FST_NM, OWNER_SUR_NM)
VALUES('someEmail@email.com','firstname','surname');


I get this error

Error starting at line 1 in command:
INSERT into TS_TESTTABLE_T (EMAIL, OWNER_FST_NM, OWNER_SUR_NM)  
VALUES('someEmail@email.com','firstname','surname')  
Error at Command Line:1 Column:12
Error report:
SQL Error: ORA-04098: trigger 'TEST_SCHEMA.TS_TESTTABLE_T_INSERT' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.
Re: declare and set a variable within a trigger? [message #333344 is a reply to message #333336] Fri, 11 July 2008 05:49 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

JRowbottom wrote on Fri, 11 July 2008 12:11
What
Do you also get this error when you compile it in SQL*Plus?


Compile it in SQL*Plus and then type show err
Re: declare and set a variable within a trigger? [message #333350 is a reply to message #333344] Fri, 11 July 2008 06:05 Go to previous messageGo to next message
Jayla
Messages: 5
Registered: July 2008
Location: Suffolk, UK
Junior Member

Hi

Tried it in SQL Plus, getting a "PLS-00049: bad bind variable 'V_TESTCHAR'" on this line..



 v_testchar := :new.REF;

Re: declare and set a variable within a trigger? [message #333361 is a reply to message #333350] Fri, 11 July 2008 06:52 Go to previous messageGo to next message
mfinn
Messages: 9
Registered: July 2008
Location: UK
Junior Member
v_testchar varchar2;


Surely you need to declare the size of your VARCHAR2, or is this something new in 10g?

SQL> declare
  2  x varchar2;
  3  begin
  4  x:='test';
  5  end;
  6  /
x varchar2;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00215: String length constraints must be in range (1 .. 32767)

SQL> 
Re: declare and set a variable within a trigger? [message #333363 is a reply to message #333350] Fri, 11 July 2008 06:55 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what happens on my Oracle 10g, using code like yours (posted in the first message):
SQL> create or replace trigger trg_dept
  2    before insert on dept
  3    for each row
  4  declare
  5    v_testchar varchar2;
  6  begin
  7    null;
  8  end;
  9  /

Warning: Trigger created with compilation errors.

SQL> show err
Errors for TRIGGER TRG_DEPT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/14     PLS-00215: String length constraints must be in range (1 ..
         32767)

SQL>

In other words, "v_testchar" can not be VARCHAR2, but requires length (for example, "v_testchar varchar2(20)").
SQL> l5
  5*   v_testchar varchar2;
SQL> c/varchar2/varchar2(20)
  5*   v_testchar varchar2(20);
SQL> /

Trigger created.

SQL>


So, how about posting your real code, possibly as a copy-paste from SQL*Plus session (just like I did)? It might help finding source of your problems.
Re: declare and set a variable within a trigger? [message #333386 is a reply to message #333363] Fri, 11 July 2008 08:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> create or replace procedure my_test
  2  is
  3    v_testvar varchar2;
  4  begin
  5    select :v_testvar into v_testvar from dual;
  6  end;
  7  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE MY_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/10     PLS-00049: bad bind variable 'V_TESTVAR'

This shows that the original poster DID post the actual code. Apparently the error about the invalid bind is given before any syntax error.
The original code has :v_testchar as penultimate parameter in the call to mailout. The colon should be removed there.
Re: declare and set a variable within a trigger? [message #333855 is a reply to message #333386] Mon, 14 July 2008 10:56 Go to previous message
Jayla
Messages: 5
Registered: July 2008
Location: Suffolk, UK
Junior Member

Yes Frank, it was that ":" that was causing the issue.

I did say it would probably be something simple, but this newbie couldn't find it!

Thanks for the help guys, really appreciate it

Kind Regards
Previous Topic: What are the possible alternatives for rule hint
Next Topic: Host command NOT getting executed in Hot backup script.
Goto Forum:
  


Current Time: Sat Dec 03 18:28:57 CST 2016

Total time taken to generate the page: 0.09096 seconds