Firebird GEN_ID() function equivalence in ORACLE

Asked

Viewed 152 times

1

Speak guys, I’m starting learning with BD Oracle, before I used Firebird. However I’m having a problem that I’m not able to solve. I can not include a new country by the system (java), if I insert by Sqldeveloper, it appears in the query. The error that appears when I try to include is the following: 

java.sql.SQLException: ORA-00942: a tabela ou view não existe

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
    at bd.Conexao.pegaGenerator(Conexao.java:50)


(...)

the line the error indicates to me, is :

public static Integer pegaGenerator(String generator) 
    {
        try 
        {
            Statement st = Conexao.getConexao().createStatement();
            ResultSet rs = st.executeQuery("SELECT GEN_ID(" + generator + ", 1) FROM RDB$DATABASE");
            rs.next();
            return rs.getInt(1);
        } 

        catch (Exception e) 
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, "Não foi possível obter o generator!");
            return null;
        } 
    }

The table in Oracle I created so: 

CREATE TABLE Pais
(
ID_Pais INTEGER NOT NULL,
Nome_Pais VARCHAR(60) NOT NULL,
Sigla_Pais VARCHAR(3) NOT NULL,
Status_Pais CHAR(1) NOT NULL,
CONSTRAINT ID_Pais PRIMARY KEY (ID_Pais) 
);

and for the tool I made Rigger

java database oracle firebird oracle11g

  • If you do "SELECT GEN_ID(" + Generator + ", 1) FROM PARENTS" does not work?

  • I used to do this in Firebird to be oriented, then he would take all tables, this code I don’t need to repeat.

  • Voce can make a select MAX([coluna]) + 1 from [tabela] but I believe that Oracle does not have a function GEN_ID() equal to Firebird - the question of info. the oracle equivalent of the Firebird dummy table RDB$DATABASE is DUAL

  • Ah, I think I get it now (I don’t know Firebird). You want to generate an ID to use on ID_Pais. That’s it?

1 answer

3


In the Firebird there are the generators. Already in Oracle there are sequences.

To create it you must do:

create sequence seq_tabela_exemplo;

You can even set multiple settings:

create sequence seq_tabela_exemplo
   start with 5
   increment by -1
   maxvalue 5
   minvalue 0
   nocache
   cycle;

To get the next value, run:

select seq_tabela_exemplo.nextval from dual;

For more details, see documentation.

  • i tried Resultset rs = st.executeQuery("SELECT SEQ_ID_(" + Sequence + ", 1) .NEXTVAL FROM DUAL"); But error saying: java.sql.Sqlexception: ORA-00904: "SEQ_ID_": invalid identifier. My syntax is wrong ?

  • The right is "SELECT " + Quence + ". NEXTVAL FROM DUAL"

  • It worked out! Thank you very much.

Browser other questions tagged java database oracle firebird oracle11g

You are not signed in. Login or sign up in order to post.