Creating a View to call data from a function

assuming that the Comment function is using the "pipelined" technique to return the rows, see below code.
otherwise, you have to give the function header.

create view yourcommentview
AS
 select * from table(comment);

Select allOpen in new window

Could post the function code pls?

I have attached the function code. Now I have to call (p_cn, p_type,p_pos,p_id ) from this function to a view in Oracle.
Thanks

CREATE
OR REPLACE FUNCTION f_hdr_comments (
 
p_ccn
VARCHAR2,
 
p_type
VARCHAR2,
 
p_id
VARCHAR2,
 
p_pos
VARCHAR2
 
)
 
RETURN VARCHAR2
 
IS
 
v_result
VARCHAR2 (4000);
 
/******************************************************************************
 
NAME: f_hdr_comments
 
PURPOSE:
 
REVISIONS:
 
Ver Date Author Description
 
--------- ---------- --------------- ------------------------------------
 
1.0 9/11/2007 1. Created this function.
 
NOTES:
 
Automatically available Auto Replace Keywords:
 
Object Name: f_hdr_comments
 
Sysdate: 9/11/2007
 
Date and Time: 9/11/2007, 2:26:30 PM, and 9/11/2007 2:26:30 PM
 
Username: (set in TOAD Options, Procedure Editor)
 
Table Name: (set in the "New PL/SQL Object" dialog)
 
******************************************************************************/
 
BEGIN
 
v_result
:= '';
 
FOR i IN (SELECT hdr_comment
 
FROM hdr_cmnt g
 
WHERE g.cmt_ccn = p_ccn
 
AND g.cmt_type = p_type
 
AND g.cmt_id = p_id
 
AND g.cmt_pos = p_pos
 
ORDER BY hdr_cmnt_seq)
 
LOOP
 
v_result
:= v_result || ' ' || i.hdr_comment;
 
END LOOP;
 
RETURN v_result;
 
EXCEPTION
 
WHEN NO_DATA_FOUND
 
THEN
 
NULL;
 
WHEN OTHERS
 
THEN
 
-- Consider logging the error and then re-raise
 
RAISE;
 
END
f_hdr_comments;

Select allOpen in new window

Hi Angellll,
Thanks for the reply.But...
The toad is throwing  syntax error when I try to validate the  code as mentioned by you.

actually, we need to specify a column alias name for the view :

CREATE VIEW vw_hdr_comments 
AS
SELECT f_hdr_comments( 'p_ccn', 'p_type', 'p_id', 'p_pos' )  alias_name
FROM DUAL
;

Select allOpen in new window

Well, I tried Aliasing like this. But don't know why the syntax error is coming !
Jinesh, I don't get the "Your Table name" part of the code. I am not using any tables, all the data has to come from the Function "f_hdr_comment" into the view vw_hdr_comment

CREATE
OR REPLACE VIEW V_HDR_COMMENT
 
(
ccn,
type,
id,
pos
)
AS
 
SELECT f_hdr_comments ('p_ccn', 'p_type', 'p_id', 'p_pos')
 
FROM DUAL

Select allOpen in new window

your function returns only 1 value,  so you cannot "alias" as 4 columns.
please try my suggestion.

>all the data has to come from the Function
however, your function accepts 4 parameters, what values do you want to pass to the function?

Thanks Guys....Its working now...Thanks for the Response.!