Re: "ORA-00984: column not allowed here" Creating table
From: monkeyboy <google_at_vellosa.co.uk>
Date: Mon, 6 Apr 2009 09:52:25 -0700 (PDT)
Message-ID: <73848a45-9375-450e-a167-40ae04971827_at_j12g2000vbl.googlegroups.com>
On Apr 6, 2:35 pm, roelof.streeks..._at_gmail.com wrote:
>
> On Apr 6, 8:57 am, monkeyboy <goo..._at_vellosa.co.uk> wrote:
>
> > On Apr 6, 1:31 pm, johnbhur..._at_sbcglobal.net wrote:
>
> > > On Apr 6, 7:40 am, monkeyboy <goo..._at_vellosa.co.uk> wrote:
>
> > > > Hi All,
>
> > > > I'm sorry if this has been asked before, but my searching has only
> > > > thrown up all kinds of error, which don't quite match what I'm doing.
>
> > > > I am creating a table bar with multiple columns, where foo_id is a
> > > > foreign key reference to the table foo,
>
> > > > CREATE TABLE bar
> > > > (
> > > > foo_id NUMBER (14,0) NOT NULL,
> > > > weird_id VARCHAR2(32) ,
> > > > -- other columns
> > > > FOREIGN KEY(foo_id) REFERENCES foo
> > > > );
>
> > > > My weird id is actually a function of foo_id created with a stored
> > > > function:
>
> > > > FUNCTION generate_weird_id ( p_normal_id IN NUMBER ) RETURN VARCHAR
> > > > IS
> > > > v_weird_id VARCHAR(32);
> > > > BEGIN
> > > > SELECT TO_CHAR(SYSDATE, 'YYDDD') || lpad(p_normal_id, 8, 0)
> > > > FROM DUAL;
> > > > RETURN v_weird_id;
> > > > END generate_weird_id;
>
> > > > So I created the table as:
>
> > > > CREATE TABLE bar
> > > > (
> > > > foo_id NUMBER (14,0) NOT NULL,
> > > > weird_id VARCHAR2(32) DEFAULT generate_weird_id(foo_id),
> > > > -- other columns
> > > > FOREIGN KEY(foo_id) REFERENCES foo
> > > > );
>
> > > > I can understand that I'm not supposed to use the one column as a
> > > > parameter for the input to a function defining the default value for
> > > > another, but how else can I achieve this?
>
> > > > Do I need to have an update trigger?
>
> > > > Thanks
>
> > > Well Tom Kyte says ( and I believe him ) that triggers are evil and
> > > should be avoided where at all possible so the answer to "Do I need to
> > > have ... " is obviously no.
>
> > > You need to have an application that is designed so that parent table
> > > ( the one referenced ) is populated with rows before rows in the child
> > > table(s) are created.
>
> > > In oracle often one has a setup where both sets of id's in the parent/
> > > child tables are generated from sequences ( some people like one
> > > sequence per table some people like shared sequences ) ... with
> > > alternate sets of other keys and indexes as the application design
> > > requires. Many to one relationships use embedded foreign keys
> > > typically in the many side.
>
> > > I guess the first question I should have asked is have you done a
> > > complete ERD model for the application? It kinda sounds like you are
> > > busy coding away perhaps before the design has been validated?
>
> > Thanks for the quick response.
>
> > Agreed triggers are bad. I’ve managed to avoid them until now and
> > don’t really want to start.
>
> > Indeed there is an ERD for the application. The id for the main/parent
> > table ‘foo’ is generated from a sequence, ‘sq_foo_id’.
>
> > This is a system that is currently in production where all database
> > access (inserts, updates as well as selects) is done through stored
> > procedures. My current task is to move away from these and implement
> > some Java OR mapping instead using Hibernate.
>
> > I was under the impression that it was possible to tell Hibernate that
> > the value in a column is generated by calling some database
> > functionality, i.e. sequence. But it appears that by telling Hibernate
> > a column is generated it assumes that this is done in the background
> > and then performs a select on the table once the insert is completed
> > to get the values that have been created for you. Therefore, I thought
> > that if I could get the database to default a value, or have the evil
> > trigger create it then I could work around the OR mapping tool.
>
> > I guess as the ‘weird_id’ can be produced by concatenating a function
> > on the insertion date, with some text and a function on the foreign
> > key. And in the parent table we have the created date/time I could
> > create a view over this table to expose the value I want. Inserts
> > should still work as the view will only be across one table (and I
> > wont need to create any insert triggers…
>
> Not all triggers are bad :) triggers that do data integrity checks are
> ok to code.
> meaning triggers that do not change data!!!!
>
> The problem with triggers is if they start doing things other then
> data checks.
> ei logic like this update record A in table y if state changes on
> record B in table x.
> Then you have an nice opaque system, which leads you to have gray hair
> early in life
> sooner then you which for.
>
> Thanks Roelof
>
> You should also read this blog:
>
> http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declarati...
>
> His book about "Applied Mathematics for Database Professionals" is
> also a very good read.
Date: Mon, 6 Apr 2009 09:52:25 -0700 (PDT)
Message-ID: <73848a45-9375-450e-a167-40ae04971827_at_j12g2000vbl.googlegroups.com>
On Apr 6, 2:35 pm, roelof.streeks..._at_gmail.com wrote:
>
> On Apr 6, 8:57 am, monkeyboy <goo..._at_vellosa.co.uk> wrote:
>
> > On Apr 6, 1:31 pm, johnbhur..._at_sbcglobal.net wrote:
>
> > > On Apr 6, 7:40 am, monkeyboy <goo..._at_vellosa.co.uk> wrote:
>
> > > > Hi All,
>
> > > > I'm sorry if this has been asked before, but my searching has only
> > > > thrown up all kinds of error, which don't quite match what I'm doing.
>
> > > > I am creating a table bar with multiple columns, where foo_id is a
> > > > foreign key reference to the table foo,
>
> > > > CREATE TABLE bar
> > > > (
> > > > foo_id NUMBER (14,0) NOT NULL,
> > > > weird_id VARCHAR2(32) ,
> > > > -- other columns
> > > > FOREIGN KEY(foo_id) REFERENCES foo
> > > > );
>
> > > > My weird id is actually a function of foo_id created with a stored
> > > > function:
>
> > > > FUNCTION generate_weird_id ( p_normal_id IN NUMBER ) RETURN VARCHAR
> > > > IS
> > > > v_weird_id VARCHAR(32);
> > > > BEGIN
> > > > SELECT TO_CHAR(SYSDATE, 'YYDDD') || lpad(p_normal_id, 8, 0)
> > > > FROM DUAL;
> > > > RETURN v_weird_id;
> > > > END generate_weird_id;
>
> > > > So I created the table as:
>
> > > > CREATE TABLE bar
> > > > (
> > > > foo_id NUMBER (14,0) NOT NULL,
> > > > weird_id VARCHAR2(32) DEFAULT generate_weird_id(foo_id),
> > > > -- other columns
> > > > FOREIGN KEY(foo_id) REFERENCES foo
> > > > );
>
> > > > I can understand that I'm not supposed to use the one column as a
> > > > parameter for the input to a function defining the default value for
> > > > another, but how else can I achieve this?
>
> > > > Do I need to have an update trigger?
>
> > > > Thanks
>
> > > Well Tom Kyte says ( and I believe him ) that triggers are evil and
> > > should be avoided where at all possible so the answer to "Do I need to
> > > have ... " is obviously no.
>
> > > You need to have an application that is designed so that parent table
> > > ( the one referenced ) is populated with rows before rows in the child
> > > table(s) are created.
>
> > > In oracle often one has a setup where both sets of id's in the parent/
> > > child tables are generated from sequences ( some people like one
> > > sequence per table some people like shared sequences ) ... with
> > > alternate sets of other keys and indexes as the application design
> > > requires. Many to one relationships use embedded foreign keys
> > > typically in the many side.
>
> > > I guess the first question I should have asked is have you done a
> > > complete ERD model for the application? It kinda sounds like you are
> > > busy coding away perhaps before the design has been validated?
>
> > Thanks for the quick response.
>
> > Agreed triggers are bad. I’ve managed to avoid them until now and
> > don’t really want to start.
>
> > Indeed there is an ERD for the application. The id for the main/parent
> > table ‘foo’ is generated from a sequence, ‘sq_foo_id’.
>
> > This is a system that is currently in production where all database
> > access (inserts, updates as well as selects) is done through stored
> > procedures. My current task is to move away from these and implement
> > some Java OR mapping instead using Hibernate.
>
> > I was under the impression that it was possible to tell Hibernate that
> > the value in a column is generated by calling some database
> > functionality, i.e. sequence. But it appears that by telling Hibernate
> > a column is generated it assumes that this is done in the background
> > and then performs a select on the table once the insert is completed
> > to get the values that have been created for you. Therefore, I thought
> > that if I could get the database to default a value, or have the evil
> > trigger create it then I could work around the OR mapping tool.
>
> > I guess as the ‘weird_id’ can be produced by concatenating a function
> > on the insertion date, with some text and a function on the foreign
> > key. And in the parent table we have the created date/time I could
> > create a view over this table to expose the value I want. Inserts
> > should still work as the view will only be across one table (and I
> > wont need to create any insert triggers…
>
> Not all triggers are bad :) triggers that do data integrity checks are
> ok to code.
> meaning triggers that do not change data!!!!
>
> The problem with triggers is if they start doing things other then
> data checks.
> ei logic like this update record A in table y if state changes on
> record B in table x.
> Then you have an nice opaque system, which leads you to have gray hair
> early in life
> sooner then you which for.
>
> Thanks Roelof
>
> You should also read this blog:
>
> http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declarati...
>
> His book about "Applied Mathematics for Database Professionals" is
> also a very good read.
OK, so what I've done now is to create a trigger like this:
CREATE TRIGGER tr_insert_bar
BEFORE INSERT
ON tb_bar
FOR EACH ROW
BEGIN
:new.weird_id := pk_code_package.sf_weird_id_generator(:new.bar_id);
END tr_insert_bar;
/
which seems to be doing the trick for the moment... Received on Mon Apr 06 2009 - 11:52:25 CDT