We have an ASP.NET web service that invokes a stored procedure on our DB (Oracle 11g). The problem is that the call to the procedure blows up every time an empty string is passed as one of the parameters. We're seeing an ORA-01084 error, which indicates to me that call is failing before the procedure is actually run. Here's the proced...
This fails on VS2010 RC LINQ-to-SQL with the InvalidOperationException "Stored procedures cannot be used inside queries.": var foo = (from a in aTable from b in this.SomeStoredProcedure() where a.Id == b.Id select b.Id); SomeStoredProcedure is a SQL procedure which returns a table. 'join' also appears to fail. Any thought...
Hello, I am using a platform (perfectforms) that requires me to use stored procedures for most of my queries, and having never used stored procedures, I can't figure out what I'm doing wrong. The following statement executes without error: DELIMITER // DROP PROCEDURE IF EXISTS test_db.test_proc// CREATE PROCEDURE test_db.test_proc() S...
The architecture for this scenario is as follows: I have a table of items and several tables of forms. Rather than having the forms own the items, the items own the forms. This is because one item can be on several forms (although only one of each type, but not necessarily on any). The forms and items are all tied together by a common O...
I am having the below statement from stored procedure. It's giving Insufficient Privileges. But If i run the create statement alone from the sql prompt it's creating table. execute immediate 'create table TEST_ABC( NO_AC NUMBER(8) , ILL_PER VARCHAR2(15) , INIT_C DATE ...
Hello, I have a stored procedure that when I ran it from within the SSMS, it runs fast about 20 sec. but when I execute this same stored procedure from my .net application it takes about 50-70 secs!! Why is that happening? Thank you. ...
Hi everyone, is it possible to call an oracle storage procedure with a record type as IN parameter. In Oracle I have a record definition: TYPE R_InData_tab IS RECORD ( ... ); TYPE InData_tab IS TABLE OF R_InData_tab INDEX BY BINARY_INTEGER; Now I want to set this record type as parameter: PROCEDURE myProcedure (inRecord IN myPackage....
I am using a gridview to select, delete and update data in database. I have written a single SP for doing all these operation. Based on a parameter SP decides which operation to perform. Here is the image of my gridview <asp:GridView ID="GridView1" runat="server" DataSourceID="dsDomain" AllowPaging="True" AllowSo...
I have a problem where I've mapped a stored procedure named sp_getMyEntity, which takes in one parameter called Id and then maps the results to a custom entity called MyEntity I'm using mock data to illustrate the point. When I run the stored procedure with an id of 1, I get two distinct results back from the database: exec [dbo].[sp_g...
I am using a gridview where i am calling a stored procedure which has 4 input parameters. Out of these 4 parameters, values are to be given such that DomainId = This has to be the row which is to be deleted. This is a primary key Domain = This field has to be passed to SP as NULL. Description= This field has to be passed as NULL. ...
Our team just experienced for the first time the hassle of not having version control for our DB. How can we add stored procedures at the very least to version control? The current system we're developing relies on SPs mainly. ...
I have a LINQ TO SQL Context that I have created that calls a stored proc. When I'm looping through the ISingleResult is it creating entities on the fly while the underlying DataReader reads the results or does it put every thing into Entities upfront before the function call returns. Basically what is going on is I'm working with a sto...
Today is my first day using ASP.NET MVC, and I'm finding it very intriguing. I only just started learning asp.net. So basically I'm trying to call a procedure from an MSSQL database, and with it I need to send a paramater "PlaceID", which is an integer. This procedure basically just picks out a number of columns from different tables in...
I have a table 'account' (id, email, pass) in MySQL database. I have stored procedure: DELIMITER $$ CREATE PROCEDURE `LoadAccount`(email_p VARCHAR(100)) BEGIN SELECT pass FROM account WHERE email = email_p; END$$ DELIMITER ; And here's the code: function loadAccount($email, $pass) { // connect to DB // ... $query = "CA...
Hi i have a store procedure, where i do a select query. I want order this by an external parameter. I post an minimal example: CREATE PROCEDURE [dbo].[up_missioni_get_data] @order VarChar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- In...
Hi folks, I work with an OO database abstraction layer that I wrote. I have parameters you can set for cleaning up child records when the parent is deleted. I was wondering if there are other, better ways to do this? Can you do this in mySQL via a stored procedure or other method? Thanks for any advice. JG ...
Why calling a user defined function need the owner name when calling a stored procedure doesn't ? Please help! ...
We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long. Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research. Currently I am ...
[Also on SuperUser - http://superuser.com/questions/116600/can-i-spead-out-a-long-running-stored-proc-accross-multiple-cpus] I have a stored procedure in SQL server the gets, and decrypts a block of data. ( Credit cards in this case. ) Most of the time, the performance is tolerable, but there are a couple customers where the process is...
Using .Net and Oracle 11g - I've been returning dataTables from a procedure inside of a package by opening a Cursor. IE - 'OPEN TABLEREF FOR SOMESQL; Where TableRef is an 'OUT' Param. It's been working great. What I'm struggling to do is have that first Proc call another Proc and let that second Proc open the cursor. Inside Proc1 (w...