Skip to main content
Share Your Experience: Take the 2024 Developer Survey
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
8 votes
Accepted

Best way to get current year minus 5 years

There are different ways to achieve the goal. SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual; OR SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'...
atokpas's user avatar
  • 8,634
7 votes

How to find foreign keys that don't have corresponding indexes?

Oracle SQL Developer ships with a report to find these unindexed columns. Here's the SQL behind this report: select a.owner "Owner", a.table_name "...
thatjeffsmith's user avatar
7 votes
Accepted

How to get hierarchical data from two tables in oracle?

Use grouping sets. You can try it here. select cat1, cat2, cat3, sum(val) as val from t1 join t2 on t1.f_id = t2.f_id group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3)) order by cat1, ...
vercelli's user avatar
  • 756
6 votes

Unable to see list of tables in Oracle SQL Developer

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details. If you want to see 'the tables' - you need to expand ...
thatjeffsmith's user avatar
6 votes
Accepted

Inserting year with zero

SQL> create table t1 (c1 date); Table created. SQL> insert into t1 values (date'0000-01-05'); 1 row created. SQL> commit; Commit complete. SQL> select c1, to_char(c1, 'dd/MM/yyyy'), ...
Balazs Papp's user avatar
  • 40.7k
5 votes

ORA-03113 when starting up an Oracle 11g database

From ParnassusData: This error indicates that a mismatch has been detected between redo records and rollback (undo) records. ARGUMENTS: Arg [a] - Maximum Undo record number in Undo ...
miracle173's user avatar
  • 7,682
5 votes
Accepted

Is it possible to perform skip N rows and fetch M rows without full scan

The standard approach to get rows N through M is to do something like SELECT * FROM (SELECT a.*, rownum rnum FROM (SELECT emp_id, last_name FROM employees ...
Justin Cave's user avatar
  • 20.3k
5 votes
Accepted

Oracle return the first row, not returning the first row

The answer is right there, in the data. (I assume that the time part is 00:00:00 in all of them): The order by has ties. There is not one "first row" but 3 of them. So, when the 2nd query ...
ypercubeᵀᴹ's user avatar
5 votes

How to run a query in PL/SQL with Serializable transaction isolation

Alter the session: declare a number; begin EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE'; select count(*) into a from item w where w.Item_num='MOH601' ; ...
Philᵀᴹ's user avatar
  • 31.8k
5 votes

oracle 11g Grant on dbms_lock not persisting

From Oracle documentation- When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role. SQL> conn system Password: SQL> select * from ...
atokpas's user avatar
  • 8,634
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

Lock out of Oracle based on Windows username

The reason why your trigger does not work for users like SYS or SYSTEM is because they have the ADMINISTER DATABASE TRIGGER privilege. The ADMINISTER DATABASE TRIGGER privilege allows you to create ...
Wernfried Domscheit's user avatar
4 votes

Not exists in sql query

First of all, NOT IN is not the same as NOT EXISTS. create table customer (customerid number); create table purchasebill (customerid number, retailoutletid number); create table retailoutlet (...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Select multiple columns from subquery - Connect by

ORA-00913 too many values Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the ...
atokpas's user avatar
  • 8,634
4 votes

Which one is created first? Database or Instance?

Introduction to the Oracle Database Instance A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Data Replication in Oracle

A fast refresh would copy incremental changes over the network but requires that a materialized view log be created on the master site on the source table. That adds some overhead to the inserts ...
Justin Cave's user avatar
  • 20.3k
4 votes

Different execution plan with Rownum <NUM

By artificially limiting the number of rows returned, it's doing 90% less work (based off cardinality, bytes, etc.). The result is that the cost-based optimizer decided to do a nested loop instead of ...
Randolph West's user avatar
4 votes

Creating a index on a date column that's already populated

There is no downside in creating an index at a later moment. The index will perform the same way like it would when it was created when the table was created. You mentioned that the table has already ...
Marco's user avatar
  • 3,710
4 votes
Accepted

Is wrapping only possible for package bodies?

PL/SQL Source Text Wrapping You can wrap the PL/SQL source text for any of these stored PL/SQL units, thereby preventing anyone from displaying that text with the static data dictionary views *...
Balazs Papp's user avatar
  • 40.7k
4 votes

Can `sys.aud$` recycle itself?

The audit table does not 'clean' itself. And there is not just a simple setting, like an init parameter, to do so. You have to set up and schedule jobs to do that, and @eckes gave you a link to the ...
EdStevens's user avatar
  • 2,034
4 votes

limiting db user password to 3 days

First, create a password policy (profile) that captures your time period. Then assign that profile to the user. CREATE PROFILE SHORT_LIFE_PROFILE LIMIT PASSWORD_LIFE_TIME 3 ...
CaM's user avatar
  • 2,183
4 votes

Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni"

Having the correct value in sysctl.conf does not mean that is the current setting in effect system-wide. You can check the current value in effect with: sysctl -a | grep "kernel.sem" If it differs ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Group by checks constraints are possible?

You can use a unique index for that. Example: create unique index one_leader_per_team on members (case when IS_LEADER = 'Y' then TEAM_ID else null end) ; What it does is store team_id - in ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Lock out of Oracle based on Windows username

I suggest a multi-phase approach that can be implemented in stages and will minimize the impact of changing to a more secure approach. I assume that you have a development environment to test in and ...
kevinskio's user avatar
  • 4,262
4 votes

PL/SQL function to receive a number and return its binary format

No need to use a function - you can do it in (Oracle's) SQL. SELECT LISTAGG(SIGN(BITAND(43, POWER(2,LEVEL-1))),'') WITHIN GROUP(ORDER BY LEVEL DESC) bin FROM dual CONNECT BY POWER(2, LEVEL-1)&...
Vérace's user avatar
  • 30.1k
4 votes
Accepted

How to use "regular expression" to separate specific strings in Oracle

Simple method: col field1 format a6 col field2 format a6 col lvl format a3 variable B1 varchar2(32); exec :B1 := '1:30,2:4,52:0,8:1'; select regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

De-Duplicate from LISTAGG

select speaker, listagg(product, ';') within group (order by product) as products from ( select distinct speaker, product from existing_data ) t group by speaker order by ...
mustaccio's user avatar
  • 26.1k
3 votes

ORA-03113 when starting up an Oracle 11g database

I agree with Miracle173's answer. I dug around on metalink (I assume you don't have an account there) and this seems to be the best, supported method to solve your ora-600 [4194] issue. This error ...
sandman's user avatar
  • 489
3 votes
Accepted

Can we expdp using JOINS in QUERY clause

The QUERY parameter is used to restrict data in the export job. If you want to export number of rows that are exist in another table then you can use subquery in QUERY parameter. If you want to join ...
atokpas's user avatar
  • 8,634

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