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.

learn more… | top users | synonyms

11
votes
3answers
6k 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 ...
11
votes
1answer
6k 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.
8
votes
3answers
1k 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.
8
votes
3answers
1k 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 ...
7
votes
2answers
446 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 ...
6
votes
5answers
11k 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 ...
6
votes
3answers
12k 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) ...
6
votes
1answer
16k 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 ...
6
votes
2answers
92 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 ...
6
votes
1answer
745 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
3answers
1k 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
6answers
1k 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: ...
5
votes
1answer
154 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
2answers
629 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 ...
5
votes
1answer
300 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 ...
5
votes
1answer
967 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 ...
5
votes
3answers
1k 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 ...
4
votes
3answers
14k 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 ...
4
votes
2answers
208 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
3answers
3k 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 ...
4
votes
2answers
1k 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 ...
4
votes
2answers
13k 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 ...
4
votes
2answers
34k 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 ...
4
votes
2answers
888 views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
4
votes
1answer
1k views

How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats

Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings: FOR COLUMNS SIZE 5 some_column and FOR COLUMNS some_column SIZE 5 both will create a ...
3
votes
2answers
307 views

Is it good practice to check for existence of records before deleting them

I've came across the following SQL/PLSQL while investigating a stored procedure written by someone else: 1. V_COUNT NUMBER(5,0); ......... 2. SELECT COUNT(*) INTO V_COUNT FROM TABLE1 WHERE ...
3
votes
1answer
1k views

How far should bind variables go in Oracle?

I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time. Now, I have a quite firm ...
3
votes
2answers
1k views

FORALL on Nested Collections

Suppose I have the following two tables in my Oracle database: create table a (num number); create table b (val varchar2(30); and in my PL/SQL program I have declared a nested collection as ...
3
votes
1answer
6k views

Referencing PL/SQL variables in FOR loop

I have written a PL/SQL script to find the size of a long column in a table. Just to make the script generic I am passing the table Name and column name as variables , but I am getting an error saying ...
3
votes
2answers
13k views

How to execute this procedure in PL/SQL?

I have this table in the below format: Persnbr | Userfieldcd | Value 01 | Port | Funds 01 | Vip1 | Systems 02 | Port | Bank 02 | Vip1 | Authority This is how I want ...
3
votes
1answer
1k 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 ...
3
votes
1answer
1k views

Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)?

In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table (i.e. a it will be a ...
3
votes
2answers
3k views

Reset every Oracle sequence to the MAX value of its target table PK?

I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created ...
3
votes
1answer
1k views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation ...
3
votes
1answer
618 views

What is an Effective Way to Count the Number of Pages in Oracle?

We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results. We build implement paging by putting the original query inside a ...
3
votes
2answers
1k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
3
votes
2answers
23k 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 ...
3
votes
3answers
179 views

How to raise_application_error beyond a when others clause

Background: I've used a few Oracle articles to develop an error package, with some procedures. One of these is Log_And_Return which is called throughout the database. I've simplified it for brevity, ...
3
votes
1answer
625 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
0answers
314 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
3
votes
1answer
1k 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, ...
3
votes
0answers
758 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, ...
3
votes
1answer
3k 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 ...
3
votes
0answers
1k 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 ...
2
votes
2answers
2k 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?
2
votes
2answers
460 views

Use different Windows account in pl/sql

Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean: Other ...
2
votes
1answer
135 views

Is there a way to pass to execute immediate only once a variable but use it several times?

I have a Cursor that gets its data from a script along these lines: OPEN myCursor FOR SELECT value1 AS Value1, myPackage.function1 (value1, :myId) AS Result1, myPackage.function2 (value1, :myId) ...
2
votes
1answer
1k views

PL/SQL : query numeric data type by scale?

Wish to select rows with number fields having a certain amount of data after the decimal eg select number from table where number.scale > 4; Is this possible to do directly, or must I cast to ...
2
votes
2answers
1k views

Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

Normally in java or C# , I would use a following construct when try and catch errors. Bad Exception Handling in C# and Java try { // do something } catch(Exception ex) { } Good Exception ...
2
votes
1answer
21k 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 ...