Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Techniques for selecting an object's attribute

User_1871May 16 2022

I have a column that is an object datatype — SDO_GEOMETRY:

create table my_tbl (shape sdo_geometry);
insert into my_tbl (shape) values (sdo_geometry('LINESTRING(1 2,3 4)'));

The object has attributes (2.2 SDO_GEOMETRY Object Type):

SDO_GTYPE
SDO_SRID
SDO_POINT
SDO_ELEM_INFO
SDO_ORDINATES

I want to select an attribute from the object. I can do that by using a table alias:

select
  a.shape.sdo_gtype
from
  my_tbl a

Out of curiosity, are there any other ways to select object attributes, other than creating an alias?
As a non-expert, it seems strange to me that the alias is necessary. Of course, creating an alias isn't a big deal, but I would have guessed that I could just use the dot notation without an alias: select shape.sdo_gtype from my_tbl. But that doesn't work: ORA-00904: "SHAPE"."SDO_GTYPE": invalid identifier .

This post has been answered by BluShadow on May 16 2022
Jump to Answer

Comments

Frank Kulash

Hi,

4249458 wrote:

...

Attached are all the scripts I used so you can recreate the issues.

...

Post everything right in your message.  Not everyone can (or will) open attachments.

Tubby

4249458 wrote:

Hello PL/SQL Experts,

I need your help. … Banging my head on the wall trying to make this simple sequence trigger work. …..

BACKGROUND:

a) I need a sequence that will update a column with a number between (1 - 10) [the column is VARCHAR2(2) datatype]

b) The table currently has values in this field that we need to maintain (3,4). So I need the sequence to skip those numbers. …

c) This also needs to work for update statements.

Attached are all the scripts I used so you can recreate the issues.

When I run an insert I get the below errors:

ORA-01403: no data found

ORA-04088: error during execution of trigger

I had a look at what you posted and stopped when I saw you issuing DDL within the trigger to alter the sequence.

Nothing about the current code you've posted is something I'd support for real world usage. That said there are plenty of highly skilled people here that love to volunteer their time and I'm sure one of them will be able to help you with a sustainable solution.

Can you please elaborate on your requirements, what value does a sequence between 1-10 (excluding 3 and 4) have? How can you use that for business value? Sequences aren't meant for business value, they're meant for technical value, a way to ensure a unique value in a performant manner.

Cheers,

Solomon Yakobson

It isn't clear what you need. Here is example of skipping sequence generated values 3 & 4:

DROP SEQUENCE SEQ

/

DROP TABLE TBL PURGE

/

CREATE SEQUENCE SEQ

  MAXVALUE 10

  CYCLE

  CACHE 5

/

CREATE TABLE TBL(

                 COL NUMBER

                )

/

CREATE OR REPLACE

  TRIGGER TBL_BIR

    BEFORE INSERT

    ON TBL

    FOR EACH ROW

    BEGIN

        :NEW.COL := SEQ.NEXTVAL;

        IF :NEW.COL = 3

          THEN

            :NEW.COL := SEQ.NEXTVAL;

        END IF;

        IF :NEW.COL = 4

          THEN

            :NEW.COL := SEQ.NEXTVAL;

        END IF;

END;

/

INSERT

  INTO TBL

  SELECT  1

    FROM  DUAL

    CONNECT BY LEVEL <= 15

/

SELECT  *

  FROM  TBL

/

       COL

----------

         1

         2

         5

         6

         7

         8

         9

        10

         1

         2

         5

         6

         7

         8

         9

15 rows selected.

SQL>

SY.

User_I4Y3C

Thanks Frank - It's been updated.

User_I4Y3C

Tubby - This was just an example, obviously no one would every use a sequence between 1-10, but why test with a sequence between 1 - 10000?

I originally had the DDL in a separate package that was being called by the trigger - but consolidated it to limit the number of things to be created while testing.

I'm not sure what's unclear about the requirments:

1) I have a table with a column that must be updated with a unique value moving forward (upon each new insert)

         *For example a number range between 1 - 10 (because the table column is varchar2(2))

2) There are currently values in this column that can NOT be deleted

          *Numbers 3 and 4 are already in the table - and can NOT be deleted

3) The numbers should overwrite any manual entry input by the user to the new sequence

4) When the sequence cycles - it should still skip numbers 3 and 4

Solomon Yakobson

I already showed how to do it.

SY.

User_I4Y3C

Thank You Solomon - I like you approach. .. but I still get the below error

SQL> INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13;

INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13

                   *

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

Solomon Yakobson

Show trigger code.

SY.

