Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: Create Oracle Dynamic View

    Hi Expert,

    I have queries on creating a dynamic Oracle view which will aggregate all of the view into one single view for reporting purpose.

    Currently, my system will generate multiple view for one day. For example, the view auto generated by the system will be:

    1) CDR_DETAILS_20090501_0001_View
    2) CDR_DETAILS_20090501_0002_View
    3) CDR_DETAILS_20090501_0003_View
    4) CDR_DETAILS_20090502_0001_View
    5) CDR_DETAILS_20090503_0001_View
    6) CDR_DETAILS_20090504_0001_View
    7) CDR_DETAILS_20090504_0002_View

    The view above are generated dynamically by the system and each day may have max of five view or min of one view which indicated by '0001 - 0005'.

    Now, i wish to aggregate all of the view above into one single view which will combine all of the seven views above into one single view called "CDR_VIEW_ALL'. This is because we do not know how many views will be generate by system per day. I am using Oracle SQL Developer 1.5.4 (Oracle 10g) as my development database.

    In order to solve this, i have created a stored procedure in order for me to schedule the procedure to run everyday for get the new view for each day. However, i encountered some issues which is the procedure created was invalid. Below are my procedure script:

    CREATE OR REPLACE PROCEDURE CDR_PROCEDURE

    IS
    SQLSTRING VARCHAR2(2000);
    RECCNT NUMBER;
    CURCNT NUMBER;

    BEGIN
    EXECUTE IMMEDIATE 'DROP VIEW CDR_VIEW_ALL;

    SQLSTRING := 'CREATE OR REPLACE VIEW CDR_VIEW_ALL AS ';

    SELECT COUNT(1) INTO RECCNT
    FROM ALL_OBJECT
    WHERE OBJECT_TYPE='VIEW' AND OBJECT_NAME LIKE 'CDR_DETAILS_%';

    FOR REC IN (SELECT OBJECT_NAME FROM ALL_OBJECT WHERE OBJECT_TYPE='VIEW' AND OBJECT_NAME LIKE ‘CDR_DETAILS_%')
    LOOP
    CURCNT := CURCNT + 1;
    IF CURCNT < RECCNT THEN
    SQLSTRING := SQLSTRING || ' SELECT * FROM ' || REC.OBJECT_NAME || ' UNION ALL ';
    ELSE
    SQLSTRING := SQLSTRING || ' SELECT * FROM ' || REC.OBJECT_NAME;
    END IF;
    END LOOP;

    EXECUTE IMMEDIATE SQLSTRING;
    END CDR_PROCEDURE;

    Appreciated anyone know this could advice me soonest possible.

    Many thanks.

    Regards,
    CK

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to fix the syntax problem(s).

    If you use SQL*Plus, it will identify where problem exists.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Hi,

    Thanks for your prompt reply.

    Can you briefly tell me where i have goes wrong on my scripts?

    I pasted it on SQL*PLUS and it stated insufficient privilege.

    Need your kind advice.

    Thanks.

    Regards,
    CK

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Need your kind advice.
    Code correctly & no advice is needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In most programming languages the number of opening parentheses, brackets, quotation marks etc. in any routine should be equal to the number of closing parentheses, brackets, quotation marks etc...
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ckchwa
    Can you briefly tell me where i have goes wrong on my scripts?

    I pasted it on SQL*PLUS and it stated insufficient privilege.
    I would say that Oracle stated it clearly enough (with important details like the number of line where it occured): the user has insufficient privileges to run "it". Depending on "it" (CREATE PROCEDURE statement?), it may be CREATE PROCEDURE, SELECT ON all_objects or CREATE VIEW privilege. Simply grant the one the user lacks.

    Be aware, that the last two privileges have to be granted directly to the owner of the procedure (not through role). You may study details e.g. in this thread on AskTom: http://asktom.oracle.com/pls/asktom/...:1065832643319.

Posting Permissions

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