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!

Sequence Trigger (exclude number range) Fails wtih ORA-01403: no data found

User_I4Y3CJun 23 2020 — edited Jun 24 2020

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

HERE'S THE CODE:

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

-- Create the Sequence

CREATE SEQUENCE MYSCHEMA.TEST_SEQ START WITH 1 MAXVALUE 13 INCREMENT BY 1 CYCLE NOCACHE NOORDER;

-- Create the Table

CREATE TABLE MYSCHEMA.TEST_SEQ (

item nvarchar2(2) NOT NULL,

seq nvarchar2(6) NULL)

TABLESPACE

USERS

STORAGE (initial 50k);

--Insert rows into the table

INSERT ALL

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('A','')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('B','')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('C','3')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('D','4')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('E','')

SELECT * FROM DUAL;

--Create the trigger

CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG

BEFORE INSERT ON MYSCHEMA.TEST_SEQ

FOR EACH ROW

    DECLARE seqto NUMBER(6);

    BEGIN

      IF :new.SEQ IS NULL THEN

        IF MYSCHEMA.TEST_SEQ.NEXTVAL = '100000'

        THEN

          SELECT 104000 INTO seqto FROM DUAL;

        BEGIN

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

         SELECT MYSCHEMA.TEST_SEQ.NEXTVAL INTO :new.SEQ FROM DUAL;

        END;

        END IF;

        ELSE

          IF MYSCHEMA.TEST_SEQ.NEXTVAL = '999999'

            THEN

              BEGIN

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

                SELECT MYSCHEMA.TEST_SEQ.NEXTVAL INTO :new.SEQ FROM DUAL;      

              END;

          END IF;

      END IF;   

      EXCEPTION WHEN NO_DATA_FOUND THEN

      BEGIN

        SELECT 1 INTO :new.SEQ FROM DUAL;

        INSERT INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES (:new.ITEM,:new.SEQ);

    END; 

  END;

/

--Insert test rows

INSERT ALL

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('F','12')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('G','')

SELECT * FROM DUAL;

This post has been answered by Solomon Yakobson on Jun 24 2020
Jump to Answer

Comments

User_64CKJ
867570 wrote:
In an applet, StyleManager.loadStylesheet(final String fname) causes a null exception .
What's a <tt>StyleManager</tt>? I don't see it in the Java 6 JavaDocs.
The code below evade this :

StyleManager styleManager= StyleManager.getInstance();
URL url=getURL("/indicateurs/charts.css", Main.class);
What <tt>URL</tt> constructor takes a <tt>String</tt> and ..what is that, a <tt>Class</tt>?
} catch (IOException ex) {
System.out.println("exception:"+e);
Don't throw away exception information. Call <tt>e.printStackTrace()</tt>. Even if you choose to dump part of the message to output, <tt>System.err</tt> rather than <tt>System.out</tt> should be used.
Will there be a more simple way to deal with the css in an applet ?
Can you:
<li>Use the code formatting tags as described on the sticky post at the top of the forum thread listing?
<li>Ask a smart question? The current code & text reads like nonsense.
870573
it is in the javafx general Question :
StyleManager is from the package : com.sun.javafx.css
aidreamer
Packages in com.sun are usually intended as implementation or experimentation. Using them in your own projects is not recommended. As for using CSS in an applet, you should be able to do that without having to resort to those packages. The tutorial on CSS is right here: http://download.oracle.com/javafx/2.0/css_tutorial/jfxpub-css_tutorial.htm
David Grieve-Oracle
I am interested in knowing what the traceback of the NPE is so I can see if it is a bug in the code. Thanks.
darrylburke
I would guess it's just a result of the security manager preventing the applet from accessing files on the local filesystem.

db
870573
when the code is :
Scene scene = new Scene(root);
scene.getStylesheets().addAll("/indicateurs/charts.css");


the message from the console java is :
WARNING: com.sun.javafx.css.StyleManager$2 run Resource "null" not found.

SEVERE: javafx.scene.control.Control impl_processCSS The -fx-skin property has not been defined in CSS for Button@1feac8e[styleClass=sample-tile]

SEVERE: javafx.scene.control.Control impl_processCSS The -fx-skin property has not been defined in CSS for Button@1feac8e[styleClass=sample-tile]



thanks for your help
1 - 6

Post Details

Added on Jun 23 2020
21 comments
1,351 views