Total Pageviews

August 12, 2017

8/12/2017 10:21:00 PM


Oracle Sql Queries 



Oracle SQL queries
How to find first day and Last day of previous month


 Last_Day(ADD_MONTHS('YOUR DATE',-1)); - Last day 
 Last_Day(ADD_MONTHS('YOUR DATE',-2))+1; - First day 



How to retrieve only name from email id and truncating the data?


select substr(email_address,1,instr(email_address,'@')-1 ) from fnd_user 




Generating  XML from oracle data

Essentially, use @ before the column you want as attribute.
qryCtx := dbms_xmlgen.newContext('SELECT empno "@empno", ename, job from scott.emp where rownum <5'); 


Passing parameters to a select statement column list


P2 DATE,
P3 VARCHAR2,
P4 DATE,
P5 VARCHAR2,
P6 DATE,
P7 VARCHAR2,
P8 VARCHAR2,
P9 VARCHAR2) IS

trackings sys_refcursor;
currenttracking W431TRACKING%ROWTYPE;
stmt varchar2(2000);

BEGIN
dmsmsg.init;

begin
stmt = 'select
'||P1||' , ' ||P2||' , ' ||P3||' , ' ||P4||' , ' ||P5||' ,
' ||P6||' , ' ||P7||' , ' ||P8||' , ' ||P9||
'from tracking '
|| 'where
CREATEDATE = TRUNC(sysdate) '
|| 'OR
CHANGEDATE = TRUNC(sysdate)';
execute
immediate stmt;
loop
OPEN trackings FOR stmt ;
fetch trackings INTO currenttracking;
exit when trackings%notfound;
begin
null;
end;


end loop; 

 
Related Posts Plugin for WordPress, Blogger...