Hot answers tagged

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.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) ...
Balazs Papp's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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. ...
Balazs Papp's user avatar
  • 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); ...
Balazs Papp's user avatar
  • 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 ...
kevinskio's user avatar
  • 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 =>...
a1ex07's user avatar
  • 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 ...
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,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....
Erik Darling's user avatar
  • 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,...
Balazs Papp's user avatar
  • 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. ...
David דודו Markovitz's user avatar
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,'/...
user2968720's user avatar
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 -...
Balazs Papp's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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 ...
Philᵀᴹ's user avatar
  • 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. ...
Joe W's user avatar
  • 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 ...
Balazs Papp's user avatar
  • 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 ...
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
  • 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 ...
Andi Schloegl's user avatar
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: ...
Julien Vavasseur's user avatar
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 ...
Leigh Riffel's user avatar
  • 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 ...
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.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 ...
Joe Obbish's user avatar
  • 31.9k

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