1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

parameter in reports

Discussion in 'Oracle Forms and Reports' started by oraformsrep, Jul 24, 2012.

  1. oraformsrep

    oraformsrep Guest

    when user enter value in parameter it should filter records accordingly otherwise all records should display.
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Elaborate the quisition.
     
  3. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    correct the quistion! :)
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi,

    Look into the below logic may be it will helps you to build the query according to your requirement.
    Code (Text):

    declare
    emp_no number;
    enam varchar2(50);
    begin
    emp_no:=&Enter_emp_no;
        begin
        select ename into enam from emp where empno = emp_no;
        dbms_output.put_line('ename: '||enam);
        exception when others then
        for i in (select ename from emp) loop
        dbms_output.put_line(i.ename);
        end loop;
    end;
    end;
     
    Here emp table is taken as example. Here we have created one parameter to prompt employee number. If we have that employee number then it prints that employee name and if it doesn't then it prints all employee names. This was just an example which explains your exact scenario. Here why I have used exception means if we dont have any record with that parameter value (Employee number) then it throws error like no data found. So whenever it throws that error we can catch it through that exception block. If we don't write exception block then the entire pl/sql will fails.
     
  5. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    You can also try the following code.....

    declare
    emp_no number;
    enam varchar2(50);
    begin
    emp_no:=&Enter_emp_no;

    FOR i in (select ename from emp where empno = NVL (emp_no,empno)) LOOP
    dbms_output.put_line(i.ename);
    END LOOP;

    end;
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    oraformsrep is asking for "parameter in reports".
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    The query in for loop works fine when we provide value to emp_no parameter. If we don't provide any value to it, it wont return any thing. And it produces all rows from that query if we provide null as parameter. null means not empty field. It means we have to type null in parameter. Then it will return all values.

    Please post the things after testing it thoroughly.
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yeah kiran, Just logic I have explained how to go with it. If he posts exact requirement just like form name and which parameter he wants and what he wants exactly to display, by that we can do it in forms for him.