Re: "ORA-00984: column not allowed here" Creating table
From: <roelof.streekstra_at_gmail.com>
Date: Mon, 6 Apr 2009 06:35:03 -0700 (PDT)
Message-ID: <490749aa-90dd-4eb9-84cf-f40f177edaa0_at_r33g2000yqn.googlegroups.com>
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…
Date: Mon, 6 Apr 2009 06:35:03 -0700 (PDT)
Message-ID: <490749aa-90dd-4eb9-84cf-f40f177edaa0_at_r33g2000yqn.googlegroups.com>
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-declaration-observation-1.html
His book about "Applied Mathematics for Database Professionals" is also a very good read. Received on Mon Apr 06 2009 - 08:35:03 CDT