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;
