Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.
9
votes
1answer
1k 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.
6
votes
3answers
2k 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 ...
6
votes
3answers
260 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 ...
6
votes
1answer
218 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
719 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
3answers
1k 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) ...
5
votes
3answers
820 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 ...
5
votes
1answer
81 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
379 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
234 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 ...
4
votes
2answers
574 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
2k 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
2k 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
1answer
7k 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 ...
4
votes
1answer
502 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 ...
4
votes
1answer
781 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 ...
4
votes
2answers
312 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 ...
3
votes
3answers
565 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 ...
3
votes
2answers
80 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
...
3
votes
1answer
557 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
295 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
3k 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
1answer
220 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
304 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
1answer
369 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
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
1k 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 ...
2
votes
3answers
1k 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 ...
2
votes
2answers
339 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
2answers
681 views
Dynamic access to record column in plpgsql function
How can I address a column from a record in a plpgsql function dynamically?
In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
2
votes
1answer
336 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
291 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
231 views
how to create and insert into a table in an anonymous block
I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table.
When the block compiles, it gives me an ORA-00942: table or view does not exist ...
2
votes
1answer
172 views
How to check parameter value existence in a procedure?
The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking:
...
2
votes
1answer
164 views
How to speed up a query with a lot of subqueries
I have a query which uses a series of functions to return the status of each document for a loan.
SELECT loan_number,
borrower_name,
get_application_status(loan_number, ...
2
votes
2answers
262 views
Get timing on individual queries in Anonymous code block
I have a set of queries that I need to run in succession, carrying the result from the previous query into the next. My only need is the execution time on the last query in the list, but SET TIMING ON ...
2
votes
1answer
75 views
PLSQL Package - Catching Errors in procedure (Oracle)
I have a problem with my package (and procedure) not catching exceptions.
I have the below code:
CREATE OR REPLACE PACKAGE process_orders
IS
PROCEDURE add_order (
order_num NUMBER,
...
2
votes
1answer
236 views
Virtual Column - Using it in view (Oracle)
I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as
lname varchar2 (256);
snumber varchar2 ...
2
votes
1answer
908 views
Within a PL/SQL procedure, wrap a query or refcursor in HTML table
It seems really easy if you are using SQL*Plus to use
SQL> set markup html on;
and get some lovely results to the SQL*Plus window. we have an oracle job that runs overnight and sends an email ...
2
votes
1answer
960 views
Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)
I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java ...
2
votes
1answer
83 views
Oracle Packages, Procedures and Functions Not Respecting Roles
When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
2
votes
2answers
1k 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 ...
2
votes
2answers
831 views
Return many rows on a plsql Oracle10g
I'm doing a little project and I need to do a plsql to return many rows.
I have someting like:
Table(id, data1, data2, from)
And the user gives me the value of from and I have to return all that.
...
2
votes
2answers
122 views
Overhead of DBMS_Profiler
If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
2
votes
1answer
309 views
Alter in PL SQL Loop
I just started learning PL/SQL an hour ago... I'm trying to set profile settings dynamically... I select distinct profile names from dba_users and I'm trying to run an alter profile command for each ...
2
votes
1answer
392 views
Logging/profiling an unpredictably slow stored procedure
I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only ...
2
votes
2answers
626 views
Piplelined Result Set over Database Link Alternatives
Since a pipelined result set cannot be returned over a database link (see MOS note 560743.1), what workarounds/alternatives are there. I can think of a few, but I'd rather not lead the question. I ...
2
votes
1answer
501 views
PL/SQL + after login seems Not see tnsnames.ora file on my PC
I get the following error
when I login in by PL/SQL - please advice how to fix this?
Data character set (UTF8) and client character set (IW8MSWIN1255) are different character set conversion may ...
2
votes
1answer
114 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 ...
2
votes
2answers
78 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 ...
