Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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-...
imVJ's user avatar
  • 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: ...
Balazs Papp's user avatar
  • 40.7k
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) ...
Balazs Papp's user avatar
  • 40.7k
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. ...
Balazs Papp's user avatar
  • 40.7k
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); ...
Balazs Papp's user avatar
  • 40.7k
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 ...
Anton Chekmarev's user avatar
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), ...
schurik's user avatar
  • 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 ...
atokpas's user avatar
  • 8,632
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....
Erik Darling's user avatar
  • 41.1k
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,...
Balazs Papp's user avatar
  • 40.7k
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. ...
David דודו Markovitz's user avatar
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 ...
Balazs Papp's user avatar
  • 40.7k
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 ...
Philᵀᴹ's user avatar
  • 31.8k
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. ...
Joe W's user avatar
  • 1,049
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 ...
Balazs Papp's user avatar
  • 40.7k
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 ...
kfinity's user avatar
  • 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, ...
mustaccio's user avatar
  • 26.2k
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 ...
Andi Schloegl's user avatar
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 ...
Bo Jeanes's user avatar
  • 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 ...
Bibhuti Bhusan Padhi's user avatar
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 ...
Balazs Papp's user avatar
  • 40.7k
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 ...
Joe Obbish's user avatar
  • 32.2k
4 votes
Accepted

How to port LTRIM from Oracle to SQL Server?

Take the suffix of the string starting from the first character which is not a space, comma or hyphen: declare @str varchar(100) = ' -----, ,,, This is ,- a test,---,' select substring(@str,...
David דודו Markovitz's user avatar
4 votes

Compilation error on stored procedure using PL/SQL in Oracle?

@kevinsky is correct, you cannot use avg as a variable name in PL/SQL, however that is not the only issue. You also cannot assign values to IN variables like m1, m2, and m3. My best guess is that you ...
Jack Douglas's user avatar
4 votes

Is it possible to enter a missing sequence number into another table, on exception in a Trigger

EXCEPTION WHEN OTHERS THEN SEQ_AUDIT_PKG.Inse... is a bad idea, because 'when others' could be anything. eg, you might have a datafile corruption, or an out of memory error. You dont ...
Connor McDonald's user avatar
4 votes
Accepted

Oracle SQL. Error creating type

Type the following command right after you receive your error-message: show errors Show here: SQL> CREATE TYPE boolean_typ AS OBJECT 2 (valor char(5) CHECK (VALUE IN ('true', 'false', NULL)) )...
Marco's user avatar
  • 3,710
4 votes
Accepted

How to execute stored procedure with output parameter in Oracle PL/SQL?

There are several mistakes. 1. When specifying parameters of procedure, you don't need to specify size. E.g. it should be CREATE OR REPLACE PROCEDURE insert_toys(toy_id OUT NUMBER,toy_name VARCHAR ), ...
a1ex07's user avatar
  • 9,010
4 votes

Oracle procedure with input parameter that performs multiple SUBSTR

You don't need a procedure, you can do this in a single SQL statement (though you could wrap that statement in a procedure if necessary): insert into employee(employee_name) with w as (select 'adam,...
Jack Douglas's user avatar
4 votes
Accepted

How do I execute a PL/SQL package from my database SQL> prompt?

You cannot execute a PL/SQL package, as it is simply a container for one or more routines (stored procedures and functions). Typically you use packages to organize various related routines. You ...
mustaccio's user avatar
  • 26.2k

Only top scored, non community-wiki answers of a minimum length are eligible