|
Still you didn't share the script of stored procedure.
Alright, possibly the issue is related to parameters then. Have you passed values for stored procedure parameters correctly? You should care datatime parameters particularly, you should pass correctly using date/time values instead of string value. Also the format. Did you check the link in my answer?
|
|
|
|
|
I didn't share the scrip because it works when it's execute on the server. The code I provided has identical signatures. There are no parameters passed into the procedure. Perhaps that is a problem - that since I didn't pass any parameters the Oracle library doesn't want to execute the command even though there are no parameters for the procedure.
|
|
|
|
|
Here is the actual code, though I can't imagine why this would help:
create or replace
procedure UpdateUnitType as
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;
|
|
|
|
|
It's been long time ....Oracle, try different syntax like below
cmd.CommandText = "{CALL StoredprocedureName()}" OR
cmd.CommandText = "BEGIN StoredprocedureName(); END;"
And are you using oracle "package" by any chance?
Because the stored procedure missing the declare keyword for cnt. Possbily you have declared the cnt inside the package & executed the stored procedure to see the output. It would work.
So if you have the stored procedure inside the package, you should mention(prefix) the package before the stored procedure in your code like below.
cmd.CommandText = "PackageName.ProcedureName"
•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
So you should use/reference the proper driver(dll) in your project. Verify both versions of assemblies Oracle/PLSQL: ORA-00900[^]
|
|
|
|
|
hopefully I am missing something but are you sure you are using SQLite? as this link shows that it doesn't support stored procedures.
http://www.sqlite.org/whentouse.html[^]
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.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
You're right. Actually, I'm using Oracle's "SQL Developer". It was just easier to say SQLLite.
|
|
|
|
|
Well, what the ORA-00900 says is that there is something wrong with the CommandText, I'm afraid you will have to share it if you want any useful feedback
|
|
|
|
|
With these sorts of issues the first thing I do is write a really simple stored procedure to eliminate the stored procedure being an error.
Now I know you have tested the stored procedure from Oracle however I would do this anyway.
So create something like :
create procedure test_orac as
begin
declare @testvar int
select @testvar = 1
end
Then call this procedure from .NET .
If you still get the same error check your tnsnames.ora file to be sure that it points to the correct service and that the syntax within that file is correct.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Did you resolve the issue?
If you did it would be interesting to know how you resolved the issue.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I think one more hit & run
|
|
|
|
|
Yes ,for some, the forum works one way - it's there just to get answers to your questions and not to contribute to the wider knowledge of the community.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
please provide me good information about various locking mechanism exist in Sql server and when & where which lock is used. thanks
tbhattacharjee
|
|
|
|
|
Tridip Bhattacharjee wrote: please provide me good information about various locking mechanism Understanding Locking in SQL Server[^]
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Since you did not provide which SQL Server the answer is several. Query BOL and you will find them. You will also need to know the type of resource that is being locked. DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT
|
|
|
|
|
we have 2 databases with phpmyadmin v5.5.31 and we want to convert data from the one to the other, so we are in need of a converter that will fix the difference of tables
is there any one who could give a hand to that?
thanks
|
|
|
|
|
Red-Gate SQL Compare works with SQL Server not sure what your DB is but it may be compatible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello..can anyone help me on this.
I want to insert data based on the dropdownlist value. This is my database sql.
SELECT TOP (200) Actuators.id, Actuators.ActuatorInfo, Actuators.Fan, Actuators.Date, Actuators.DeviceId, Actuators.House, Actuators.DateView
FROM Actuators INNER JOIN TDevice ON Actuators.DeviceId = TDevice.id INNER JOIN TMushroomHouse ON TDevice.MushroomHouseId = TMushroomHouse.id
If I want to insert data DateView into table Actuators, based on the dropdownlist for mushroom house name, how I can inner join with table TDevice and TMushroomHouse.
For example:"Insert into Actuators(DateView) Values (@DateView) INNER JOIN ..."
|
|
|
|
|
There is no "join" clause on the insert-statement. You'd insert the values you need, so I doubt you need to join any lookup-table in there.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I don't understand what exactly you want to do, but there are two ways to insert data:
1. Insert values.
INSERT INTO Table1 (Column1, Column2)
VALUES (@Value1, @Value2);
2. Insert from table/tables.
INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2 FROM Table2
WHERE Column1 = @Parameter;
or something like
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;
Best regards,
Andrius Leonavicius
|
|
|
|
|
Hello..i want to ask question.
In my database there is a column for Date such as below:
2014-03-26
2014-03-25
2014-03-24
Does anyone know how I want to retrieve data value from a previous row with select statement.If I want the latest data, so the sql is like this ORDER BY Actuators.Date DESC";and the answer I get is 2014-03-26. But if I want to show the previous data value from previous row what can I do?The result will show date 2014-03-25. Tq
|
|
|
|
|
Which database system? SQL Server 2012?
You want to get the second highest? Or both the highest and second-highest in one row?
You'll never get very far if all you do is follow instructions.
modified 23-Apr-14 13:12pm.
|
|
|
|
|
Database system is SQL Server 2012. I just want to get the second highest in a column Date.
|
|
|
|
|
You could use ROW_NUMBER and select the one with a value of 2.
Or something like:
SELECT TOP(1) * FROM (
SELECT TOP(2) StartTime FROM dbo.RunSummary ORDER BY StartTime DESC
) T ORDER BY StartTime
Neither seems very elegant.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Tqvm
|
|
|
|
|
Syafiqah Zahirah wrote: what can I do? ..SELECT TOP 2
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|