Creating a View to call data from a function

Hi Gurus,
I want to create a view in Oracle which will get data by calling a fuction named "Comment". Comment has four columns in it and I want the exact same columns in my view.
Please help me in this matter.
Thanks
Flextron
Flex TronDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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);

Open in new window

0
Jinesh KamdarCommented:
Could post the function code pls?
0
Flex TronDeveloperAuthor Commented:
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;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do it like below.
however, I guess you want to replace the string values I suggested by some other values?!
CREATE VIEW vw_hdr_comments 
AS
SELECT f_hdr_comments( 'p_ccn', 'p_type', 'p_id', 'p_pos' ) 
FROM DUAL
;
 
 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Flex TronDeveloperAuthor Commented:
Hi Angellll,
Thanks for the reply.But...
The toad is throwing  syntax error when I try to validate the  code as mentioned by you.
0
Jinesh KamdarCommented:
Try something like this. Note that i assume p_ccn, p_type, p_id, p_pos are columns coming from yourtablename.

CREATE VIEW vw_hdr_comments
AS
SELECT f_hdr_comments( p_ccn, p_type, p_id, p_pos )
FROM yourtablename;
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
;

Open in new window

0
Flex TronDeveloperAuthor Commented:
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

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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?
0
Flex TronDeveloperAuthor Commented:
Thanks Guys....Its working now...Thanks for the Response.!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.