|
I would build a dedicated admin delete view where the admin can see a list of items flagged for delete, possibly a treeview if they are deep enough and allow the admin to confirm delete. Chasing down the dependency tree will be a PITA unless you know the structure intimately, the diagram should help.
It does seem a lot of work to get rid of junk when you already filter it out with a flag, I presume your flag already does the cascade thing (unless you cheated and only did the top levels). Why not change the flag to a datetime and delete anything flagged older than n months/years in a purge function.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi. So I know I've done this before but I can't get it to work despite every example on the internet telling me it should... I'm trying to call a procedure from code. I keep getting an "Invalid SQL statement" exception. Any ideas why?
create procedure DoSomething
as
begin
end DoSomething;
I try to execute from code like so:
Using conn = New OracleConnection(MyConnectionString)
Using cmd = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "DoSomething"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
At which point I get the aforementioned exception.
What am I doing wrong?
Sam.
|
|
|
|
|
Does calling your SP through Oracle directly work? Can you narrow it down to whether the error is in the Oracle syntax or in the C# calling it?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Yes, it works fine if I call it from an oracle client. This is only a problem trying to execute it from code.
|
|
|
|
|
When I googled the error and C# I saw some posts where people had to add some settings to their commands. I suggest googling until you find the right thing.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
You're getting error due to the statement(s) of stored procedure. Always share the relevant code/script in your question for qucik answer. Check this
ORA-00900: invalid SQL statement tips[^]
|
|
|
|
|
No, the procedure works correctly when I execute it from SQLLite.
|
|
|
|
|
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[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
