Database

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

Tridip Bhattacharjee wrote:

please provide me good information about various locking mechanism

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

INSERT INTO Table1 (Column1, Column2)
VALUES (@Value1, @Value2);

INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2 FROM Table2
WHERE Column1 = @Parameter;

INSERT INTO Table1 (Column1, Column2)
SELECT t2.Column1, t3.Column2 FROM Table2 AS t2
INNER JOIN Table3 AS t3 ON t2.ColumnID = t3.ColumnID
WHERE t2.Column1 = @Parameter;

SELECT TOP(1) * FROM (
SELECT TOP(2) StartTime FROM dbo.RunSummary ORDER BY StartTime DESC
) T ORDER BY StartTime