Get database name in Oracle

SELECT *       FROM   v$database

Set define off to ignore the bind parameters when executing a procedure Oracle

set define off

/*

Define the PLSQL spec & body

*/


show errors
SET DEFINE '&'
exit

How to execute procedure on TOAD editor Oracle SQL?

declare
test1 varchar(32000);
begin PACKAGE.PROCEDURE('DEC-13',1041,'VALUE',test1);
DBMS_OUTPUT.PUT_LINE(test1);
end;


PACKAGE.PROCEDURE(p_param1 IN VARCHAR2,
                                 p_param2 IN NUMBER,
                                 p_param3 IN VARCHAR2 , p_param4 OUT VARCHAR2)
//init
begin
for loop

end loop;
end;


 set SERVEROUTPUT ON
 declare v_period varchar2(10):='1';
      begin
   --   fnd_profile.get( 'BRVO_PL56_PERIOD', v_period );
      DBMS_OUTPUT.PUT_LINE(v_period);

      end;

execute() vs executeUpdate() callable statements

I get an error on executing a callable statement (it takes forever and the system hangs) where as when i use executeUpdate the callable statement executes fine

Search the text in DB schema

desc dba_source

select * from dba_source where upper(text) like '%ENTER THE TEXT YOU WANT TO SEARCH HERE%'

DISTINCT only for some columns

https://community.oracle.com/thread/1120347

select *
  from (select col1,
               col2,
               col3,
               col4,
               col5,
               col6,
               row_number () over (partition by col1, col2, col3, col4, col5 order by null) rn
          from table1)
 where rn = 1

Using Partition Over in Oracle

SELECT ID, OTHER_ID, DATE_VALUE, ROW_NUMBER() OVER (PARTITION BY OTHER_ID ORDER BY DATA_VALUE desc) R,COUNT(*) OVER (PARTITION BY OTHER_ID) FROM SOME_TABLE


**Row_Number gives the row number of the row in a table after partition by other_id
**Count(*) gives the count value of the rows partitioned by other_id