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
0answers
64 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
0
votes
0answers
45 views

Statement ignored - PL/SQL Procedure

create or replace PROCEDURE UNIQUE_MATCH ( PHYSICIANID IN NUMBER , FNAME IN VARCHAR2 , LNAME IN VARCHAR2 , POSTALCODE IN VARCHAR2 , SOURCETABLE IN VARCHAR2 ) AS ALREADY_HAS NUMBER; BEGIN ...
0
votes
0answers
132 views

MySql stored procedures are not atomic (JDBC)?

I have a stored procedure with one UPDATE and one INSERT in its body. I call the SP with a single executeQuery(...) wrapped within a try-with-resources. The DB connection is set (by default) to ...
0
votes
0answers
192 views

How can i add index in MYSQL stored procedure?

I can not use alter table () on my stored procedure i don't have a permission to do that in the big database data_pro. How can i use index in my stored procedure for my 2 fields in where condition? ...
0
votes
0answers
31 views

How to Create a Recommended Test Planning in SQL Server

Goal: Make lots of different test for a specific stored procedure named 'sp_test' in order to review if you are retrieving the same data based on data input. Purpose: If you have made lots of ...
0
votes
1answer
66 views

trouble creating a stored procedure to print out values for each day of week

I am having difficulty in understanding how to get this working correctly. I am trying to have create a stored procedure that is run automatically on a sunday and will get the data for the previous ...
0
votes
3answers
158 views

Merge MySQL tables without data repetition

My situation is this: I have several DIFFERENT tables in my MySQL database with the same name columns. Such tables can contain (or not) the same data. What I need to do is a kind of "merge" between ...
0
votes
0answers
188 views

Data Mock up for my table

I want to mock up data for a table. So I need to generate a million sample records for the table? Table structure is like this: CREATE TABLE Employees( EmployeeID int NOT NULL, FirstName ...
0
votes
0answers
29 views

How to Customized the Where Statement based on UDT

Goal: Display customized data based on customized where statement from UDT. The UDT is a parameter inside of a stored procedure. Problem: A parameter from a stored procedure is @communication ...
0
votes
0answers
89 views

How to reset @@IDENTITY value inside a Sybase SP

Is there any way to reset the @@IDENTITY value inside a Sybase SP? I'm writing a SP which involves a transaction, so everytime I do an insert, to check data was correctly inserted, I assign ...
0
votes
1answer
17 views

Help with stored procedure and update table

I am trying to write a stored procedure that has parameters and updates a table with the supplied title and middleName for the person having the businessEnityID. Create Procedure sp_PersonEditor ...
0
votes
0answers
32 views

Which technologies should I consider using for creating a calculated view?

I use SQLServer, but I suppose this question is general enough for any type of database. Suppose I wish to build an application that allows people to rate geographic regions on where they would like ...
0
votes
0answers
77 views

How can I replace the cursor in the below query?

I have this below query which calls a stored procedure that takes only 1 item's attributes at a time. But because of performance problems we are required to remove the cursor. How can I replace the ...
0
votes
0answers
203 views

Hide Inconsequential Result Sets From Stored Proc

How does one mask resultsets in multi-layered stored proc calls? Due to stored proc reuse, I have an issue where a top level stored procedure calls multiple stored procedures (directly) and in-tern ...
0
votes
1answer
97 views

SQL Server stored procedure to insert/ update which has date

The source which has date in dd-MMM-yy format , have to convert it yyyy-mm-dd hh:mm:ss:SS and insert/update into the Database using stored procedure.New to stored procedure I am managed to write a ...
0
votes
2answers
201 views

Logging the Execution times of dynamically constructed mysql stored procedures

I have a MySQL Server. The MySQL server contains a huge network data collection. The client API's will connect to the MySQL server and execute some dynamically created stored procedures and delete ...
0
votes
0answers
326 views

Mysql cursor alternative / Optimization - each row updates are too slow

I'm looking to optimize my cursor based update or actually replacing it... Situation We're carrying out promotional campaigns and I'd like to track user activity per campaign. Logic Each campaign ...
0
votes
1answer
249 views

Access denied to user@localhost

As a root user I did this: GRANT DELETE, SELECT, INSERT, UPDATE, EXECUTE ON dbName.* TO 'user'@'localhost' IDENTIFIED BY 'apassword'; In 'dbName' I created a procedure 'proc_name' with definer set ...
0
votes
0answers
82 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
0
votes
1answer
71 views

“show procedure status” displaying the non existing databases stored procedures in mysql server

i have taken a mysql server backup using mysqldump and imported the backup file in another mysql server.Import failed because of disk full issue. Only 11 databases are created out of 15 . when i issue ...
0
votes
1answer
76 views

No records in Mysql.func table even though there are stored functions in server

When i am creating a user defined function in one of my mysql server, it's not creating any row in mysql.func table . Even though i have multiple functions in my mysql server , this table is empty . ...
0
votes
0answers
46 views

MySQL: routines removed but still in information_schema

I have a MySQL server with a lot of stored procedures (around 150). When I tried to list the routines with PHPMyAdmin it requires a lot (around 2-3 minutes) in order to load all of them. So I started ...
0
votes
1answer
93 views

Is there any way to perform atomic updates of stored procedures in MySQL?

I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588). In our project ...
0
votes
2answers
2k views

