Database

Nico Haegens wrote:

Never heard of foreign keys that were dependent on user role

Nico Haegens wrote:

I always implement foreign keys when I create the database structure

create or replace
procedure UpdateUnitType as
--updates unit type codes
  cnt number(10,0):=0;

begin
 
  for u in (select * from unit where type_cd = 1)
  loop
  
    cnt := 0;
  
    select count(billet_id)
    into cnt
    from billet
    where fiscal_year = u.fiscal_year
    and uic = u.uic;      
    
    if cnt > 0 then
    
      update unit
      set type_cd = 2
      where unit_id = u.unit_id;
    
    end if;
      
  end loop;

end updateunittype;

cmd.CommandText = "{CALL StoredprocedureName()}"

cmd.CommandText = "BEGIN StoredprocedureName(); END;"

cmd.CommandText = "PackageName.ProcedureName"

From link:[^]

•I was using a older JDBC driver so after fixing the above error I was getting
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [{ call PROC_GET_NEW_CHECK_NUMBER(?,?,?) }]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:44)
at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:100)
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

To fix this I upgraded my JDBC driver to the correct version that matches the Oracle installation. So in my case I used ojdbc6_11g_11_2_0_1.jar since my Oracle instance :Oracle 11.2.0.1.0

Quote:

n order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth.

create procedure test_orac as
begin

declare @testvar int
select @testvar = 1

end