Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: Views in Stored Procedures?!?!?

    I'm trying to create a view inside a procedure using something like this

    PROCEDURE myProc (inVal Number) IS
    BEGIN
    CREATE OR REPLACE VIEW qry_myProc AS
    SELECT
    table1.PC,
    FROM table1
    ORDER BY table1.PC;

    END myProc;

  2. #2
    Join Date
    Oct 2003
    Posts
    12

    Oops!

    Sorry that sent on me half way through. Like I said trying to create a view in a procedure, and having real problems.

    Any ideas?

  3. #3
    Join Date
    Aug 2003
    Posts
    41
    Hi,

    You cant use DDL statements in pl/sql directly. You could use dynamic SQL procedures to do this. like,

    PROCEDURE myProc (inVal Number) IS
    BEGIN
    EXECUTE IMMEDIATE('CREATE OR REPLACE VIEW qry_myProc AS
    SELECT
    table1.PC,
    FROM table1
    ORDER BY table1.PC');

    END myProc;

    -Sunil.

  4. #4
    Join Date
    Oct 2003
    Posts
    22

    Re: Views in Stored Procedures?!?!?

    Follow sunilthomus's solution.
    OR-
    Try to create the view first & then call it in the procedure.
    View is a different database object.

    CREATE OR REPLACE VIEW qry_myProc
    AS

    SELECT column_list
    FROM table1
    ORDER BY column;

    END;

    CREATE PROCEDURE myProc (inVal Number) IS
    BEGIN

    call_the_view_here;

    END myProc;

    Also declare variable inVal in the procedure.
    Last edited by stephen_pe; 10-16-03 at 15:52.

  5. #5
    Join Date
    Oct 2003
    Posts
    12

    Re: Views in Stored Procedures?!?!?

    Cheers lads! That worked a treat!

Posting Permissions

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