Re: PROMPT in a VIEW

On Mar 14, 4:00 pm, travelling..._at_yahoo.co.uk wrote:
> Hello
>
> I want to create a view so that when it is run it prompts for the user
> to enter a patient number and display their details. This is what i
> have got ...
>
> DROP VIEW view2;
> CREATE VIEW view2 AS
> SELECT OTHERNAMES, SURNAME, SEX, DOB, ADDRESS1, ADDRESS2,
> Physicianname
> FROM PATIENTVISIT, PHYSICIAN
> WHERE PATREFNO = &&PATREFNOIN;
>
> But when the view is created it prompts for a number and then saves it
> so when the view is run it only shows that 'patient' when i want it to
> always prompt for the patient number.
>
> UNDEFINE PATREFNOIN; Will clear it but i know this can't be used
> within a select or create view.
>
> Any ideas anyone?

Write a pipelined function and cast it as a table in a select, like the function dbms_xplan.display. As you've already seen you cannot use such variables in a view. I'd go to www.psoug.org, find Morgan's Library and browse through the examples under "Pipelined table functions".

David Fitzjarrell Received on Wed Mar 14 2007 - 16:09:10 CDT