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
2answers
663 views

How to create a stored procedure with several delete in(select) statements?

Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the ...
0
votes
1answer
83 views

how to Update Value in local tabel when job Finished

I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs ...
0
votes
1answer
165 views

Stored procedure not working

I am creating a procedure in a new database. There is no trigger on any table and no procedures exist. I am getting this error: ERROR 1303 (2F003): Can't create a PROCEDURE from within another ...
0
votes
1answer
143 views

Postgres - Schema information within Trigger?

Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x). Reviewing patterns for the following problem: I ...
0
votes
1answer
163 views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
1answer
36 views

Special syntax sth.name() in the CREATE FUNCTION statement, what does it mean?

In this SO question about stored procedures in plpgsql, the stored procedure look like sth.name(). I don't know what is the meaning of the prefix sth. For example: create or replace function ...
0
votes
1answer
257 views

Cannot use python3 as stored procedure language in posgresql database

I want to have python3 in my postgresql database for writing stored procedures. Being in the psql client, when I enter the command create extension plpython3u I get the error: couldn't open ...
0
votes
1answer
34 views

Two dichotomy views of a table, and means to express this as a stored procudure?

There is a table events. Some relatively complex logic determines for each event whether it is archived. Then I need two more views: All archived events and all not archived events. How to express ...
0
votes
2answers
2k views

Use of CASE statement in a MySQL query

I have 3 tables: TASK (id_task (PRIMARY KEY), name) PROJECT (id_project (PRIMARY KEY), name) PROJECT_HAS_TASK (id_project, task_id) PROJECT_HAS_TASK is the (n,m) relation between PROJECT and TASK. ...
0
votes
2answers
117 views

Understanding the use of Sql command parameters

I am using MySql database and I have a stored procedure to insert data, like this- CREATE DEFINER=`root`@`localhost` PROCEDURE `adduser`( IN id varchar(10), IN pwd varchar(10), IN fname ...
0
votes
1answer
352 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
2answers
71 views

SQL Server Stored Procs not parameterized, leading to high compilations?

We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL. However, I noticed that in SQL Profiler our calls look like: exec cid.dbo.uspCIDLog ...
0
votes
1answer
111 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...
0
votes
1answer
3k 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
1answer
367 views

How to start and then stop one cyclic process

I have to start cyclic process in a stored procedure and then have to stop it after some time (after 12-24 hours). Stored procedure's work should not be started twice. SQL Server client (caller) ...
0
votes
1answer
41 views

Is this application creating stored procedures on the fly? [duplicate]

We are running a transaction program that uses MS SQL Server. Lately as our number of users and locations have grown, speed/response seems to be an issue. When looking at *sys.dm_exec_sql_text*, I ...
0
votes
0answers
37 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
0
votes
0answers
43 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
1answer
25 views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
0
votes
0answers
38 views

Dynamic Query creation by looping through Query-Result?

after my first question regarding pivoting a table (here) got great answers I'm now struggling with how to adapt this to variable table sizes. I need something like a "FOR EACH IN" which creates a ...
0
votes
3answers
56 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
2answers
34 views

How to fill a tabe automatically with a procedure in MySQL

I have a very big table for users as below: CREATE TABLE `karbar` ( `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, ...
0
votes
0answers
40 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
91 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
81 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
1answer
87 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
42 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
0answers
174 views

Does Netezza have functionality that is equivalent to a trigger?

Does Netezza have a feature that is similar to a trigger in SQL Server or Oracle? Right now we are using Netezza as a data warehousing database, but we would like to perform some audits and ...
0
votes
0answers
82 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
81 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
33 views

Does MySQL have built in support for procedural programming languages made in Python, PHP, Java, etc?

If I understand correctly, Postgresql has built in support so you can write procedures in different languages. But when I look at MySQL I only see a native language, it's own thing. Does MySQL have ...
0
votes
0answers
27 views

how to use information table's result in a select statement

SELECT COLUMN_NAME into columnname_date FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='syslog_data' AND `TABLE_NAME`='syslogs_index_1' AND ordinal_position = forder_date+2; When I ...
0
votes
0answers
33 views

Permissions different between test and live databases

We have 2 database Servers - Test and Live. We restore the live database back to test periodically. We have noticed that a SQL User has access to execute a stored proc on live but not on the test ...
0
votes
0answers
20 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
0answers
71 views

coercibility and collations

I have a similar situation as described here - http://forums.mysql.com/read.php?103,63845,63845; basically If I run the query in this SP directly, it executes fine: However, if I execute the ...
0
votes
1answer
473 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
1answer
226 views

MySQL stored procedure create database by month

I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to Query main database and move records by month to another database located on the same system. This ...
0
votes
0answers
82 views

MySQL GIS - point in polygon storedproc too slow

My server is on a shared host which is running MySql version 5.5.30-30.2 - Percona Server (GPL), Release rel30.2, Revision 509 (taken from phpmyadmin). I have a table containing 10,023 records ...
0
votes
0answers
54 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 ...
0
votes
0answers
22 views

developing procedure in sql for global use

what type of complex procedure can i develop in SQL which will make others work easy as well , like in Java , i had developed a program for FTP so that anyone can import this program and can use it ...
0
votes
0answers
119 views

DBA_SCHEDULER_Job is not giving any result

I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering. select job_name,status from dba_scheduler_jobs; ...
0
votes
0answers
151 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
2answers
259 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 ...
0
votes
1answer
302 views

How do I check if a stored procedure is defined in MySQL

When I do : select routine_definition from information_schema.routines where routine_name = 'sp_name'; it returns a row, but the routine_definition column is NULL. Does this mean that this Stored ...
0
votes
0answers
133 views

How to load XML data into an oracle db table ?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? I mean, Can we write a Stored Procedure to accomplish the same in a more efficient way with the best performance ? ...
0
votes
1answer
275 views

Need a stored procedure to check and update row when it does not already exists.

Need a stored procedure that will check and then update the rows (with username & email) except the id (auto incremented with a unique one). For example, if id = 5 has, the username abc and the ...
0
votes
1answer
724 views

Is there a way to skip errors in procedures and resuming procedure

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again. ...
0
votes
0answers
116 views

how do i bulk copy a huge amount of data and keep the same table updated through stored procedure in SQL SERVER [duplicate]

Possible Duplicate: How to move data from big table In my project i need to fetch datas from different tables in a data base and create a new table with the same colmn names for all those ...
-1
votes
1answer
29 views

prepare stmt is not working

set @sqlt = concat("select", @rowname_date," from syslog_data.syslogs_index_1 "); prepare stmt from @sqlt; execute stmt; deallocate prepare stmt; gives me a eeror 1064 in the prepare statement? Any ...
-1
votes
1answer
36 views

delete in stored procedure [closed]

i am write this stored procedure to delete one row DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT) BEGIN DELETE FROM students WHERE ...