If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > a view in a package for oracle

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-20-03, 09:01
Naweed Naweed is offline
Registered User
 
Join Date: Nov 2003
Posts: 76
Question a view in a package for oracle

Ho do I create a view inside a package.
Reply With Quote
  #2 (permalink)  
Old 11-20-03, 09:23
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Re: a view in a package for oracle

Use EXECUTE IMMEDIATE:

BEGIN
...
EXECUTE IMMEDIATE 'create view v_emp as select * from emp';
END;

But why would you want to do that?
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old 11-20-03, 14:35
Naweed Naweed is offline
Registered User
 
Join Date: Nov 2003
Posts: 76
I have to create install scripts for these, because they need to go into various schemas. along with functions and procedures. So I wanted to bundle them all up. That way a package can stay in the schema for future reference. Or can we store scripts on the schema.
Why, is this in-efficient?
Reply With Quote
  #4 (permalink)  
Old 11-21-03, 05:17
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
No, I thought maybe you were creating views dynamically from your application. Normally packages are used for code that will be run many times. What you are doing is unusual, but harmless.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old 11-24-03, 11:26
Naweed Naweed is offline
Registered User
 
Join Date: Nov 2003
Posts: 76
Question package header for this body.

Hi, I am unable to figure out how to invoke this view from the package specification. here is what I have

CREATE OR REPLACE PACKAGE ABC IS
end;
/

I know something has to identify the view in the header but I cant figure out how to do that.

CREATE OR REPLACE PACKAGE BODY ABC AS
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE VIEW ABCD
(LISTNAME, CODE, DESCRIPTION)
AS
SELECT
translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
translate(CODELKUP.CODE,"^`"""," ") CODE,
translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
FROM
CODELKUP';
END;
/
Reply With Quote
  #6 (permalink)  
Old 11-24-03, 11:41
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Re: package header for this body.

You need to create a procedure in the package like this:

Code:
CREATE OR REPLACE PACKAGE ABC IS
  procedure create_view;
end;
/

CREATE OR REPLACE PACKAGE BODY ABC AS

  procedure create_view is
  begin
    EXECUTE IMMEDIATE
     'CREATE OR REPLACE VIEW ABCD
    (LISTNAME, CODE, DESCRIPTION)
    AS 
    SELECT
    translate(CODELKUP.LISTNAME,"^`"""," ") LISTNAME,
    translate(CODELKUP.CODE,"^`"""," ") CODE,
    translate(CODELKUP.DESCRIPTION,"^`"""," ") DESCRIPTION
    FROM
    CODELKUP';
  end;

END;
/
You will have problems with those quotes though. Oracle uses only single quotes (') to delimit text strings.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On