Error PLS-00302: Component must be declared

Error PLS-00302: Component must be declared

select xx.maxno from dual

Where 'maxno' is the function in other schema 'XX'

and we are getting the error PLS-00302.

There are many reasons for this error :-

One can be 'maxno' exists in the current schema
or

SELECT * from all_objects where object_name like 'XX'

If yes this error will come ...so either you have to change the schema name or change the duplicate
object name in your current schema.Hope it will solve your problem...

Error Frm-40505 :-Unable to perform query..

Error Frm-40505 :-Unable to perform query..

To check this error Press Ctrl+Shift+E. Mostly it is non-database item defined as database..or database item not defined but used in form.

How to write format trigger on report based on page number

we can decide what to hide and display in report based on the page number:-
function F_4FormatTrigger return boolean is
 pagenum   number;
begin
  srw.get_page_num(pagenum);
  if pagenum = 1  then
    return (TRUE);
  else
    return (FALSE);
  end if;

end;

Oracle query optimization tips

Query optimization tips :-

1.Rewrite complex subqueries with temporary tables.

2.Use minus instead of EXISTS subqueries

3.Use SQL analytic functions

4.Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins

5. Index all the comparing columns in query

6.Avoid the use of NOT IN or HAVING

7.Avoid the LIKE predicate

8.Use decode and case

9.Always use table aliases when referencing columns

10.Never mix data types

comparision between regexp_like and like statements

Search using like is always faster than the regexp_like.
REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE.
This condition evaluates strings using characters as defined by the input character set.

LIKE syntax for pattern is simple and supports a small set of wildcards,
but does not support the full regular expression syntax.

Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.
LIKE calculates strings using characters as defined by the input character set

how to get the first_date and last_date of the months between the given time period

how to get the first_date and last_date of the months between the given time period

We have a start date and end date given as an input .We have to calculate the number of months and start date ,end date of each month.
The following query is used to calculate the above requirement:-

SELECT ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) start_date
,last_day(ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1) ) end_date
FROM   DUAL
CONNECT BY ADD_MONTHS(TRUNC(TO_DATE('02-Mar-2014', 'DD-MON-YYYY'), 'MON'), ROWNUM - 1)
    <= TRUNC(TO_DATE('29-Jun-2014', 'DD-MON-YYYY'), 'MON')


and the output will be like:-
start_date end_date

01/03/2014|31/03/2014
01/04/2014|30/04/2014
01/05/2014|31/05/2014
01/06/2014|30/06/2014

How to display bar-code format value in Oracle reports?

Sometimes some key fields are required to be displayed in a bar-code format. One thing we should have the font bar code 39 .. concatenate the value to be displayed as bar code with '*' .
for example I want to display employee_code as bar code format then in a formula column write '*'||employee_code||'*' and assign this value to the bar code field. Then in the output we can see bar code formatted field value!!