How to find last day of the past month and First day of the past Month

Requirement:- To find last day of the previous month and First day of the previous Month.

Solution: 

1) This can be achieved using TRUNC function as below.

SELECT TRUNC(TRUNC(SYSDATE, 'MM') - 1, 'MM') "FIRST DAY OF PREVIOUS MONTH",
                TRUNC(SYSDATE, 'MM') - 1 "LAST DAY OF PREVIOUS MONTH" ,                         TRUNC(SYSDATE) "Today Date"   FROM DUAL






2)  Can be achieved using "ADD_MONTHS and LAST_DAY" function as below.

   SQL>SELECT TRUNC(LAST_DAY(ADD_MONTHS(sysdate,-1))) from dual

Easy way to find DML operation Issues

Lets see easy method to find-out root cause of any DML operation easily by following below steps:


Requirement   is to track DML operation errors in one unique table.
Lets say i have below  table.



CREATE TABLE TRANS(T_ID INTEGER,AMOUNT NUMBER(10,2))

alter table TRANS add constraint T_ID_PK primary key(T_ID);


insert into TRANS values (1,21);
insert into TRANS values (5,5.75);

Commit;

Click on below images to get enlarged size.
 

Update Scenario:

CONTINUE Statement

We are having following statements:-
1) CONTINUE
2) CONTINUE WHEN

1) CONTINUE:-

The CONTINUE statement makes to skip the Current iteration of the loop UNCONDITIONALLY and then transfers to next iteration of the current loop.

Syntax: continue;

Eg:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2    FOR VENKY_IDX IN 1 .. 3 LOOP
  3      DBMS_OUTPUT.PUT_LINE(' VENKY_IDX BEFORE ' || VENKY_IDX);
  4      CONTINUE;
  5      DBMS_OUTPUT.PUT_LINE(' VENKY_IDX AFTER ' || VENKY_IDX);
  6    END LOOP VENKY_IDX;
  7    DBMS_OUTPUT.PUT_LINE('AFTER LOOP');
  8  END;
  9  /

 VENKY_IDX BEFORE 1
 VENKY_IDX BEFORE 2
 VENKY_IDX BEFORE 3
AFTER LOOP

PL/SQL procedure successfully completed
2) CONTINUE WHEN:-

Its same as  'CONTINUE' statement but here we can specify any condition to meet that criteria to skip the current iteration and transfer it to next iteration of the current loop.

Syntax: continue when;

Eg:

SQL> BEGIN
  2    FOR VENKY_IDX IN 1 .. 3 LOOP
  3      DBMS_OUTPUT.PUT_LINE(' VENKY_IDX BEFORE ' || VENKY_IDX);
  4      CONTINUE
  5       WHEN VENKY_IDX =2;
  6      DBMS_OUTPUT.PUT_LINE(' VENKY_IDX AFTER ' || VENKY_IDX);
  7    END LOOP VENKY_IDX;
  8    DBMS_OUTPUT.PUT_LINE('AFTER LOOP');
  9  END;
 10  /

 VENKY_IDX BEFORE 1
 VENKY_IDX AFTER 1
 VENKY_IDX BEFORE 2
 VENKY_IDX BEFORE 3
 VENKY_IDX AFTER 3
AFTER LOOP

PL/SQL procedure successfully completed


In above example when venky_idx becomes 2 then its skipping to execute "DBMS_OUTPUT.PUT_LINE(' VENKY_IDX AFTER ' || VENKY_IDX);" .

Basically Continue when statement will be used to skip some execution if any requirement meets or doesn't meet as above eg.


Flow of Continue statement:-

Restrictions on CONTINUE Statement:-

• A CONTINUE statement must be inside a LOOP statement.
• A CONTINUE statement cannot cross a subprogram or method boundary.
  
  
  
  

Resolved Install has encountered an error while attempting to verify your virtual memory settings. Please verify that the sum of the initial sizes of the paging files is at least 256 MB

While installing forms ,we may get following error "Install has encountered an error while attempting to verify your virtual memory settings. Please verify that the sum of the initial sizes of the paging files is at least 256 MB".

Solution: 

1    1)  Change the specified parameter -->  Computer --> Properties --> Advanced System Settings --> Advanced --> Performance Setting --> Advanced --> Change

Un check Automatically manage paging , and select Custom Size.

Custom the size as shown below and click on Set

          2) Restart the system and retry installation

Solving Abnormal program termination. An internal error has occured in Forms installation

We might face below issue while installing forms software.

 

 Solution:

Go to setup.exe and right click  then select properties and check the box as below to solve this issue.

 

 Once you retry installation issue will be resolved and will get below screen.

 

 

 

Solving operating system version: must be 5.0, 5.1, 5.2. Actual 6.1 Failed in Win - 7 (64 Bit)

You might face an issue while installing Oracle 10g forms as below.

So lets go software folder and search for "oraparam.ini" file and edit it and then
modify as below to accept 6.1 version.

Ideally this 'oraparam.ini' file will be present above your "setup.exe" file which we use for installation.

[Certified Versions]
Windows=5.0,5.1,5.2,6.1

then save and retry installation.

 

How to find used tables from wrapped packages

We may encounter some situation ,where client code is completely wrapped and situation may come to know table names used for that package without unwrapping code.


Below query will help us to know some information for it.

SQL> SELECT * FROM USER_DEPENDENCIES 
                        WHERE TYPE ='PACKAGE BODY'                 
                        AND REFERENCED_OWNER = '&OWNER_NAME' 
                        AND NAME = '&NAME_OF_THE_OBJECT';



as we all know, In Normal unwrapped code , below query will help us to know anything.

SQL> select * from user_source 
               where TEXT LIKE '%&Enter_Any_String_Which_You_Want_to_Search_in_Complete_Schema%'

and Same way you may want to find DB Link where its used in your Database objects.

We can use following query to find DB link used objects:-



SQL> SELECT * FROM USER_DEPENDENCIES 
      WHERE REFERENCED_LINK_NAME='db link name';