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
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
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 ...
0
votes
1answer
40 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
...
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
65 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 ...
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
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
59 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
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 ...
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 ...
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 ...
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
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 ...
0
votes
2answers
107 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
36 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
85 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
1answer
44 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 ...
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 ...
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
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 ...
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
109 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
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 ...
0
votes
1answer
121 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
236 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
67 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$
-- ...
0
votes
1answer
85 views
Cannot `create function` in plpython3u, permission denied
As postgres user, I have create extension plpython3u; in my database
then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true
but ...
0
votes
1answer
63 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
0answers
37 views
MySQL Stored Procedure
I am developing a database for an eCommerce website, I have written the stored procedure for the following tables,
user_master,
authentication_master,
country_master,
public_master.
I have ...
0
votes
0answers
57 views
Stored Procedures and Functions in MySQL
I just need to know about Stored Procedures and Functions in MySQL.
While I designing a database for an eCommerce website, I was in a situation to write stored procedure for my database, this is new ...
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 ...
1
vote
1answer
478 views
How to return resultset from MySQL Stored Procedure using prepared statement?
DELIMITER $$
CREATE PROCEDURE List_IL()
BEGIN
DECLARE Project_Number_val VARCHAR( 255 );
DECLARE Temp_List_val VARCHAR(255);
DECLARE Project_List_val VARCHAR(255);
DECLARE FoundCount INT;
...
5
votes
2answers
184 views
Finding stored procedures with missing indexes
How to scan for stored proc that need indexes to be created.
Anyone have any idea? Basically we have stored procedures that run very slowly. I'm wondering if there is some kind of automated script ...
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 ...
4
votes
1answer
142 views
Join To a @Table Variable is running ineficiently
Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
0
votes
1answer
23 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
1answer
69 views
Difference between return next and return record
What is the difference between RETURN NEXT and RETURN RECORD in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD actually does. What is a RECORD type?
