Tagged Questions

Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

learn more… | top users | synonyms

0
votes
1answer
9 views

Execute grant for MySQL stored procedure revoked when stored procedure is altered

I have created the following stored procedure in a MySQL database (MySQL 5.5). DELIMITER $$ CREATE DEFINER=`remote_admin`@`%` PROCEDURE `usp_insert_deal_and_segment`() BEGIN "code here" END$$ ...
3
votes
2answers
67 views

CTE works as expected, but not when wrapped into a function

--testing table CREATE TABLE public.test_patient_table ( entity_id INTEGER NOT NULL, site_held_at INTEGER NOT NULL, CONSTRAINT entityid_pk PRIMARY KEY ...
1
vote
1answer
53 views

WITH RECOMPILE option in SQL Server [on hold]

When you have multiple connections concurrently trying to execute the query by using different parameters in the filter condition with recompile option, Is there are any chance that it may produce ...
0
votes
2answers
18 views

Using concat inside a stored procedure parameters

I have a stored procedure and function that accepts a varchar as a parameter and i have this code where i am using concat function in the parameters of such function and stored procedure. DELIMITER ...
0
votes
1answer
39 views

Problem with procedure performance

I'm having problem in Oracle, where I have two procedures that are executed one after the other. Where the first procedure, I have a cursor that does insert into a table. This insert has about 30 ...
1
vote
1answer
104 views

Stored procedure takes 18 seconds on first load, 0 seconds on subsequent loads, waiting several minutes makes it slow again

I have a stored procedure that returns a lot of data for a business dashboard. On first load it takes 18 seconds, after that it takes 0 seconds, even if I change the input parameters at random it ...
0
votes
0answers
50 views

CLOB data type is not working as expected [closed]

I have this Oracle 12c procedure CREATE OR REPLACE PROCEDURE LOGINCHECK(SQLQRY IN CLOB) AS C INTEGER; N INTEGER; RC SYS_REFCURSOR; stmt clob:= To_Clob('begin ' || sqlqry || '; end;'); ...
1
vote
2answers
205 views

Issues with module signing and SSIS catalog internal procedures

I'm trying to set up a stored procedure to execute an SSIS package. I want to allow a user to execute only this stored procedure, so I need it to run as a different user. I found this article which ...
2
votes
3answers
229 views

Run multiple stored procedures in sequence

I have a stored procedure written in T-SQL (SQL Server 2008 R2). It is a master procedure that essentially call multiple other subprocedures in sequence. The call and error handling is identical for ...
0
votes
1answer
22 views

Call to Function in Oracle says it's not declared

When I call the next stored-function in oracle: CREATE OR REPLACE FUNCTION SOLICITAR_DISPONIBILIDAD (parqueo IN NUMBER, semestre IN NUMBER, año IN NUMBER, jornada IN NUMBER) RETURN INTEGER IS ...
0
votes
0answers
22 views

User or Role is Missing

I'm using a stored procedure in oracle 11g r2 but when I call it from java it says that I'm missing the user or role name when it executes this sql sentence: no_dml := 'CREATE USER ' || no_carne || ' ...
0
votes
1answer
33 views

Executing try catch block in mysql stored procedure

In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure . I want my select query to be in try block and if it executes correctly I want other query to ...
1
vote
1answer
71 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
2
votes
2answers
144 views

Select from stored procedure

Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute ...
1
vote
1answer
45 views

MySQL: IF statement check is a table exists

I run in the following issue and can't figure out how to resolve it. delimiter # create procedure pr1() begin declare v1 double default 0; start transaction; ...
0
votes
1answer
23 views

How can I get all rows of data not included in a TVP

I am trying to do the following: declare @domains DomainNames; declare @CompanyId int declare @CompanyName varchar(255); declare @deletedList DomainNames; set @CompanyId = 239; set @CompanyName = ...
1
vote
1answer
36 views

How can I get a local variable that's a list in a stored procedure?

I have some code in C# I'm trying to move to a stored procedure - Sql Azure does not like multiple calls. So everything needs to get moved to a proc. Here's the C# code: List<string> ...
0
votes
0answers
29 views

Service Broker received an error message on this conversation

I will first have to say, I am still trying to wrap my brain around all the components of the Service Broker and how they work together, so it's very likely I'm missing something obvious... it's just ...
0
votes
0answers
19 views

How to pass ouput parameter values in mysql procedure

I am new with mysql and i getting issues at time of using output parameters in mysql procedure. Scenario: I have two tables MyProcedure code: DELIMITER $$ CREATE ...
0
votes
2answers
40 views

How can i branch based on a columns value in oracle?

I am trying to have a dynamic inner join..on in an Oracle database. For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for ...
0
votes
0answers
12 views

Validate hierarchy of two tables

I have two tables in my mysql database: Questions question_id Answers answer_id parent_question_id child_question_id Basically, I'd like to pass in a question_id, and have all child ...
0
votes
2answers
89 views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...
1
vote
1answer
63 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
0
votes
1answer
74 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
-1
votes
1answer
57 views

Very Slow MySql Stored Procedure

I'm working with a web app running on MySQL. It uses stored procedures for all database-related tasks. The original web app and the database were designed by another developer; I'm only maintaining it ...
0
votes
1answer
16 views

Is there a benefit to using FOR SHARE in this PostgreSql simple select?

In the following PgSql stored function: CREATE OR REPLACE FUNCTION get_offer_from_id( offer_id bigint) RETURNS json AS $$ DECLARE res "entity_data_result"; r "get_offer_data"; ...
0
votes
0answers
21 views

Using 2 order by in procedure

I need to use 2 order by in my procedure. When I use only the second order by is working and not the first one. What mistake am I doing? Here is my procedure BEGIN SELECT sd.ShopID, ...
3
votes
1answer
126 views

When to use With Recompile

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc.. Sometimes there are cases that it may help, but OPTION(RECOMPILE) ...
0
votes
2answers
46 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
-1
votes
1answer
34 views

Issues with procedures, setting multiple values [closed]

I am trying to set multiple values but I am only able to set one value. This code fails: alter procedure modifyvalue @ID int, @flight varchar(6), @destination char(10) as begin update ...
0
votes
2answers
80 views

What do cursors do internally?

I was optimizing a stored procedure written in cursors (DECLARE cur_name CURSOR... The client saw this SP ran for more than a day so, my manager assigned this to me. Upon searching, I used table ...
0
votes
1answer
25 views

I cant figure out the error probably with the date field in this mysql stored procedure

This is my empDetails table I am trying to create a stored procedure on. mysql> describe empDetails; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
1
vote
2answers
98 views

Changing compatibility level of database causes performance improvement of stored procedure

We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has ...
0
votes
2answers
75 views

How to use same stored procedure for different tables and avoid using dynamic sql

I have quite a few tables which contain some common columns (same name and datatype). On these columns I need to perform the same operations and I would like to do that via a stored procedure. However ...
0
votes
0answers
31 views

Replication overview

My boss wants me to elaborate a comprehensive report about synchronisation/replication. I know that replication can be done with the DBMS (we are using SQL Server 2012), but he wants something manual ...
0
votes
1answer
50 views

How to retrieve stored procedures from raw data

I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't ...
0
votes
0answers
18 views

MYSQL Stored Procedure Problem. Need Help

MySQL Stored procedure: Delimiter $$ DROP PROCEDURE IF EXISTS spGetCompanyNames $$ Create procedure spGetCompanyNames (IN Employer nvarchar(75), Out Employer_Namee nVarchar(75)) begin Select ...
0
votes
0answers
52 views

prevent sql injection inside stored procedure

I finally found a solution for the following question: reading input parameter values in stored procedures However I'm not sure, whether this solution is resistant to SQL injections. I'm using ...
1
vote
1answer
62 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
1answer
77 views

reading input parameter values in stored procedures

I'm using the current version of MariaDB 10.0.12 . I created a stored procedure as specified in the accepted answer for the following question: ...
0
votes
1answer
339 views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
1answer
141 views

How to reduce the run time of stored procedure by getting rid of cursors?

I have a task of reducing the run time of a stored procedure that runs for hours. Description of task: The cursor takes a set of values @Master and @Slave obtained from a query(5000+ rows), finds ...
0
votes
2answers
69 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
3answers
53 views

List all tables that a specific login has permissions [duplicate]

This might be repetitive, but I haven't found a solution that serves my purpose 100%. What I'd like to achieve is to have a script (or SP) that'll list all tables that a user has permissions on. Any ...
1
vote
1answer
133 views

PostgreSQL 9.3: Primary key violated by a trigger INSERT

My problem Consider a table t with many frequent updates from users, from which only the last few are relevant. In order to keep the table size reasonable, whenever a new row is inserted old rows ...
0
votes
1answer
192 views

How to write a procedure /job to kill all session running for more than 5mins in Oracle?

How to write a procedure /job to kill all session running for more than 5mins in Oracle? So, I query v$session and find out that for a particular user, program pair if the session has been active for ...
0
votes
1answer
94 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
0
votes
2answers
63 views

Does anyone use modules in DB2 LUW?

Our environment is currently DB2 9.7 Fix Pack 4 on AIX. I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then ...
0
votes
1answer
43 views

RaiseError on SQL Server stored procedures

ALTER PROCEDURE sp1 as begin IF (@PersonId=0) RAISERROR(N'Error already raised. See previous errors.', 16, 1); break end I want something like the following. If no value in the variable, ...
5
votes
1answer
323 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...