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

3
votes
1answer
49 views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
1
vote
2answers
342 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
39 views

EXEC Multiple procedures in parallel?

SQL Server 2008. I have several stored procedures. I would like to execute them all in parallel, not serially. How can I do this?
0
votes
1answer
57 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
1answer
74 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; ...
2
votes
1answer
20 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
7
votes
2answers
299 views

How can I check for stored procedure consistency across multiples servers

I've got a stored procedure on 10 distinct servers, how can I check across all servers for stored procedure consistency? I'm currently pulling the definition for each server manually, and comparing ...
0
votes
1answer
19 views

Reviewing Stored Procedure template for handling transaction?

I've prepare a template for SQL Server stored procedures which will have Insert, delete, Update transaction. I have a stored procedure to log error in catch block in a table. How can I improve my ...
0
votes
2answers
222 views

AWS RDS MSSQL Extremely Slow compared to EC2 with MSSQL

Background - I built a tool which grabs a lot of data from multiple third party APIs and stores it in a MSSQL DB. Then, I built a visualization to display this data in tabular and chart format. ...
1
vote
1answer
1k views

Creating an SSIS package that uses a stored procedure

We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing ...
3
votes
2answers
412 views

Can anybody tell me what's wrong with this query?

CREATE PROCEDURE Click( in ipaddress varchar(45)) BEGIN DECLARE ex int; SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; IF ex = 0 THEN INSERT INTO clickactivity (IP, ...
0
votes
1answer
70 views

Stored procedure to compare two columns from different tables and make the insert

I have two tables: Project with fields fld_id (integer), fld_allocated_hours (integer), fld_project_id (integer) Timesheet with columns fld_id (integer), fld_allocated_time (integer), ...
0
votes
1answer
37 views

Procedure permission issues with a SQL state query

We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side. ...
-1
votes
0answers
16 views

MySql Store procedures is better for large web application [closed]

i make a large web application using php pear concept and MySql Stored procedures for better performance please tell i am right or not . and also comment about advantage and disadvantage of this ...
2
votes
0answers
91 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
0
votes
1answer
47 views

SQL Server 2012: stored procedures permissions

Is it possible to control permissions on a single stored procedure level in SQL Server 2012? It seems that SQL Server Manager doesn't expose such functionality via GUI. Can somebody advice me how to ...
1
vote
1answer
40 views

stored procedures location influence their performance?

I have a procedure SP_MYPRO that updates data in MYDB1. 1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1? 2) what if the procedure SP_MYPRO only read data, ...
4
votes
2answers
148 views

How to rollback when 3 stored procedures are started from one stored procedure

I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful. If the first stored procedure works fine in the ...
1
vote
1answer
80 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, ...
-1
votes
1answer
26 views

Update table using SELECT in WHERE clause

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON t2.t1id = t1.id LEFT JOIN table3 as t3 ON t3.t1id = t1.ID SET t1.status = 4, t2.status = 2, t3.status = 2, WHERE t2.status = 1 AND t3.StatusID = 1 AND ...
2
votes
4answers
293 views

Saving a variable globally in SQL server it self and use it when required

We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE. So I planned to create a stored ...
0
votes
1answer
777 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
1
vote
1answer
83 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
32 views

Using 2 order by in procedure [closed]

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, ...
1
vote
0answers
26 views

How to access another db from a stored procedure in db2

I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2. ...
0
votes
1answer
163 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
1answer
133 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 ...
2
votes
2answers
150 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 ...
1
vote
1answer
235 views

sp_startpublication_snapshot Parameter(s)

I am creating a stored procedure that: Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up (If specified in the SP's parameter) Creates ...
1
vote
1answer
279 views

Cannot grant execute to stored procedure

When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below: Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5 Arithmetic overflow error ...
0
votes
0answers
14 views

MySQL stored procedure return variable values from seconds query? [migrated]

SET @v1 = ''; SET @v2 = ''; SET @Query = CONCAT('SELECT sum(colName1), sum(colName2) INTO @v1, @v2 FROM tableName WHERE id=1 '); PREPARE stmt FROM @Query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET ...
1
vote
1answer
119 views

Error updating zoned field from SQL Server stored procedure linked server to iSeries

I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned. In my stored ...
4
votes
2answers
1k views

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR ...
4
votes
2answers
111 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 ...
2
votes
2answers
7k views

insufficient privileges while executing oracle stored procedure?

Im getting insufficient privileges error while executing the following oracle stored procedure. Im using Oracle Database 10g Express Edition. CREATE OR REPLACE PROCEDURE sp_update_acounts( ...
2
votes
2answers
158 views

Return several values from a PL/pgSQL procedure - to speed up the query

I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is ...
0
votes
1answer
31 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$$ ...
1
vote
1answer
61 views

WITH RECOMPILE option in SQL Server [closed]

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
376 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...
1
vote
2answers
256 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 ...
0
votes
2answers
49 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
44 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 ...
18
votes
4answers
6k views

PostgreSQL Stored Procedure Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
0
votes
1answer
6k views

Stored Procedure Create Table from Variable with Variable Constraint

I have managed use a store procedure to create a copy of a table with a variable name. But I am struggling to understand how to incorporate a constraint into the stored procedure. The problem: The ...
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 ...
2
votes
3answers
321 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 ...
7
votes
1answer
6k views

Dump only the Stored Procedures in MySQL

I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
0
votes
2answers
4k views

work with json in oracle

Is there an easy way to work with JSON within Oracle? I have a stored procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but ...
0
votes
0answers
24 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
350 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 ...