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
1answer
1k views

Create and fill temp table

I have a stored procedure that selects some data and returns it. Now I also want to add a temp table, fill it with data and return it with the stored procedure. I define it as follows: create table ...
0
votes
2answers
729 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
2answers
4k views

Grant permission to execute stored procedure without granting access to underlying table - Oracle

I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else. I see from here how to add execute permissions ...
0
votes
1answer
11k 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
2answers
46 views

Column name or number of supplied values doesn not match table definition

I have created three tables using USE UserDecomAudit GO CREATE TABLE ADUserData ( ADUserID int NOT NULL PRIMARY KEY, Name varchar(50), SamAccountName varchar(10) NOT NULL, DistinguishedName ...
0
votes
1answer
32 views

Unused Postgres sequence values

Observation: I have a serial index on a table in a PostgreSQL instance, and I execute a function that inserts a new record into that table. The function fails (reason not important), and the ...
0
votes
1answer
58 views

When to use stored procedure / UDF? [closed]

I'm confused about usage of SP / UDF. Typically, it is also possible to write code in programs outside of database. So is there any general advice to decide when to use them?
0
votes
1answer
417 views

Procedure doesn't have compatible arguments

Getting an error while invoking my procedure named LOG_MSG_TEST(). Procedure Schema name is ADMINIST. My procedure has one INTEGER input parameter and one VARCHAR output parameter. I am calling ...
0
votes
2answers
2k views

Use event schedule and stored procedure to automatically average every hour MySQL

I have a database in Mysql Workbench about weather that updates every second. It contains temperature and wind speed. This is my database: CREATE TABLE `test`.`new_table` ( `id` INT(10) NOT NULL, ...
0
votes
2answers
320 views

Stored procedure runs forever, but inner query runs fine

If I run exec pr_BuscaLog -2, it will run forever (days, without end). But, if I just copy the query and run it on its own, it runs in seconds. Updating statistics did not help. --pr_BuscaLog -1 ...
0
votes
2answers
387 views

Does anyone use modules in DB2 LUW?

Our environment is currently DB2 9.7 Fix Pack 4 on AIX. I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then ...
0
votes
2answers
508 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
2answers
5k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
0
votes
2answers
16k 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
1answer
689 views

Stored procedure arguments get immediately truncated

I have a stored procedure with a single SELECT statement inside: DROP PROCEDURE `pos_neg_test`// CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP) BEGIN SELECT ...
0
votes
1answer
79 views

Using of OPTIMIZE FOR hints in a sql query

as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing. For such a stored procedure: SELECT * FROM Tabele WHERE param=@param1 We have two parameter S1 with high selectivity and s2 with low ...
0
votes
1answer
38 views

Can't cast a SPROC parameter?

I have several sprocs that take a date/time input, one of which takes a date only in the form of an nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the sproc I'm ...
0
votes
2answers
505 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
1answer
2k views

“Access denied for user” error when creating a procedure

When I request my database server (MySQL), with "show grants for current_user" I notice that I have the grant to execute procedure: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK ...
0
votes
1answer
6k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
0
votes
1answer
728 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
2answers
174 views

looping through a table type variable

I have a function that has one parameter of a table type, i want to loop through all the fields and return the column that has a null or empty value. CREATE OR REPLACE FUNCTION ...
0
votes
2answers
5k views

How can I change collation for all tables and columns in MySQL database?

I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, ...
0
votes
2answers
46 views

Create a copy of a SQL Server Job

Using T-SQL or Powershell, how can I create a stored procedure that performs the following: Copy a SQL Server Agent Job to another backup Job on the same server (with a new name) Disable the backup ...
0
votes
1answer
46 views

Innovative solution required - Remotely create stored procedure

I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually. All servers can access the same network share if I want to go ...
0
votes
1answer
53 views

Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements [closed]

I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and ...
0
votes
1answer
131 views

Stored Procedure produce data for a range of dates

I have this table: PRJ_ID | START | FINISH xxxx | 2015-01-01 | 2016-01-01 I have two separates date start_range and end_range. I would like to produce the following output using a stored ...
0
votes
1answer
71 views

Use PL/pgSQL in PostgreSQL outside of a stored procedure or function?

I'd really like to use an IF, ELSE construct and execute multiple statements within the IF and ELSE blocks, but I don't wish to save my commands to the database as a stored procedure or function. Can ...
0
votes
3answers
505 views

Executing a script via Powershell possibility

Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it ...
0
votes
1answer
60 views

Getting error while installing log4db2 utility

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be ...
0
votes
2answers
210 views

Executing stored procedures asynchronously using tasks in SSIS

I have a stored procedure which is calling 20-30 stored procedures. I need these stored procedures to run asynchronously. From what I have read, it seems like executing those stored procedures using ...
0
votes
1answer
325 views

PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

We are using a software that needs to request the max primary key column value to add a new row into the table that was requested. I know that there are some alternatives like: Sequences, and GUIDs ...
0
votes
1answer
629 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
524 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 ...
0
votes
1answer
104 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 ...
0
votes
2answers
97 views

How can i branch based on a columns value in oracle?

I am trying to have a dynamic inner join..on in an Oracle database. For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for ...
0
votes
2answers
57 views

Triggers not reflecting on procedure

I have a trigger create trigger flightinput2 on reservation after insert as begin update reservation set Departure_Airport='LDN', Arrival_Airport='KUL' where Flight_Code='EM0088' end ...
0
votes
1answer
4k views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
2answers
6k views

How to return a result set from oracle procedure ?

How can i return a result set from a stored procedure in oracle ? suppose i want to return a table from a simple select statement such as this : Select * from tblTest I tried this but this seems ...
0
votes
2answers
3k views

create a stored procedure if it doesnt exist using a ddl in db2

My requirements are, I want to create a ddl script which will check if a stored procedure exists on DB, if yes then drop it and recreate it. What I tried is, IF EXISTS (select procname into ...
0
votes
2answers
1k views

SQL Log Messages - FILESTREAM and xp_cmdshell

I was perusing the SQL logs today and found the following: FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'. Configuration option ...
0
votes
1answer
490 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
0
votes
1answer
10k views

Execute stored procedure in another server from sql agent job

I am trying execute stored procedure in remote server in sql agent job using sqlserver 2008 r2 select * from openquery([1.1.1.1],'exec [DB_Name].[dbo].[sp_ProcName]') Or EXEC ...
0
votes
1answer
114 views

An SQL Server 2008 R2 based program fails when updating stored procedures?

I'm trying to update MIP software and one of the "companies" fails while it is updating a stored procedure. Is there any place in SQL Server 2008 R2 / Windows where I could see an error message ...
0
votes
1answer
423 views

Memory is not released after the execution stored procedure followed by a process end

I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after ...
0
votes
3answers
2k views

SQL Server, how to execute a job against all databases on server?

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan. I have a stored procedure that i'd like to run on these databases, but ...
0
votes
3answers
3k views

MySQL Continue Handler Problems inside a Function

I am in process of writing a simple MySQL function that looks up a value in a table and returns it. In case there is not value found it returns null. However, even with a continue handler defined I ...
0
votes
1answer
200 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, ...
0
votes
1answer
2k 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?
0
votes
1answer
2k views

Calling stored procedure with select and update statement

I have a requirement to return a batch of records and then mark the batch complete. I was thinking to acheive this with a select and the update. Something like create procedure testproc as select ...