User_I4Y3C

I used your trigger code?!?

CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG

BEFORE INSERT OR UPDATE ON MYSCHEMA.SEQ_TEST

FOR EACH ROW

    BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      IF :new.SEQ = 3

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

      IF :new.SEQ = 4

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

    END;

Solomon Yakobson
Answer

Actually, there is a chance 3/4/ will not be skipped. Assume SEQ.NEXTVAL = 4. Then code I posted generates another SEQ.NEXTVAL. Issue is some other sessions could generate SEQ.NEXTVAL too, so we can't assume our session will get SEQ.NEXTVAL=5. It can get 3 or 4. We need to loop until we get anything but 3 or 4:

DROP SEQUENCE SEQ

/

DROP TABLE TBL PURGE

/

CREATE SEQUENCE SEQ

  MAXVALUE 10

  CYCLE

  CACHE 5

/

CREATE TABLE TBL(

                COL NUMBER

                )

/

CREATE OR REPLACE

  TRIGGER TBL_BIR

    BEFORE INSERT

    ON TBL

    FOR EACH ROW

    BEGIN

        :NEW.COL := SEQ.NEXTVAL;

        WHILE :NEW.COL IN (3,4) LOOP

          :NEW.COL := SEQ.NEXTVAL;

        END LOOP;

END;

/

INSERT

  INTO TBL

  SELECT  1

    FROM  DUAL

    CONNECT BY LEVEL <= 15

/

SELECT  *

  FROM  TBL

/

      COL

----------

        1

        2

        5

        6

        7

        8

        9

        10

        1

        2

        5

        6

        7

        8

        9

15 rows selected.

SQL>

SY.

Marked as Answer by User_I4Y3C · Sep 27 2020
User_I4Y3C

I was thinking something like:

    BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      IF :new.SEQ BETWEEN '3' AND '4'

      THEN

        BEGIN

          EXECUTE IMMEDIATE 'ALTER SEQUENCE MYSCHEMA.TEST_SEQ RESTART START WITH 5';

          :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

But still I can't get past the ORA-ERROR. Even the simplest trigger won't work.

Solomon Yakobson

Something doesn't add up here:

SQL> INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13;

INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13

                  *

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

And:

CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG

BEFORE INSERT OR UPDATE ON MYSCHEMA.SEQ_TEST

FOR EACH ROW

    BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      IF :new.SEQ = 3

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

      IF :new.SEQ = 4

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

    END;

Insert statement inserts into TEST_SEQ while based on your trigger TEST_SEQ is sequence name, not table name and table name is SEQ_TEST.

SY.

Solomon Yakobson

You were told already - you can't execute DDL from a trigger since DDL does implicit commit. You could do it as autonomous transaction but changes will not be visible to main transaction and you will have to use dynamic SQL all over the place. And don't forget there can be multiple sessions inserting. Anyway, modifying sequence on the fly is FLAWED approach.

SY.

User_I4Y3C

I think we would have received a different error if I didn't have the right table name sir. I am simply editing the names so I don't put the real table name or trigger name.

This is the problem:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

Someone said it might be permission issues? I created them in MYSCHEMA as system, and run the insert as system. But even if I GRANT the error still shows up.

Solomon Yakobson

SQL> CREATE USER MYSCHEMA IDENTIFIED BY MYSCHEMA

  2  DEFAULT TABLESPACE USERS

  3  QUOTA UNLIMITED ON USERS

  4  /

User created.

SQL> GRANT CREATE SESSION,

  2        CREATE TABLE,

  3        CREATE SEQUENCE,

  4        CREATE TRIGGER

  5    TO MYSCHEMA

  6  /

Grant succeeded.

SQL> CONNECT MYSCHEMA@PDB1SOL122/MYSCHEMA

Connected.

SQL> CREATE TABLE MYSCHEMA.SEQ_TEST(

  2                                 SEQ  NUMBER,

  3                                 ITEM NUMBER

  4                                )

  5  /

Table created.

SQL> CREATE SEQUENCE MYSCHEMA.TEST_SEQ

  2    MAXVALUE 10

  3    CYCLE

  4    CACHE 5

  5  /

Sequence created.

SQL> CREATE OR REPLACE

  2    TRIGGER MYSCHEMA.TEST_TRIG

  3    BEFORE INSERT

  4        OR UPDATE

  5    ON MYSCHEMA.SEQ_TEST

  6    FOR EACH ROW

  7    BEGIN

  8        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

  9        WHILE :new.SEQ IN (3,4) LOOP

10          :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

11        END LOOP;

12

