27
This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.
The reasons people do it:
they're lazy - I don't know why people think their productivity is improved by writing terse code rather than typing for an extra 40 milliseconds to get much more literal code.
The reasons it's bad:
it's not self-documenting -...
25
I think this is what you're after:
V_COUNT := MY_ARRAY.COUNT;
17
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 behaviour, ...
17
Oracle's official documentation library has some great resources, including:
The Database Concepts guide, which is essential reading for beginner and Oracle ninja alike.
The 2 Day DBA
The 2 Day Developer's Guide
Oracle 12c official PL/SQL documentation:
PL/SQL Language Reference
PL/SQL Packages and Types Reference
Chapter 5 Developing Stored Subprograms ...
15
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 your ...
15
If you're really only looking to learn about the development side and have no interest in the administration or installation side at the moment, a quicker route might be to download a prebuilt developer VM image for Virtualbox. That can get you up and running very quickly, and you can connect to the DB running inside the VM from outside, so you can continue ...
14
Anonymous PL/SQL blocks don't start with IF. The above code should be in between a begin and end; at least.
EXISTS is an SQL function, it can't be used in PL/SQL like that.
Try something like this:
set serveroutput on
declare
c number;
begin
select count(*) into c from my_table where rownum = 1;
if c != 0 then
dbms_output.put_line('has rows');
...
12
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 – ...
12
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
...
12
I am not proud of publicly writing something like this.
Sample + data:
CREATE TABLE donaldduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE daisyduck ( SURNAME VARCHAR(100), TOWN VARCHAR(100) );
CREATE TABLE goofy ( something number(1), SURNAME VARCHAR(100), TOWN VARCHAR(100) );
INSERT INTO DONALDDUCK ( SURNAME, TOWN ) VALUES ( '...
12
This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm.
Here is an example:
https://www.orafaq.com/wiki/Binary
CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
binval varchar2(64);
N2 number := N;
BEGIN
while ( N2 > 0 ) loop
binval := mod(N2, 2) || binval;
N2 := trunc( N2 / 2 );
...
11
As you can see from the output below, a test anonymous PL/SQL block works in SQL Plus as expected:
[oracle@node1 ~]$ sqlplus phil/phil
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 1 21:55:35 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With ...
11
Of course you ought to study the license, but as I recall it is free to download and use for non-production purposes.
It includes all of the Option packs, and is not in any sense cripled, but patches will not be available as they would be if you had a support account.
Oracle XE is free to use in production environments, but has limited functionality and ...
10
No, there's really no shortcuts here. Examine things in the following order:
Check the procedure name.
Check the number of parameters.
Check the types of the parameters.
10
First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.
begin
hotel.fill_city(10000);
end;
/
should correctly invoke your procedure.
Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the ...
9
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 ...
9
MySQL allows you to do GROUP BY with aliases (Problems with Column Aliases). This would be far better that doing GROUP BY with numbers.
Some people still teach it
Some have column number in SQL diagrams. One line says: Sorts the result by the given column number, or by an expression. If the expression is a single parameter, then the value is interpreted as ...
9
I think people were having trouble understanding your question due to the table structure which is so bad it seems designed to give you a headache.
As Balazs Papp indicates very little can be implemented that will scale or not look like something hacked together.
However there are solutions that can be done in PL/SQL. A pipelined table function will end ...
8
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...
8
Interesting question. As far as I can tell, this is not possible, though it seems like it should be. Unless someone else can show how it can be done, a workaround would be to nest the code in a block:
create or replace trigger trg_before_insert_temp1 before insert on temp1 for each row
declare
begin
<<bob>> declare
int_val int;
...
8
It sounds like you want the GREATEST function
INSERT INTO my_table( date_column )
VALUES( GREATEST( date_default, date_insert_value ));
7
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 ...
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) := 2;
...
7
This is the expected behaviour of nextval as documented:
Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:
For each row returned by the outer query block of a SELECT statement
[...] If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence ...
7
I would just delete.
Even if I had to only delete if there were say exactly 2 records, then I would add this condition to the where clause, like
DELETE FROM table1
WHERE col1='somevalue'
AND (SELECT COUNT(*) FROM table1 WHERE col1 = 'somevalue') = 2;
In all cases when writing a delete statement consider that in general the where clause is usually either
...
7
Consider below case:
+------------+--------------+-----------+
| date | services | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Apps | 1 |
| 2016-05-31 | Applications | 1 |
| 2016-05-31 | Applications | 1 |
| 2016-05-31 | Apps | 1 |
| 2016-05-31 | Videos | 1 |
| ...
7
Have you seen this? https://community.oracle.com/thread/889338?start=0&tstart=0
It states you can only exchange partitions from a partitioned table to a non-partitioned table, or vice-versa.
You'd need to temporarily exchange the partition into an
interim, non-partitioned, table, then into the target partitioned table.
There are some interesting ...
7
In SQL Developer, use the /*insert*/ "hint".
select /*insert*/ * from t1;
REM INSERTING into T1
SET DEFINE OFF;
Insert into T1 (C1,C2) values (1,2);
Insert into T1 (C1,C2) values (2,3);
Insert into T1 (C1,C2) values (3,4);
Another useful "hint":
select /*csv*/ * from t1;
"C1","C2"
1,2
2,3
3,4
Starting with version 4.1, the "hint" the below also works:
...
6
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 ...
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: PL/...
Only top voted, non community-wiki answers of a minimum length are eligible
