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

Using WITH, DECODE functions in sql

       
 WITH Q1 AS
(SELECT unique_id,status from table group by unique_id having count(unique_id)=1)

SELECT * FROM Q1
WHERE unique_id IN
            (SELECT unique_id FROM Q1 WHERE  status = 'Approve'
            AND EXISTS (SELECT 1 FROM Q1 subq1
                        WHERE Q1.emp_id = subq1.emp_id
                        AND TRUNC(Q1.status_date) < TRUNC(subq1.insert_date)) 
            )                 
ORDER BY Q1.unique_id,decode(status,'Approve',1),status_date;
       

How to insert data into a table on Oracle database

       
INSERT INTO TABLE_NAME (Column1, Column2, Column3)

     Select TABLE1_SEQ.NEXTVAL, NewColumn2, NewColumn3

  FROM TABLE1  WHERE Column4 IN (X,Y) AND SomeCondition;