13      END;

14  /

Trigger created.

SQL> INSERT

  2    INTO MYSCHEMA.SEQ_TEST(item)

  3      SELECT  1

  4        FROM DUAL

  5        CONNECT BY LEVEL <= 13

  6  /

13 rows created.

SQL> SELECT  *

  2    FROM  MYSCHEMA.SEQ_TEST

  3  /

       SEQ       ITEM

---------- ----------

         1          1

         2          1

         5          1

         6          1

         7          1

         8          1

         9          1

        10          1

         1          1

         2          1

         5          1

13 rows selected.

SQL>

SY.

Tubby

4249458 wrote:

Tubby - This was just an example, obviously no one would every use a sequence between 1-10, but why test with a sequence between 1 - 10000?

I originally had the DDL in a separate package that was being called by the trigger - but consolidated it to limit the number of things to be created while testing.

I'm not sure what's unclear about the requirments:

1) I have a table with a column that must be updated with a unique value moving forward (upon each new insert)

*For example a number range between 1 - 10 (because the table column is varchar2(2))

2) There are currently values in this column that can NOT be deleted

*Numbers 3 and 4 are already in the table - and can NOT be deleted

3) The numbers should overwrite any manual entry input by the user to the new sequence

4) When the sequence cycles - it should still skip numbers 3 and 4

I think perhaps the reason it isn't clear is because you are insisting you need to take this technical approach to solve your problem yet haven't put it in context of what business value it provides. I can't imagine this being a business requirement, and the need to do this technically isn't well founded. If you need a technical solution to have a unique number generated and you already have some numbers you need to avoid a collision with then you would use a value that can't possibly be present. For example if you had random values up to 10,000 that couldn't be used you would start the sequence at 10001 and always use nextval, no trickery in triggers, no confusion.

So although your technical requirements are clear, it's highly unlikely what you propose is actually the best solution.

Cheers,

User_I4Y3C

True -

I agree and that was something I considered - but the end result is still the same:

This is the problem:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

I was trying to kill two birds with one stone and maybe added confusion to the real issue at hand.

Solomon Yakobson

I have no Idea what are you doing. Provide complete output showing create table, create sequence, create trigger and insert statements. Or better run:

DROP TABLE MYSCHEMA.SEQ_TEST PURGE

/

DROP SEQUENCE MYSCHEMA.TEST_SEQ

/

CREATE TABLE MYSCHEMA.SEQ_TEST(

                               SEQ  NUMBER,

                               ITEM NUMBER

                              )

/

CREATE SEQUENCE MYSCHEMA.TEST_SEQ

  MAXVALUE 10

  CYCLE

  CACHE 5

/

CREATE OR REPLACE

  TRIGGER MYSCHEMA.TEST_TRIG

  BEFORE INSERT

      OR UPDATE

  ON MYSCHEMA.SEQ_TEST

  FOR EACH ROW

  BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      WHILE :new.SEQ IN (3,4) LOOP

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END LOOP;

    END;

/

INSERT

  INTO MYSCHEMA.SEQ_TEST(item)

    SELECT  1

      FROM DUAL

      CONNECT BY LEVEL <= 13

/

SELECT  *

  FROM  MYSCHEMA.SEQ_TEST

/

SY.

Tubby

4249458 wrote:

True -

I agree and that was something I considered - but the end result is still the same:

This is the problem:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

I was trying to kill two birds with one stone and maybe added confusion to the real issue at hand.

Ideally you'd strip all the fluff away from the trigger, start with :new.column_value := sequence.nextval and then add in pieces from there. That should give you a good idea where the code is failing. Or post the complete piece of code here, not one you're mocking up for the forum but the actual code (redact names if you need to).

I would highly encourage you to abandon the current approach though and go with something simple like simply starting the sequence outside the existing values range.

Cheers,

User_I4Y3C

This worked great -

I updated the loop to use a between instead:

WHILE :new.SEQ BETWEEN ''3'' AND ''4''

The ORA-01403 error was a permissions related issue. I recreated it under a user schema and everything worked fine.

Thank you gentlemen @"Solomon Yakobson" @"Tubby"

Mark D Powell

user4249458, I will just add a note that whenever you store a number into a varchar (varchar2) column that you may encounter issues if the output needs to be sorted on this column.  Character wise '1', '10', '2' is correct sort order but what you want is likely 1, 2, 10 so it may be necessary to format the varchar2 values to have leading zeroes: to_char(number,'09').

- -

HTH -- Mark D Powell --

1 - 21

Post Details

Added on May 16 2022
3 comments
490 views