[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Creating a View to call data from a function

Posted on 2008-02-07
10
Medium Priority
?
2,670 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:flextron
  • 4
  • 4
  • 2
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20848245
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
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20848414
Could post the function code pls?
0
 

Author Comment

by:flextron
ID: 20851370
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 240 total points
ID: 20851412
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
 

Author Comment

by:flextron
ID: 20851850
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
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 210 total points
ID: 20851922
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20852183
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
 

Author Comment

by:flextron
ID: 20852538
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20853050
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
 

Author Closing Comment

by:flextron
ID: 31429084
Thanks Guys....Its working now...Thanks for the Response.!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question