Update MySQL Stored Procedure Definers on shared environment

I am accessing a MYSQL database through PHPMyADMIN. My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL ...
0
votes
0answers
386 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0
votes
0answers
22 views

i always get o for my result.why is it so?

CREATE DEFINER=`root`@`localhost` PROCEDURE `roomAvailbility`( IN checkindate date, IN checkoutdate date, out roomtype char(20)) BEGIN select count(*) INTO roomtype from room_1401557b ...
0
votes
1answer
457 views

create multi valued variable inside mysql stored procedure

I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement. CREATE PROCEDURE ...
0
votes
1answer
281 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 ...
0
votes
1answer
342 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$$ ...
0
votes
1answer
46 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
52 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
0answers
479 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
1answer
169 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 ...
0
votes
0answers
35 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
411 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
3answers
709 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 ...
0
votes
0answers
653 views

Can a stored procedure OUTPUT parameter return a table as output?

I would like to know whether we can use a stored procedure's OUTPUT parameter to a return table as result set? I would appreciate any practical example using it OUTPUT parameters in stored ...
0
votes
0answers
5k views

Multiple SELECT subqueries in an INSERT statement in a stored procedure

So I had the following (genericized), which works just fine for me (barring errors in making the example generic) : DELIMITER // CREATE PROCEDURE stored_proc(IN val1 INT, IN date1 DATETIME, IN date2 ...
0
votes
0answers
404 views

Create Drop Temporary Table very slow vs Truncate

I've got this stored Procedure that is called by an event that runs every 1 minute. The stored procedure selects some data from one device and puts it in a temporary table. Than it picks up other ...
0
votes
0answers
91 views

Sorting the view on which the stored procedure runs

I want to sort the view on a particular column before my stored procedure runs. Is there a way to do this through the stored procedure? Can anyone show me a MySQL example? This is what the stored ...
0
votes
0answers
548 views

Execution time is different between normal query and query inside a stored procedure

I have following simplified query WITH Report1 AS ( SELECT SUM(t.X), SUM(t.Y) FROM Table1 t WHERE t.Date1 > :dateStart and t.Date1 < :dateFinish ) SELECT * FROM Report1 This query ...
0
votes
3answers
348 views

Inner Join Table (Mapping table)

this is first time i am try mapping table in mysql i write this procedure but not work ? whats wrong ? DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_GetInformation`( ...
0
votes
0answers
59 views

Logical or in oracle PL/SQL

I have this code: declare error1 number; error2 number; error3 number; begin error1 := 1; error2 := 0; error3 := error1 | error2; -- should be error3 = 1 end; But I dont know how to ...
0
votes
0answers
710 views

db2 cannot execute C-function for Reason code 4

I've implemented a function as C code for db2. The library name is called libmyfoo.so and is stored id /usr/local/lib as declared in the External declaration. When I try to execute this function I get ...
0
votes
0answers
487 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
0
votes
0answers
488 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
0
votes
1answer
257 views

Procedure and Automation in Mysql

i have a table with these columns and the values are coming in these tables from a csv file at a regular interval of n minutes ( by cron the load infile command) : ID (PK) status first ...
0
votes
0answers
26 views

How can I prove that the following T-SQL is bad for performance? [duplicate]

I have recently inherited a codebase with a large amount of stored procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
0
votes
1answer
3k views

Stored procedure to handle empty sets

How can I make a stored procedure to print a specific message if an empty set wa returned by the query?
0
votes
0answers
73 views

Procedure after commit

I have an application which periodically will insert large amounts of data, from that data I build a few materialized views (well emulate by creating a table from a view and replacing it with the ...