Hi, I would like to hear if any of you have an idea to do the following: I have one table containing some cardefinitions [id][Company][Mode], this could e.g. be Audi 100, Ford Mustan etc. I then have another field which is a string containing both the car and the model, this could be "Ford Mustang 2.5T", I then need to find the correct I...
I have a following oracle stored procedure CREATE OR REPLACE PROCEDURE getRejectedReasons ( p_cursor IN OUT SYS_REFCURSOR) AS BEGIN OPEN p_cursor FOR SELECT * FROM reasons_for_rejection; END; However, when I run this stored procedure in sql-developer then I dont see anything. I just see something like this: Connecting to...
Stored procedure that executes fine with a development database but fails to execute in production database? Error returned is :"PLS-00306: wrong number or types of arguments in call to <procedure naeme>" VC++ database oracle Closed:The error occurred due to wrong synonym. ...
Hi, I've been trying to make this work for the last couple of hours. I have a simple stored proc called get_log_entries: CREATE DEFINER=`root`@`localhost` PROCEDURE `get_log_entries`(p_min_id INT) BEGIN SET p_min_id = IFNULL(p_min_id, -1); SELECT * FROM db_log WHERE item_id > p_min_id; END It's dead simple and it returns results ...
Hi, We have set up a replication scheme master/slave and we've had problems lately because some users wrote directly on the slave instead of the master, making the whole setup inconsistent. To prevent these problems from happening again, we've decided to remove the insert, delete, update, etc... rights from the users accessing the slave...
Hi, We are developing a Visual Studio.NET 2008 application with Oracle 9i as backend. Is it true that the usage of Stored procedures would be faster than Inline SQL ? This is a debate with my co-programmers. We are using ODP.NET (Oracle Data Provider for .NET) from Oracle. Thanks. ...
Hey everyone, I have a report that takes two parameters from a couple of text boxes that always ends up being null when passed to the stored proc. Here is the code. Also I stepped through the code and the passed in parameters have values but between passing in the params to when the proc is executed it becomes null. protected voi...
I am looking for good resources on Stored Procedures. Particularly for Microsoft SQL server 2005, but any overall good resources for SQL will do. These can be books, websites, etc. Thanks ...
From an efficiency and best practices point of view, I appreciate everyones thoughts. I have a stored procedure that makes (n) copies of a row and inserts the row into the necessary table. For example. Make a copy of an inventory item and add the copies to inventory. I see two options for this type of stored procedure. Option 1: CR...
I'm new to the whole WPF programming, but have been doing Windows Forms programming for a while. Anyway, I have a button that when clicked will run a stored procedure on a SQL Server and display the resulting data in a grid within the same window. In windows forms world, I'd create a datatable, use a dataadapter to fill it and then ass...
Can any one tell me if its possible to create a stored procedure in oracle which accept array as an input parameter and how ? ...
A stored procedure that runs a SELECT on a large table is timing out. The where clause is causing the timeout, because I am selecting only cities that are in another table. AND city IN (SELECT DISTINCT city from tOH where clientId = @clientId) AND state IN (SELECT DISTINCT state from tOH where clientId = @clientId) *note almost alway...
My database has a parent table with an auto-incrementing primary key identity 'ID', and a normal 'TIMESTAMP column'. I have child tables with a foreign key that refer to the parent 'ID' column. I want to write a stored procedure that inserts a new column into both the parent and child databases. How would I set the child 'ID' column to...
Hello, I have a stored procedure that makes very complex joins and returns 1 row of data (LIMIT 1 is used). In my application I then use that data to make some calculations. This way I try to keep business logic out of stored procedures. But now I need to get that data in another stored function to also make some calculations. The reaso...
How much effort would it take to migrate a large existing codebase from a Strongly Typed Dataset driven data access layer to a DAL driven by stored procs and/or a more modern ORM package? Is there any shiny tool that automates a portion of this process? The current code base has well over 100+ datasets mirroring the sql database (but ha...
I'd like the stored procedure to return a list of data, just like what "select" do. But the PHP/MySql engine my hosting service does not support that feature. The only output parameter I can provide is primitive types, like int, varchar and so on. So I tried to combine the whole list into one big string with the help from cursor. But ...
Here's what I want to do: Given a table PeopleOutfit (id int primary key, boots int, hat int) And a stored procedure UpdateOutfit @id int, @newBoots int = null, @newHat = null Is there a way to tell whether I called this procedure as exec UpdateOutfit @id=1, @newBoots=null, @newHat=5 effectively telling that the person with id ...
I'm a developer and I suck at SQL:) Please help me out here. I'd like to create my own Stored Procedure that creates a Tenant in my SaaS database. In order to do this I need to create a new SQL Login for the Tenant and then add it to a predefined SQL Role. I'm already stumped just trying to create the Login. Here is what I've tried....
How can I execute SQL statements from a C program. For example I want to display the output of select * from EMPfrom a c program. Do I need to do it using Stored Procedures? Do I need any library files to do it. My C compiler is Tiny C Compiler and I am currently having SQL server studio 2005. Suggestions for Unix/AIX platform + GCC are ...
I am trying to decypher an absolute monstrosity of a stored procedure and i'm not having much luck. Are there any free tools that will help visualise the query or at least format the syntax into a more readable format ? Any hints and tips are also welcome. The type of database i am using is MS sql server 2005 ...