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.
1
vote
1answer
24 views
Is it possible to define package procedure/function outsize of package body block (for Oracle)?
Is it possible to define package procedure/function outsize of package body block (for Oracle)?
Simplified syntax for defining package body is:
create or replace package body <package_name> IS
...
1
vote
1answer
28 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 ...
3
votes
1answer
637 views
Linked tables versus stored procedure performance in MySQL
Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form).
Option 1 is to ...
1
vote
1answer
1k views
“Procedure: XXXX has an unresolved reference to object XXXX” errors
After using for some time MS SQL Server Management Studio for creating and editing databases I tried to use Visual Studio 2010 "SQL Server 2008 Database Project" for this.
I used the SQL 2008 Wizard ...
3
votes
1answer
34 views
SP with Encryption Read/Writes Stats
If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
0
votes
1answer
39 views
Stored procedure with and without preparing statement
I have 2 stored procedures:
CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
...
2
votes
2answers
67 views
The multi-part identifier “xxx” could not be bound
The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);
HAVING (SELECT COUNT(*) FROM
(
SELECT *
FROM ...
1
vote
1answer
55 views
Custom sp_who/sp_whoUsers
I need to allow a client in a dev DW SQL 2K8R2 environment, to view and kill processes, but I do not want to grant VIEW SERVER STATE to this person (he's a former sql dba and is considered a potential ...
0
votes
0answers
24 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 ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
21
votes
4answers
1k views
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database 'sp_something'. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
0
votes
0answers
21 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 ...
-1
votes
1answer
64 views
Use select query in stored procedure
I have some doubt about stored procedure.
Using select query inside the stored procedure is efficent or using select query in front end. Which is take less time and give the result is fast Or if have ...
2
votes
2answers
100 views
EMS SQL manager permission problem
I have a strange problem with EMS SQL Manager.
I'm using MySQL 5.5 with linux servers. One of my developers need to edit store procedure, so I grant him:
GRANT CREATE ROUTINE, ALTER ROUTINE ON ...
0
votes
2answers
39 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 ...
3
votes
2answers
79 views
How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION ...
0
votes
1answer
100 views
MySQL user defined rollback procedure
I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more ...
0
votes
0answers
45 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;
...
1
vote
2answers
56 views
Same Parameter in MySQL Stored Procedure
How do I cache dynamic query from store procedure?
Right now I have created my store procedure like this :
CREATE PROCEDURE usp_MyProcedure (
IN UserID INT,
....
)
BEGIN
SET @sqlQuery = ...
3
votes
0answers
43 views
SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows
So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:
ALTER PROCEDURE ...
2
votes
2answers
78 views
How to create a mysql stored procedure through linux terminal
In mysql console we will use the delimiter command to change the delimiter and it is useful to define the procedures.
I understood the command delimiter for only the mysql client(client side ...
1
vote
1answer
34 views
Trigger not firing when deleting a record from stored procedure MySql
Bare with me, as I'm fairly new to stored procedures / triggers.
I have the following Stored Procedure:
DELIMITER $$
CREATE DEFINER=`username`@`serveraddress` PROCEDURE `TASK_APPROVE`(IN idtask ...
0
votes
1answer
39 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
2answers
105 views
From SQL Server to NuoDB: Common Table Expressions, Stored Procedures and Bulk Uploads
I am currently evaluating NuoDB as a replacement for SQL Server. The application is written in C#, .NET 4.0.
Now I am using features of SQL Server like:
Common Table Expressions (I have a table ...
0
votes
1answer
46 views
PostgreSQL stored function that returns arbitrary resultset
I would like to write a PostgreSQL stored function that essentially behaves like the stored procedures I know and love from MSSQL and MySQL where I can just wrap a query that takes no parameters and ...
1
vote
1answer
67 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 ...
0
votes
1answer
34 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. ...
2
votes
2answers
84 views
Executing a table type parameter stored procedure or function within a select clause
Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
-2
votes
1answer
27 views
Following procedure generates an error and I cannot find the reason [closed]
create or replace procedure buyunits is
mnav number;
mpno varchar2(100);
mtransactioncharge number;
mservicecharge number;
maccno number;
cursor ecursor is
select accno from sipholder where ...
1
vote
1answer
50 views
MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host
We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication.
"'Column count of mysql.proc is ...
0
votes
2answers
57 views
PostgreSQL stored procedure which can return one of two custom rowtypes
I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:
CREATE OR REPLACE FUNCTION ...
2
votes
4answers
6k views
Call a stored procedure from a trigger
I have created a stored procedure in mysql using the following syntax.
DROP PROCEDURE IF EXISTS `sp-set_comment_count`;
DELIMITER $$
CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
-- ...
0
votes
1answer
43 views
Different Results between stored proc and udf
I am getting different results between my stored proc and my SVF.
The jist of of the both of them is that, it queries a remotely linked server, jams those results into a temp table/table variable, ...
5
votes
1answer
82 views
Best practice for copying tables from server to server
Not being a DBA and not having sysadmin privileges to do a backup/restore, would the following be a good solution for copying a set of tables? (I have a linked server from serverA to serverB)
...
0
votes
1answer
66 views
PostgreSQL stored procedure to return rows or empty set
I have stored procedure, which RETURNS SETOF ct_custom_type and inside I do
RETURN QUERY EXECUTE 'some dynamic query'
And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
3
votes
0answers
27 views
PLSQL : DBMS Jobs - parallelization?
I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs?
for rec in select column from table1
loop
execute stored_procedure( rec.column );
end ...
3
votes
1answer
59 views
Is it possible to use Oracle XMLTABLE and %type column type selector?
I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE.
I want to know how can i specify a field type according to a table column type when passing ...
0
votes
1answer
108 views
What is the best way to insert a XML input to an oracle table in stored procedure?
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:
<rowset>
<row>
...
0
votes
0answers
30 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 ...
10
votes
3answers
2k 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
2answers
61 views
How to Drop Tables using a variable in SQL Server?
DETAILS:
I am trying to create a stored procedure where I can pass a variable table name through from MS-Access to tell SQL Server to Drop a table.
WHY?:
In my Access Database I am running a ...
0
votes
1answer
145 views
how to dump a single stored procedure from a database
I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
1
vote
2answers
191 views
Using MySQL EXPLAIN to analyze the performance of a function
You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as ...
0
votes
1answer
120 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 ...
3
votes
5answers
234 views
Stored Procedures under Source Control, best practice
I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
0
votes
1answer
103 views
Error: set_valued function called in context that cannot accept a set. What is it about?
I use Postgresql 9.1, with ubuntu 12.04.
Inspired by Craig's answer to my question Concatenation of setof type or setof record I thought I would go well with using return query, setof record, and a ...
1
vote
1answer
66 views
Concatenation of setof type or setof record
I use Postgresql 9.1 with Ubuntu 12.04.
In a plpgsql function I try to concatenate setof type returned from another function.
the type pair_id_value in question is created with create type ...
0
votes
1answer
25 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 ...
1
vote
1answer
66 views
Correct use of VOLATILE COST (and ROWS) indications in Postgresql stored procedure
While looking at several examples of pl/python and pl/pgsql, I have seen many - but not all - using volatile cost.
ie:
CREATE OR REPLACE FUNCTION my_function()
RETURNS setof record AS
$BODY$
-- ...
