problem creating trigger through forms 1

[carolinaof]

I am creating a trigger through forms using forms_ddl. The trigger is created correctly, but is invalid, even though there's no error in it. How can I make it valid through forms? The same problem happens if I use exec_sql.

Thanks in advance for help.

 

[sem]

Forms_ddl doesn't raise error, how do you know that the trigger is error-free? Did you tried 'alter trigger compile' command?

 

[carolinaof]

Using 'alter trigger compile' I get the following error:

PLS-00103: Encountered the symbol "" when expecting one of the following: ( ; is with authid deterministic parallel_enable as compress compiled wrapped

But I still say the trigger has no error as it compiles with no problem using PLEdit, for example. Also, I get this error even if I turn my trigger into a procedure with only a null command.

 

[sem]

And I still say that your trigger is INVALID, so it can not be executed. This is the ONLY real criteria.
If you create it by using forms_ddl, check the number of quotes as this is most popular error: the quotes within string literal MUST be doubled.

To get something like

SELECT 'X' FROM DUAL

the statement should be

'SELECT ''X'' FROM DUAL'

Anotther possible error is missing space between concatenated parts:

'select'
||'1'
||'from dual'

produces

select1from dual

Check the REAL string you're executing by assigning it to some text item, COPY it via clipboard to sql*plus (or PLEdit) and try to execute.

 

[carolinaof]

Below is the test procedure as it appears in PLEdit:

procedure AUD_001_U is

BEGIN

null;

END;

As you can see there's none of the errors you said, but thanks for your help anyway.

 

[sem]

What procedure you're talking about? It was trigger initially ...

 

[sem]

I have no experience with PLEdit so do not know WHY doesn't it show errors, but can you provide the result of the following commands?

select trigger_body from user_triggers where trigger_name=<trigger name>

select line, text from user_errors where type='TRIGGER' and name= <trigger name>

 

[carolinaof]

It is a procedure I created as a test to find out what was wrong. The same problem that happened with the trigger happened with that procedure with nothing in it to be the cause of the error. Got it?

 

[carolinaof]

trigger_body:

DECLARE
v_num_evento AUD_EVENTOS.num_evento_AUD%TYPE;
v_login_db V$SESSION.SCHEMANAME%TYPE;
v_osuser V$SESSION.OSUSER%TYPE;
v_maquina V$SESSION.MACHINE%TYPE;
v_programa V$SESSION.PROGRAM%TYPE;
BEGIN

-- busca a sequencia
SELECT S_AUD_EVENTOS.NEXTVAL INTO v_num_evento FROM DUAL;

SELECT UPPER(SCHEMANAME), UPPER(OSUSER), UPPER(MACHINE), UPPER(PROGRAM)
INTO v_login_db, v_osuser, v_maquina, v_programa
FROM V$SESSION
WHERE AUDSID = USERENV('SESSIONID');

-- INSERE O LOG DO EVENTO
INSERT INTO AUD_EVENTOS
(NUM_EVENTO_AUD, NUM_TABELA_AUD,
DATA_EVENTO, TIPO_EVENTO, NOME_LOGIN_DB,
NOME_LOGIN_SO, COD_IDENT_MAQUINA, DCR_PATH_SOFT)
VALUES
(v_num_evento, 1,
sysdate, 'U', v_login_db,
v_osuser, v_maquina, v_programa);

-- COLUNA = CD_PLANTIO_FMT
-- INSERE OS DADOS
INSERT INTO AUD_DADOS
(NUM_EVENTO_AUD, NUM_DADO_AUD, NOME_COLUNA,
VALOR_NEW_COLUNA, VALOR_OLD_COLUNA)
VALUES
(v_num_evento, 1, 'CD_PLANTIO_FMT',
DECODEnew.CD_PLANTIO_FMT,NULL,NULL,''''||:new.CD_PLANTIO_FMT||''''), DECODEold.CD_PLANTIO_FMT,NULL,NULL,''''||ld.CD_PLANTIO_FMT||''''));

-- COLUNA = CD_USO_ACT
-- INSERE OS DADOS
INSERT INTO AUD_DADOS
(NUM_EVENTO_AUD, NUM_DADO_AUD, NOME_COLUNA,
VALOR_NEW_COLUNA, VALOR_OLD_COLUNA)
VALUES
(v_num_evento, 2, 'CD_USO_ACT',
DECODEnew.CD_USO_ACT,NULL,NULL,''''||:new.CD_USO_ACT||''''), DECODEold.CD_USO_ACT,NULL,NULL,''''||ld.CD_USO_ACT||''''));

-- COLUNA = CD_FILIAL
-- INSERE OS DADOS
INSERT INTO AUD_DADOS
(NUM_EVENTO_AUD, NUM_DADO_AUD, NOME_COLUNA,
VALOR_NEW_COLUNA, VALOR_OLD_COLUNA)
VALUES
(v_num_evento, 3, 'CD_FILIAL',
DECODEnew.CD_FILIAL,NULL,NULL,''''||:new.CD_FILIAL||''''), DECODEold.CD_FILIAL,NULL,NULL,''''||ld.CD_FILIAL||''''));

-- COLUNA = CD_PLANT
-- INSERE OS DADOS
INSERT INTO AUD_DADOS
(NUM_EVENTO_AUD, NUM_DADO_AUD, NOME_COLUNA,
VALOR_NEW_COLUNA, VALOR_OLD_COLUNA)
VALUES
(v_num_evento, 4, 'CD_PLANT',
DECODEnew.CD_PLANT,NULL,NULL,''''||:new.CD_PLANT||''''), DECODEold.CD_PLANT,NULL,NULL,''''||ld.CD_PLANT||''''));

END;


user_errors:

LINE: 1
TEXT:
PLS-00103: Encountered the symbol &quot;&quot; when expecting one of the following:

begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current
The symbol &quot;&quot; was ignored.

 

[sem]

I'm sorry, but I really doubt, that the body provided is from user_triggers view.

I may suppose that the real text may contain double quote after DECLARE clause or somewhere else, but I can only guess

 

[carolinaof]

I am sorry but you are wrong. The body I provided is from user_triggers view. I'm sorry you can't come here to see it for yourself.

Thank you for your help anyway.

 

[sem]

It may be invisible symbol. A simple testcase shows how it may be: it uses execute immediate in sql*plus instead of forms_ddl for the sake of simplicity.

create table a (id number)
/

begin
execute immediate
'create or replace trigger a_trg'
||chr(10)
||'before insert on a'
||chr(10)
||'declare'
||chr(13)||chr(13)--invalid characters
||'f number;'
||chr(10)
||' begin'
||chr(10)
||' null;'
||chr(10)
||' end;';
exception
when others then null; /*forms_ddl doesn't generate error, check form_success*/
end;
/
alter trigger a_trg compile
/
show errors


You may see the error similar to your. Moreover, I tried to look at TRIGGER_BODY in PL/SQL Developer and obtained:

declare

f number;
begin
null;
end;

So the problem is in text viewer ingnoring special characters.

The same query in sql*plus produced another output:2 special characters after declare.

 

[carolinaof]

Yes, that's it. I replaced all the 'chr(13)' by 'chr(10)' and it worked fine.

Thank you very much for your help.