FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


oracle sql Alter table from a triggered package procedure





Marcuzzo
Hi guys,
I know this is the PHP and MySQL section but I wasn't sure if this topic belongs here or in the "other programming" section

I'm basicly trying to alter a table from a trigger

which looks like:

Code:
create or replace
TRIGGER TRIGGER_DDataTable_B
BEFORE INSERT ON DATATABLE
FOR EACH ROW
declare
oms varchar2(250);
BEGIN
    oms := replace ( :new.omschrijving , ' ', '_');
    oms := replace ( oms , '-', '_');
    DDATA_PACK.adddynData(oms);
END;


and the procedure in my package looks like:

Code:
 procedure adddynData ( media String )
   IS 
     SQLRES VARCHAR2(500);
   BEGIN
      SQLRES := 'alter table audit_DATATABLE ADD :test varchar2(250)';
      execute immediate  SQLRES using media;
  END addDynMedia;


whenever I insert data I get this error:

Code:

Error starting at line 1 in command:
insert into DDATA ( mediatype, omschrijving ) values ( 13, 'test')
Error report:
SQL Error: ORA-00904: : invalid ID
ORA-06512: in "MYHOST.DDATA_PACK", line 22
ORA-06512: in "MYHOST.TRIGGER_DDataTable_B", line 14
ORA-04088: Fout bij uitvoering van trigger 'MYHOST.TRIGGER_DDataTable_B'.
00904. 00000 -  "%s: invalid identifier"
*Cause:   
*Action:


i've been looking at the code for an hour now and I've searched forever but I can't get arround it.
I've found that this most people get ORA-00904 because they forgot to put quotes around a literal string in their SQL. but there isn't any ( I think )

the table is there and if I run the alter table directly in oracle it works but for some reason it fails when the procedure is called from the trigger

what i need to do is add a column to add a column to an audit table for each record that is inserted in my datatable.

any help is much appreciated
Related topics
ALTER TABLE mySQL problem.
sql with phpmyadmin
[php/SQL] Help needed
MySQL problem (as usual)
HELP! : Uninstalling phpBB2 mod
Database won't accept this query. SQL syntax wrong?
database backup
mail system......an mysql....
Debug my SQL query please
mysql problem
SQL UPDATING TABLE
SQL error
SQL Connection
Oracle SQL
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.