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.

Filter by
Sorted by
Tagged with
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.
ursitesion's user avatar
  • 2,071
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: ...
Entity's user avatar
  • 191
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 ...
Lazer's user avatar
  • 3,351
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 ...
Maxime Pacary's user avatar
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 ...
Stephan Schielke's user avatar
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.
Leigh Riffel's user avatar
  • 23.9k
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 ...
parmanand's user avatar
  • 427
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) ...
ini's user avatar
  • 347
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 ...
rfusca's user avatar
  • 1,569
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 ...
Ravi's user avatar
  • 1,545
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, ...
stranger's user avatar
  • 193
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 ...
Michael-O's user avatar
  • 510
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 ...
Uwe Keim's user avatar
  • 887
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 ...
Michael-O's user avatar
  • 510
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; /...
Herman Plani Ginting's user avatar
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 ...
smwoody's user avatar
  • 73
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 ...
Jon Heller's user avatar
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 ...
Isaac Kleinman's user avatar
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) ...
Anant Tyagi's user avatar
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 ...
Marged's user avatar
  • 151
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?
Lazer's user avatar
  • 3,351
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 ...
Honinbo Shusaku's user avatar
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 ...
Andrej's user avatar
  • 163
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
parmanand's user avatar
  • 427
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 ...
Amine's user avatar
  • 61
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 ...
stranger's user avatar
  • 193
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....
cljk's user avatar
  • 167
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 ...
René Nyffenegger's user avatar
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, ...
Allan Lewis's user avatar
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 ...
booyaa's user avatar
  • 229
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 ...
Leigh Riffel's user avatar
  • 23.9k
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? ...
John's user avatar
  • 221
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 ...
Pantea's user avatar
  • 1,412
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(...
Spirit - Stop War in Ukraine's user avatar
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....
user avatar
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 ?...
a1ex07's user avatar
  • 8,990
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); ...
Pydi Raju's user avatar
  • 504
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 ...
Vasu's user avatar
  • 51
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 ...
Isaac Kleinman's user avatar
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 ...
Melih's user avatar
  • 337
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, ...
Learner's user avatar
  • 151
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 ...
WBAR's user avatar
  • 181
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 ...
Pydi Raju's user avatar
  • 504
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 ...
esh's user avatar
  • 51
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 ...
René Nyffenegger's user avatar
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 ...
rahim asgari's user avatar
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 ...
Bo Jeanes's user avatar
  • 353

1
2 3 4 5
12