22
votes
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
Consider below case:
+------------+--------------+-----------+
| date | services | downloads |
+------------+--------------+-----------+
| 2016-05-31 | Apps | 1 |
| 2016-05-...
- 321
15
votes
How to check if the table has rows?
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:
...
- 40.3k
14
votes
Accepted
PL/SQL function to receive a number and return its binary format
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) ...
- 40.3k
12
votes
Selecting from random amount of tables with identical structure
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 ...
- 40.3k
12
votes
Accepted
How can I use an array variable inside the IN operator for Oracle SQL?
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. ...
- 40.3k
10
votes
Accepted
Generate `insert` statements for each entry in a table
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);
...
- 40.3k
9
votes
Accepted
Selecting from random amount of tables with identical structure
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 ...
- 4,252
8
votes
Accepted
How to call a stored procedure in an Oracle Scheduler job
You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ). I'd also specify INTERVAL=1 explicitly.
BEGIN
dbms_scheduler.create_job (
job_name =>...
- 8,960
6
votes
Accepted
How to check if the table has rows?
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
...
- 130
6
votes
Accepted
How to Combine Insert, Update, and Delete in the same `Instead Of` Trigger, in Oracle db?
From Coding the Trigger Body
Detecting the DML Operation that Fired a Trigger
If more than one type of DML operation can fire a trigger (for
example, ON INSERT OR DELETE OR UPDATE OF emp), ...
- 176
6
votes
Accepted
Why am I getting PLS-00103 when I try to create a table in a PL/SQL procedure?
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 ...
- 8,604
6
votes
How to port LTRIM from Oracle to SQL Server?
Yeehaw.
DECLARE @MyTable TABLE ( MyColumn VARCHAR(100) );
INSERT @MyTable ( MyColumn )
VALUES ( '-----, ,,, This is ,- a test,---,' );
SELECT *,
SUBSTRING(mt.MyColumn, ca.p, LEN(mt....
- 38.6k
6
votes
What is the difference between using First..Last and 1..count in Oracle?
first and last returns the first index and last index. count returns the number of elements in the collection. These do not necessarily give a correct way of referencing the elements in the collection,...
- 40.3k
6
votes
Accepted
Best way to use insert statement on Oracle
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.
...
- 3,204
5
votes
SELECT from Function that return CURSOR
You could try using
select * from table(xmlsequence( myfunc() )) . In this case you get xml in columns.
Or try this example to extract fields from your refcursor
select extractvalue(column_value,'/...
- 71
5
votes
Accepted
Pass (comma separated) -yet single parameter to cursor
The simplest little dirty trick I know for this is xmltable(). With xmltable()you can convert a comma-seperated list of values into rows, for example:
select * from xmltable('1, 2, 3')
COLUMN_VALUE
-...
- 40.3k
5
votes
Accepted
How to declare reserved words as variables in Oracle PL/SQL?
NUM is not a reserved word.
The best way is not to use reserved words.
If you insist on using them, you can put them between quotation marks. This does not work:
SET SERVEROUTPUT ON
declare
begin ...
- 40.3k
5
votes
Writing a simple SELECT Stored Procedure in Oracle PL/SQL
You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter.
Quick procedure to test with:
CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT ...
- 31.6k
5
votes
Accepted
EXCEPTION logic is not working in Oracle PL/SQL function
You are trying to find an error where an error can not exist. When you are doing a count it will always return a valid result even if the value that you are looking for does not exist in the table.
...
- 1,029
5
votes
Cascade primary key update to all referencing foreign keys in oracle
With DEFERRABLE foreign key constraints:
create or replace procedure cascade_update_primary
(
p_table_name in varchar2,
p_old_value in number,
p_new_value in number
)
as
l_column_name ...
- 40.3k
5
votes
Execute sql file in Exception statement
So it's a bit confusing - you're writing a SQL*Plus script, which accepts both SQL*Plus commands as well as SQL commands and PL/SQL blocks.
SQL*Plus commands are executed locally on your client ...
- 184
5
votes
Accepted
How to solve error "ORA:01-006 :bind variable does not exist" when working with bind variables?
You can completely avoid dynamic SQL (you also should never name PL/SQL variables the same as table columns):
create or replace procedure test_bind_variable(res out sys_refcursor,
...
- 25k
5
votes
Accepted
weird behaviour of NVL(<Column_name>,Sequence.nextval) when the colummn has data?
NVL does not short-circuit, so even when the first input is not null the second argument is evaluated.
COALESCE does use short-circuit evaluation
True, but that's not the reason for the behaviour ...
4
votes
How to get return value from pl sql procedure and assign it to variable?
You can create it like this:
CREATE OR REPLACE PROCEDURE procedurename(param1 NUMBER, param2 varchar(20), returnvalue OUT NUMBER);
IS
BEGIN
... your code
END;
And then use it like this:
...
- 10.1k
4
votes
Pass (comma separated) -yet single parameter to cursor
Here is a solution using subquery factoring, regular expressions, and a hierarchical query on dual.
CURSOR My_Cursor( vsStr1 ) IS
WITH
RawInputData As (
SELECT vsStr1 sStr1 FROM ...
- 23.8k
4
votes
Identify columns that allow NULL but don't contain NULL
I adapted the query from the linked answer in my question and have something I think works, but I'd prefer if it returned a table instead of just raising notices. I may improve it later but if I don't ...
- 353
4
votes
How in Oracle can I export package that will have package and body?
There are two ways to achieve this
Way 1 :
SET HEAD OFF
SET ECHO OFF
SET FEED OFF
SET TERM OFF
SET LINE 1500
SET NEWPAGE NONE
set pagesize 0
SPOOL C:\temp\FILE_NAME.SQL
PROMPT CREATE OR REPLACE
...
4
votes
Accepted
Multiset operators on nested tables with non scalar types
MULTISET EXCEPT
The element types of the nested tables must be comparable.
Comparison Conditions
Two objects of nonscalar type are comparable if they are of the same
named type and there is a ...
- 40.3k
4
votes
Accepted
For each field name in a list of fields, get the unique values
One approach for solving this problem is to use dynamic sql. In fact, your question is one of the example use cases:
For example, a SELECT statement that includes an identifier that is unknown at ...
- 31.9k
Only top scored, non community-wiki answers of a minimum length are eligible