Hot answers tagged plsql
11
Benefits of Packages
Logical Grouping – Methods that work together can be put into a cohesive unit rather than just logically coupled but physically separate.
Secure Private Methods - Functions and Procedures can be made private to the package and only be used within it. This makes the public surface simpler and more secure.
Privilege Management – ...
9
If T2 is derived data based on the contents of T1, then you may want to replace T2 with a materialized view. If T2 is rarely accessed, and quick to derive, then you may just want to use a view.
On the application side, if you stop maintaining table T2, then the values it gets will become increasing incorrect. This can be partially corrected by ...
9
The code you posted is using a cursor. It is using an implicit cursor loop.
There are cases where using an explicit cursor loop (i.e. declaring a CURSOR variable in the declaration section) produces either cleaner code or better performance
If you have more complex queries that you can't refactor out into views, it can make the code easier to read if ...
8
The Oracle Concepts Guide for 11.2 says the following (The note is of particular interest):
Globalization Support Environment
The globalization support environment
includes the client application and
the database. You can control
language-dependent operations by
setting parameters and environment
variables on the client and server,
which ...
8
Here is an example of the API structure to replace a trigger. By calling the AddEntry procedure in the package, the data is inserted into both tables.
CREATE OR REPLACE PACKAGE MyPackage IS
Procedure AddEntry (pValue1 IN Number, pValue2 IN Number);
END;
/
CREATE OR REPLACE PACKAGE BODY MyPackage IS
Procedure AddEntry (pValue1 IN Number, pValue2 IN ...
8
Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.
By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:
SQL - Structured ...
8
You can do this with DBMS_LOCK and an exclusive lock.
See the following procedure:
CREATE OR REPLACE PROCEDURE myproc
IS
lockhandle VARCHAR2(128);
retcode NUMBER;
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);
retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);
IF retcode<>0
THEN
...
7
The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example:
you insert a row in table A
a trigger on table A (for each row) executes a query on table A, for example to compute a summary column
Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it
This is an expected and normal ...
7
If it is wrapped in
BEGIN ... END
DECLARE ... END
CREATE OR REPLACE ... END
Is a one-liner prefixed EXECUTE
Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more ...
7
Is there some operator or function that can give me the indices of the array
No, you have to loop through the associative array:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
type keys_t is table of PLS_INTEGER;
a_arr a_arr_t;
keys_ keys_t := keys_t();
l_index integer;
begin
a_arr(1) := ...
6
It didn't seem like people were really addressing the second part of your question so I thought I'd chime in.
First, I like both answers to part 1 of your question. I think either a materialized view or creating a stored procedure to manage inserting into the second table are satisfactory (+1'd both of them). I like the flexibility of using a stored ...
6
So the way I do this is I don't use default values. Or rather, I set them to NULL as default. Then within my procedure body I check to see if it's equal to NULL and if so set it to a sane default. At that point you could mangle a flag or two in the same space to see what's being set or not set.
However, there's the edge case of if they intended to pass in ...
6
Oracle has Java server-side, but I'd caution that it is not a replacement for PL/SQL. There is no better language than PL/SQL for manipulating data stored in the database - Java may be appropriate for computationally intensive business logic.
For postgres,
There are currently four procedural languages available in the standard PostgreSQL distribution: ...
5
From a general perspective (not platform-specific), here's what I'd recommend mastering for data warehouse projects:
Know how to load data fast. BI projects usually involve nightly loads of large amounts of data. The ETL guys need to shove data in fast with a minimum of concurrency issues. This means knowing when to disable indexes, when to perform tasks ...
5
The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this.
Next to that, if possible, forget about LONG and implement lobs instead.
For docu see ...
5
Assuming that you are using PL/SQL to dequeue the message (there may be additional JMS limitations-- I don't know enough about the interplay between JMS and AQ), you should be able to use a priority queue to implement LIFO (though it would be a bit hokey). When you enqueue a message, you can specify a priority
The priority attribute specifies the
...
5
If I understand the question correctly you want to find numbers that have, for example, more than 4 digits of precision after the decimal? E.g., you want 3.0671, 3.06713, etc. but not 3.067 or 3.06 or 3. If that is the case then you could do something like:
select number from table where number - trunc(number,4) <> 0;
trunc here will simply remove ...
5
Gathering statistics and rebuilding indexes are two completely separate things.
It is exceedingly rare that an index in Oracle needs to be rebuilt so any process that is regularly rebuilding an index is highly suspect. I strongly suspect that rebuilding the indexes is unnecessary. And I would strongly suggest spending some quality time reading through ...
5
Use a bind variable anywhere that you might actually change the value that is passed in.
It probably doesn't make sense, for example, to use a bind variable in the EXISTS clause in your third example because it seems extremely unlikely that anyone would ever want to pass in a different constant for the EXISTS clause.
In your second example, it probably ...
4
Is there a demarcation between SQL and PL/SQL?
SQL is a standard*.
PL/SQL is a vendor extension to the SQL standard*.
*SQL is a language that has an explicit grammar and rules for how that grammar should be implemented, and is but is not a standard per-se. However, since there is a language specification, that everyone can agree on, then anything ...
4
Assuming 11g.
There are a few ways to do this. Easiest is to use the Oracle Job Scheduler with the Remote Job Agent. That agent can run using credentials for a qualified OS account. You could run a job that selects data from the database and spools it in the required output directory.
You could also copy a file to the Remote Job agent. This is closest to ...
4
A mutating table occurs when a statement causes a trigger to fire and that trigger references the table that caused the trigger. The best way to avoid such problems is to not use triggers, but I suspect the DBA didn’t take the time to do that. He could have done one of the following:
Changed the trigger to an after trigger.
Changed it from a row level ...
4
Save sysdate or systimestamp into a pl/sql variable just before the query then use dbms_output.put_line to output the difference afterwards.
4
Prevalent? No. It's not uncommon, however, to write a procedure like so:
create or replace procedure write_to_log
(p_entry_point in varchar,
p_log_type in varchar,
p_message in varchar)
as
pragma autonomous_transaction
begin
insert into my_log_file
(entry_point, log_type, message, log_timestamp)
values
(p_entry_point, ...
4
Fortunately, I found in the existing PL/SQL code I have to maintain, a working "native" behavior:
V_COUNT := MY_ARRAY.COUNT;
should do the trick.
This one is very hard to find with Google, since "count" is more frequently referring to the SELECT COUNT(...) which can be found in SQL queries...
4
ORU-10027 will appear if you are using a lot of dbms_output.put_line in your PL/SQL with a small buffer amount. If you are just debugging then you should set this to a large amount (dbms_output.enable(10000000) is traditional though from 10g onwards it can be unlimted: dbms_output.enable(null)) but try to remove them when going to production.
4
That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE.
Here is a demo how to get around this:
CREATE TYPE mytype AS (id int, txt text);
DO
$body$
DECLARE
_val mytype := (1, NULL)::mytype;
_name text := 'txt';
...
4
I think the question is a little misleading & causes people to not think properly. Looks like the 3 conditions won't overlap, so all you need to do is OR the 3 statements together:
create or replace package body If_Else_Pack is
Procedure Moving(obj_A IN varchar2,
obj_B IN varchar2,
obj_C IN varchar2,
...
4
I guess that using subqueries inside functions is the main performance problem.
It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:
SELECT
loan.loan_number,
loan.borrower_name,
...
CASE WHEN loan.date_waiver_ordered IS NULL
THEN 'Not Ordered'
WHEN ...
4
If you use a count() aggregate on a resultset that has no rows, you'll get zero, not a null.
If a SELECT doesn't find anything, an exception is raised (NO_DATA_FOUND), and you need to catch that exception in an EXCEPTIONS block.
exceptions
when no_data_found:
dbms_output.put_line('ooups');
end;
In your case the second select is raising the ...
Only top voted, non community-wiki answers of a minimum length are eligible
