Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: a view in a package for oracle

    Ho do I create a view inside a package.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    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?

  3. #3
    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?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  5. #5
    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;
    /

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •