Questions tagged [plsql]
Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.
596
questions
50
votes
6
answers
161k
views
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
In SQL queries, we do use Group by clause to apply aggregate functions.
But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.
18
votes
6
answers
38k
views
Is Oracle free to download?
I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free?
I found this link: ...
17
votes
3
answers
15k
views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
15
votes
5
answers
94k
views
PL/SQL: best way to count elements in an array?
Given this:
DECLARE
TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY;
V_COUNT INTEGER;
I would like to do:
BEGIN
-- ... some code filling the MY_ARRAY array
...
14
votes
1
answer
107k
views
PLS-00306 Error: How to find the wrong argument?
PLS-00306: wrong number or types of arguments in call to 'string'
Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram ...
13
votes
1
answer
16k
views
What are the benefits of packages over standalone procedures and functions?
Looking for a canonical answer to the question of why packages should be preferred in an Oracle database over stand alone procedures and functions.
12
votes
2
answers
82k
views
What are the causes and solutions for mutating table errors?
I understand mutating table errors are caused by a design flaw or problematic query.
An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
12
votes
4
answers
47k
views
Why use explicit cursors instead of regular loops?
I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:
for i in (select * from STUDENTS) ...
11
votes
2
answers
1k
views
Where to start with Oracle?
This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL.
You may find your question has been closed as a ...
11
votes
3
answers
7k
views
How can I ensure that only one copy of a procedure is running in Oracle?
We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method ...
11
votes
3
answers
38k
views
Why can't we write ddl statement directly into the PL/SQL block
Why can't we write ddl statements directly in PL/SQL block, for example when i write
CREATE OR REPLACE PROCEDURE test IS
BEGIN
truncate table table_name; // error
END test;
/
But,
CREATE OR ...
8
votes
2
answers
111k
views
Writing a simple SELECT Stored Procedure in Oracle PL/SQL
This is my SQL Server Stored Procedure which is below:
CREATE PROCEDURE passenger_details
AS
BEGIN
SELECT Full_Name, Age, Nationality, Category, Airline_Name, Class_Type
FROM Passenger, Ticket, ...
8
votes
4
answers
14k
views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
7
votes
4
answers
2k
views
How to port LTRIM from Oracle to SQL Server?
Moving an application from Oracle to SQL Server, I have this pseudo Oracle PL/SQL:
SELECT LTRIM(MyColumn, '-, ') FROM MyTable
I.e. I'm using Oracle's LTRIM with a second argument, specifying the ...
7
votes
3
answers
16k
views
Using the same sequence twice in a PL/SQL statement
I wrote the snippet of PL/SQL today:
declare
first_id number;
second_id number;
begin
insert into table (sort_nr, text_id, unit_id) values(...,
table_seq.nextval, table_seq.nextval) returning ...
7
votes
3
answers
38k
views
Passing parameters to a procedure executed by DBMS_SCHEDULER
I have a scheduler program that runs a stored procedure that requires input parameters.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/...
7
votes
1
answer
6k
views
Slow join behaviour with 'or' in predicate
I'm faced with a situation which I can't understand and overcome.
In short we have left-join query like:
select from a
left join b on a.key1=b.key1 or a.key1=b.key2
This works VERY slowly, while ...
7
votes
2
answers
2k
views
What is an Oracle ASSEMBLY?
What is an Oracle ASSEMBLY, in the context of Oracle SQL?
It's listed in GV$SQLCOMMAND. Although GV$SQLCOMMAND is not a perfect guide to "real" SQL statements - for example it has UPSERT instead of ...
7
votes
1
answer
10k
views
Initializing Associative Array in PL/SQL
Using PL/SQL, suppose we've declared a nested table type and its instance as follows:
type nested_tab is table of pls_integer;
tab nested_tab;.
We are then able to initialize our nested table as ...
6
votes
2
answers
27k
views
How to check if the table has rows?
I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error:
set serveroutput on SIZE 1000000;
IF EXISTS (select * from my_table)
...
6
votes
2
answers
177
views
Selecting from random amount of tables with identical structure
I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software.
For simplicity let's say this software stores address ...
6
votes
2
answers
12k
views
What does 'nls' in nls_date_format stand for?
For example:
alter session set nls_date_format = 'DD-MON-YYYY hh24:mi:ss';
changes the date format for the session.
What is nls here?
6
votes
1
answer
41k
views
How can I use an array variable inside the IN operator for Oracle SQL?
I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();.
I insert some varchars into it, and then attempt to use it with an IN ...
6
votes
1
answer
182k
views
How to run procedure in Oracle SQL developer
Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure
-- start the script
SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON
CREATE OR REPLACE PACKAGE BODY ...
6
votes
3
answers
2k
views
Oracle Advanced Queuing LIFO
With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
6
votes
2
answers
3k
views
Identify which parameters were passed to a procedure (Oracle)
Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
6
votes
1
answer
374
views
DBA's Scope in datawarehousing Projects
As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?
I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used ...
6
votes
2
answers
32k
views
SELECT from Function that return CURSOR
I would like to select from a function that return a SYS_REFCURSOR Type value.
for example :
CREATE OR REPLACE FUNCTION my_funtion (
my_param IN VARCHAR2)
RETURN SYS_REFCURSOR
IS
...
6
votes
1
answer
89k
views
How to execute stored procedure with output parameter in Oracle PL/SQL?
This is the table I have created:
CREATE TABLE Toy
(Toy_No NUMBER PRIMARY KEY,
Toy_Name VARCHAR(30) NOT NULL
);
This is the sequence I have created:
CREATE SEQUENCE toy_seq
MINVALUE 1
START WITH ...
6
votes
2
answers
81k
views
How to store single result in variable and reuse it in a query (Oracle)?
I have a query like
SELECT id FROM xyz WHERE ...;
which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script....
6
votes
1
answer
2k
views
Using sys.diana and sys.pidl to parse PL/SQL
It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
6
votes
2
answers
2k
views
Where can I find a syntax reference for Oracle SQL Developer's "Generate DB Doc" function?
Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
6
votes
3
answers
4k
views
How do I delete a file that has been locked by UTL_FILE?
I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type).
Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
6
votes
1
answer
1k
views
Package state not re-intialized on raise_application_error
When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of ...
5
votes
3
answers
4k
views
Alternatives to an INSERT Trigger
Looking for an example code in Oracle to do the same. i.e.
Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?
...
5
votes
2
answers
6k
views
PL/SQL function to receive a number and return its binary format
I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for ...
5
votes
2
answers
13k
views
Oracle insert max from two date
I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like
Insert into MYTABLE (DATE_INSERT)
VALUE (MAX(...
5
votes
2
answers
34k
views
Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS
Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:
Exec DBMS_STATS....
5
votes
1
answer
11k
views
How to qualify variable inside trigger body (PL/SQL )?
I can explicitly qualify variable in the body of stored procedure or function :
create or replace
PROCEDURE ptest AS
int_val INT;
BEGIN
ptest.int_val:=0;
END;
/
How to do the same inside trigger ?...
5
votes
2
answers
52k
views
Commit and rollback in Oracle stored procedure
In my Oracle stored procedure, I have multiple insert and update statements like below:
create or replace PROCEDURE SPTest
AS
BEGIN
insert into emptest(empid,empname,deptno)
(1,'ravi',10);
...
5
votes
2
answers
110k
views
How do I use the IF...ELSE condition in a WHERE clause?
I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT.
Should this work?
CREATE OR REPLACE package body If_Else_Pack IS
PROCEDURE Moving
...
5
votes
1
answer
279
views
Retrieve Indices of Associative Array as Collection
In PL/SQL, suppose I have some associative array defined as follows:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
a_arr a_arr_t;
I then, sparsely, populate the ...
5
votes
4
answers
106k
views
grant all privileges of all tables in database to a user
I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that ...
5
votes
3
answers
13k
views
How to separate out individual sql statements from Oracle SQL script for execution from java code
I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code.
I am reading this sql script from a .sql file and the script contains statements such as create table, ...
5
votes
1
answer
15k
views
Oracle 11: Change global index to local on huge production table
I have big partitioned table with global index. My case is drop old data from archived partitions. I can't simply drop partition because i have global index.
So I have idea to use INVISIBLE local ...
4
votes
2
answers
50k
views
How to call a stored procedure in an Oracle Scheduler job
I want to call a stored procedure in an Oracle Scheduler job. My procedure and job are shown below:
Stored procedure
create or replace PROCEDURE emp_test ...
4
votes
2
answers
39k
views
ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes
I got that error when I tried to execute this query:
select *
from lc_history2 lc
where lc.view_name <> 'LC Main'
and lc.modifiedtime = (select max(modifiedtime) from lc_history2 l where lc.lc ...
4
votes
2
answers
608
views
Is there a bug with PL/Scope in combination with associative arrays?
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
...
4
votes
3
answers
5k
views
how to change a user password while checking the current password in oracle?
In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command:
alter user user_name ...
4
votes
2
answers
295
views
Identify columns that allow NULL but don't contain NULL
I'm working with a legacy database that never had any FOREIGN KEYs defined and not very many NOT NULL constraints, even where they are obviously applicable. I've been manually adding these in where ...