#Pl-Sql

SELECT *
FROM TABLE1 
WHERE ID NOT IN (
    SELECT ID
    FROM TABLE2
)

When I ran the above query I know the condition will give me the results, because the data exists in the TABLE1 which is not in TABLE2. But I didn’t go any results. I got surprised. After I a while I found out the issue.

So the reason is that ID column in the TABLE2 have null values. If any value of ID in the subquery is NULL, then the entire NOT IN clause fails to match anything. This is standard SQL behavior because NULL makes the whole comparison unknown.

Solution:

1. You can use NOT NULL condition in the sub-query as below:

SELECT *
FROM TABLE1 
WHERE ID NOT IN (
    SELECT ID
    FROM TABLE2
    WHERE ID IS NOT NULL
)

2. Use NOT EXISTS:

SELECT *
FROM TABLE1 T1
WHERE NOT EXISTS (
    SELECT 1
    FROM TABLE2 T2
    WHERE T1.ID = T2.ID
)

The NOT EXISTS will automatically handle null values and safer to use.

References

StackOverflow, Geeks for geeks

To extract the value of a tag from the given XML-like string in PL/SQL, you can use the EXTRACTVALUE function or XMLTABLE for XML processing.

Example Using EXTRACTVALUE

DECLARE
    xml_data CLOB := '<Status>200</Status><Message>Success</Message>';
    l_message VARCHAR2(50);
BEGIN
    -- Extract the FunctionalReferenceID value
    l_message := EXTRACTVALUE(XMLTYPE('<root>' || xml_data || '</root>'), '/root/Message');
    
    -- Print the value
    DBMS_OUTPUT.PUT_LINE('FunctionalReferenceID: ' || l_message);
END;

Example Using XMLTABLE

DECLARE
    xml_data CLOB := '<Status>200</Status><Message>Success</Message>';
    l_message VARCHAR2(50);
BEGIN
    -- Use XMLTABLE to extract the Message
    SELECT value
    INTO l_message
    FROM XMLTABLE(
        '/root/Message'
        PASSING XMLTYPE('<root>' || xml_data || '</root>')
        COLUMNS value VARCHAR2(50) PATH '.'
    );

    -- Print the value
    DBMS_OUTPUT.PUT_LINE('Message: ' || l_message);
END;

EXTRACTVALUE, XMLTABLE

For NVARCHAR2 and VARCHAR2 maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. This is useful you have to allocate more data to a variable.

You can run the below command to view the parameter.

show parameter MAX_STRING_SIZE;

References

Oracle Doc, Max string size

In Oracle PL/SQL, you can use the following commands to view table details:

1. DESCRIBE or DESC

   DESC table_name;

   Displays columns, data types, and constraints.

2. USER_TAB_COLUMNS

   SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
   FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME = 'TABLE_NAME';

   Shows details about columns for tables owned by the user.

3. ALL_TAB_COLUMNS

   SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
   FROM ALL_TAB_COLUMNS
   WHERE TABLE_NAME = 'TABLE_NAME';

   Provides column information for tables accessible to the user.

4. DBMS_METADATA.GET_DDL

   SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME') FROM DUAL;

   Displays the CREATE TABLE statement for table structure details.

To view constraints on the table, you can check here.

If your Oracle database has Flashback enabled, you can query past versions of data within a specified retention period. Here’s how to use Flashback to retrieve a prior state of data:

SELECT * 
FROM your_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) 
WHERE your_condition;

Replace SYSTIMESTAMP - INTERVAL '5' MINUTE with the timestamp or interval that reflects when the data was last known to be in its old state.

Note: Flashback must be enabled, and it’s only available within the Flashback retention window, which depends on your database configuration.

References

Killians Bytes

To update a column with a random value in PL/SQL, you can use the DBMS_RANDOM package, which provides functions for generating random numbers or strings.

To update random number

BEGIN
    UPDATE employees
    SET salary = ROUND(DBMS_RANDOM.VALUE(100, 500));
    COMMIT;
END;

• DBMS_RANDOM.VALUE(100, 500) generates a random decimal number between 100 and 500.
• ROUND is used to convert the decimal value to an integer.

To update random string

BEGIN
   UPDATE employees
    SET password = DBMS_RANDOM.STRING('x', 8);  -- 'x' specifies alphanumeric characters
    COMMIT;
END;

• DBMS_RANDOM.STRING('x', 8) generates an 8-character alphanumeric string.
• ‘x’ specifies alphanumeric characters (letters and numbers). Other options include ‘a’ for alphabets and ‘u’ for uppercase only.

The PL/SQL MINUS operator returns all rows from the first query that are not present in the second query. Each SELECT statement defines a dataset, and the MINUS operator retrieves all records from the first dataset, excluding any that also appear in the second dataset.

select stock_id, stock_name
from stock_master
where active = 'Y'

minus

select stock_poid, stock_desc
from stock_ledger
where transaction_date > '01-JAN-24'

References

Tech on the net

The LISTAGG analytic function, introduced in Oracle 11g Release 2, greatly simplifies string aggregations within SQL queries.

SELECT pid, LISTAGG(ColumnName, ' ' ) WITHIN GROUP (ORDER BY seq) AS ColumnName
FROM B GROUP BY pid;

However, if the output of the above query exceeds 4000 characters, it triggers an error, specifically ORA-01489, indicating that the result of string concatenation is too long.

To address this limitation, Oracle Database Release 2 (12.2) enhanced LISTAGG with the ability to handle overflow errors gracefully, as demonstrated below:

SELECT pid, LISTAGG(ColumnName, ' ' ON OVERFLOW TRUNCATE ) WITHIN GROUP (ORDER BY seq) AS ColumnName
FROM B GROUP BY pid;

In this updated syntax, the output is restricted to 4000 characters, preventing the ORA-01489 error from being raised.

The ON OVERFLOW clause offers several options to manage overflow situations:

• ON OVERFLOW ERROR: This is the default behavior, triggering an error if the result overflows.
• ON OVERFLOW TRUNCATE 'StringYouLike': Appends ‘StringYouLike(Count)’ at the end of the truncated string.
• ON OVERFLOW TRUNCATE '': Displays the first 4000 characters without any additional terminating string.
• ON OVERFLOW TRUNCATE WITH COUNT: Appends the total character count at the end, for example, ‘…(5512)’.

References

Stack Overflow.

When you are create an object in Oracle Database, default object type will be marked editionable. From 12c onwards, you can mark the objects as noneditionable , and same you cannot edit with create or replace . For altering the same you have to alter the object and change to editionable.

This is applicable to View, Funcitons, Procedure, Trigger, Library, Type.

create or replace noneditionable procedure test_proc ( p int ) as
begin
  null;
end;

select editionable from user_objects
where  object_name = 'test_proc ';

//result
EDITIONABLE   
N

create or replace procedure test_proc ( p int ) as
begin
  dbms_output.put_line ( 'Editionable version' );
end;

//result
ORA-38824: A CREATE OR REPLACE command may not change the EDITIONABLE property of an existing object.

With below you can alter the procedure.

alter procedure test_proc editionable;

create or replace procedure test_proc ( p int ) as
begin
dbms_output.put_line ( 'Editionable version' );
end;

exec test_proc (1);

//result
Editionable version

References

Oracle Doc