CODEWITHSUNDEEP
javaoracle-databasejdbcplsql
John Kyle
John Kyle

Reputation: 125

PL/SQL JAVA ORACLE ERROR ORA-00904: INVALID IDENTIFIER

When i run my program i got an error:

ORACLE ERROR ORA-00904: "PACJENT_ODDZIAL": INVALID IDENTIFIER

I do not know where is a problem.
I am using NetBeans and SQL Developer.

Here is my Java Code:

private String PacjentOddzial() {
    String PacjentOddzial = "null";

    con = Polaczenie.ConnectDB();

    String sql = "SELECT pacjent_oddzial (" + Login.login + ") from dual";
    try {
        pst = con.prepareStatement(sql);
        rs = pst.executeQuery();

        if (rs.next()) {
            String add1 = rs.getString(1);
            PacjentOddzial = add1;
        }
    } catch (SQLException | HeadlessException e) {
        JOptionPane.showMessageDialog(null, e);
    }
    return PacjentOddzial;
}

Here is PL/SQL Function:

create or replace FUNCTION pacjent_oddzial
    (PES IN NUMBER)
    RETURN VARCHAR2
IS
    ILE VARCHAR2(30);
    ZMIENNA NUMBER;
BEGIN
    SELECT PACJENTID INTO ZMIENNA FROM PACJENT WHERE PESEL=PES;

    SELECT NAZWAODDZIALU INTO ILE FROM PRZYJECIE_NA_ODDZIAL WHERE 
    PACJENTID=ZMIENNA and rownum=1;

    RETURN ILE;
end;

Upvotes: 1

Views: 1648

Answers (3)

unleashed
unleashed

Reputation: 771

Your SQL from this ...

String sql = "SELECT pacjent_oddzial (" + Login.login + ") from dual";

... becomes ...

SELECT pacjent_oddzial (someLoginValue) from dual;

... and in this case, the parameter is an identifier, not a string.

If you change the sql by introducing single quotes ...

String sql = "SELECT pacjent_oddzial ('" + Login.login + "') from dual";

... becomes ...

SELECT pacjent_oddzial ('someLoginValue') from dual;

... should work, even with your existing code.

However, you should read into sql injection and bind variables. When you concatenate user supplied values into sql, you are susceptible to a sql injection attack. So, instead, you should probably use sql like ...

String sql = "SELECT pacjent_oddzial (?) from dual";

... then apply the value via ...

pst.setString(1, Login.login);

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

In general an ORA-00904 error means the database can't see the thing you are referring to. So either:

  1. It really doesn't exist, at least not the way you typed it. Check the spelling, look for it in the data dictionary (all_objects etc) or where it's declared if it's a code item, check you're not referring to a procedure as if it's a function or vice versa, check whether any double-quoted identifiers are involved as these are case-sensitive.
  2. It really does exist, but your account can't see it. Check grants and synonyms, and make sure you are connected as the right user.

Regarding procedures vs functions, say you create a procedure called give_raise. Now you can call it like this:

begin
    give_raise(123, 10);
end;

or this:

call give_raise(123, 10);

but not like this:

select give_raise(empno, 10) from employees;

because procedures and functions are two different things.

Regarding double-quoted identifiers, by default names are case-insensitive so you can refer to give_raise, GIVE_RAISE etc interchangeably, because the SQL parser and PL/SQL compiler internally uppercase everything as a first step. However, double-quotes make it case-sensitive (as well as allowing other normally invalid names such as "012 Crazy huh?"). If you name your procedure "GiveRaise" (in quotes) then that is how you must refer to it, quotes and all, forever.

Also, in Oracle SQL there are things you can and can't do to alias items. For example, you can refer to an alias in an order by clause:

select dummy as myalias from dual
order by myalias;

but not in a group by clause:

/* Invalid, gives ORA-00904: */
select dummy as myalias from dual
group by myalias;

Regarding privileges, note that standard definer-rights stored PL/SQL (create procedure etc) does not use roles, so even though you have logged in as JOE who has the HR_QUERY role, and Joe's SQL queries can access everything in HR, Joe's stored procedures can only see his own objects and anything directly granted to him (not via a role).

Regarding namespace issues, say that user HR has granted SELECT on EMPLOYEES to Joe. Joe still can't just select * from employees because he has to say where it is, rather like needing to specify the path in a filesystem, or dot notation in other languages. The default is the current schema, i.e. JOE.EMPLOYEES, which of course doesn't exist. Then the options are:

  1. Refer explicitly to HR.EMPLOYEES (though hardcoding isn't ideal).
  2. Create a public synonym for HR.EMPLOYEES (though can limit your options later due to the global nature of public synonyms, plus it broadcasts your object names which might be a security concern).
  3. Create a private synonym for HR.EMPLOYEES in the JOE schema.
  4. Have Joe set his default schema to HR using alter session set current_schema = HR.

Upvotes: 1

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60046

The syntax of SELECT is wrong :

String sql = "SELECT pacjent_oddzial ("+Login.login+") from dual";

the result of that query should look like this :

SELECT pacjent_oddzial (some_string) from dual
-- ------------------------^^^^^^

And this is not a correct syntax.

If you try to call your function then this is not also correct you can use this syntax :

{? = call CREATE_A_PERSON (?)}
 1              2          3

Which :

  • (1) is the return values
  • (2) the name of your function
  • (3) is the parametters of your method
    String query = "{? = call pacjent_oddzial (?)}";
    CallableStatement stm = conn.prepareCall(query);
    stm.registerOutParameter(1, Types.VARCHAR);//return value
    stm.setString(2, Login.login);//set the parametter
    String return = stm.getString(1);//the result of your function

take a look about that here :

JDBC CallableStatement – Stored Procedure OUT parameter example

Upvotes: 2

Related Questions