how to compile a stored procedure via Crystal Reports

[Former Member]

I have a stored procedure in my database 

create or replace PROCEDURE TEST(cur IN OUT SYS_REFCURSOR  ,   parValue IN varchar)

AS

BEGIN

open cur for

select * from employment where first_name=parValue;

END TEST;

where Employment table fields are:

id     first_name     last_name     salary    city

1    a           b           23       c

6    a           b           23       c

3    a           b           23       c       

7    farhan    anasri    1000    meerut

6    kokab    anasri    1000    meerut

6    kokab    anasri    1000    meerut

1    suhail    ansari    2345    meerut

after adding a Add Command (My query goes like this : SELECT DISTINCT(id) FROM employment)

and the SP into CR , a prompt showing  drop Down list appears showing the ID's

if i insert a new record into the employment table, i have to ensure that particular value also appears in the DROP DOWN LIST, which is only feasible if i recompile that SP in SQL PLUS/SQL DEVELOPER/pl/sql DEVELOPER at this stage.

The end users who would be viewing the report ,how would they be able to see the changes when they refresh the report and the prompt appears.

ANY SUGGESTIONS ON THAT?

[abhilash_kumar]

Hi Farhan,

You don't need to re-compile the Stored proc as the List of Values are not being sourced from any field in the Stored Proc.

For the List of Values you have created another Command Object and all you need to do is refresh the report to see the new values in the list.

-Abhilash