34
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 -...
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 ...
16
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');
...
13
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 );
...
12
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 ...
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 ( '...
10
It sounds like you want the GREATEST function
INSERT INTO my_table( date_column )
VALUES( GREATEST( date_default, date_insert_value ));
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 ...
10
WHERE PARENT_ID IN my_array;
This will not work. First, as the error message states, you are not allowed to use local collection types in SQL statements, you need to define them in the database. Second, that syntax does not exist.
So first you define the type:
CREATE TYPE arr_type is TABLE of VARCHAR2(11 BYTE);
And after that, you can use the collection ...
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
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 |
| ...
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 ...
9
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:
...
8
You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
AS
BEGIN
-- We create a savepoint here.
SAVEPOINT sp_sptest;
INSERT INTO emptest(empid, empname, deptno)
VALUES(1, 'ravi', 10);
INSERT INTO test1(id, name, sal)
VALUES(1, 'raju', 4444);
UPDATE emptest
SET empname = 'hari'
WHERE empid = 1;
-- ...
7
As it says in the documentation:
Only dynamic SQL can execute the following types of statements within PL/SQL program units:
Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE
A TRUNCATE operation is DDL.
When using EXECUTE IMMEDIATE, remember that any DDL operations you execute will implicitly COMMIT the current transaction....
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
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 ...
6
In case of a Nested-Table (i.e. without INDEX BY BINARY_INTEGER) you can also use CARDINALITY
V_COUNT := CARDINALITY(MY_ARRAY);
Important difference: In case of Nested-Table which is NULL, COUNT raises an exception, CARDINALITY returns NULL.
6
I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING:
-- derived table
OPEN myCursor FOR
SELECT value1 AS Value1,
myPackage.function1 (value1, my.id) AS Result1,...
6
I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of:
You admitted that you are working with large record counts in your tables. What is ...
6
Wrong If syntax. And exists not allowed here. Try this way:
declare
cnt number;
begin
select count(*) into cnt from my_table;
if cnt != 0 then
dbms_output.put_line('has rows');
else
dbms_output.put_line('no rows');
end if;
end;
6
You can not write DDL in PL/SQL block directly as you have shown in your question.
However you can execute these DDLs using EXECUTE IMMEDIATE command as demonstrated below.
CREATE OR REPLACE PROCEDURE ddl_test AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tbl1';
END;
/
6
Yeehaw.
DECLARE @MyTable TABLE ( MyColumn VARCHAR(100) );
INSERT @MyTable ( MyColumn )
VALUES ( '-----, ,,, This is ,- a test,---,' );
SELECT *,
SUBSTRING(mt.MyColumn, ca.p, LEN(mt.MyColumn))
FROM @MyTable AS mt
CROSS APPLY ( SELECT *
FROM (VALUES (PATINDEX ('%[^ ,-]%', MyColumn))
) AS x (p) ) AS ca;
6
insert first
when CPF is null
then into COMPANIES_ADDRESS (ZIPCODE, STREET, "NUMBER")
else into CUSTOMERS_ADDRESS (ZIPCODE, STREET, "NUMBER")
select V_ZIPCOD, V_STREET, V_NUMBER
from dual
P.s.
Using reserved word as identifiers (in this case NUMBER as a column name) is a really bad practice.
5
When you create a program with CREATE_PROGRAM procedure, you can specify how many arguments it expects with number_of_arguments parameter.
If, for some reason, you want to modify some attribute (number_of_arguments in our case) for an existing Scheduler object (program in our case), you don't need to drop the object and re-create it with new attribute value ...
5
11g and higher accept this syntax:
ALTER USER username IDENTIFIED BY new_password REPLACE old_password;
5
Oracle is free for download and use in development environment. If you go to production and/or require support you must pay.
5
Each element of a name must be quoted individually:
drop java source "SCHEMA"."JAVASOURCENAME";
The identifier "SCHEMA.JAVASOURCENAME" refers to a java source name SCHEMA.JAVASOURCENAME in the current schema, e.g. "VORIAND"."SCHEMA.JAVASOURCENAME"
answered Sep 2 '15 at 8:49
a_horse_with_no_name
62.5k12 gold badges118 silver badges157 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
