Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: dbms_gather_stats throws identifier must be declared error

    Hi All,

    can you please help me find whats wrong here -

    CREATE OR REPLACE PROCEDURE dyn_test
    AS
    l_mysql varchar2(4000);
    l_nperiod number(10) := 200901;
    l_tabprtn varchar2(20);

    BEGIN
    l_mysql :=
    'begin'
    || ' dbms_stats.gather_table_stats'
    || ' ( ownname=> ''ABC'' ,tabname => ''TABA'','
    || ' partname=>'
    || 'TAB1_'
    || l_ncurrentperiod --200901
    || ', CASCADE=> TRUE, estimate_percent=> 10,'
    || ' DEGREE=> 8, no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,'
    || ' granularity=> ''PARTITION'', method_opt=> ''FOR ALL COLUMNS SIZE AUTO'');'
    || 'END;';

    dbms_output.put_line(l_mysql);
    EXECUTE IMMEDIATE l_mysql;
    END;
    When I compile this procedure, it gets created but when I execute it I get the below error.

    BEGIN
    ABC.DYN_TEST;
    COMMIT;
    END;

    Error at line 1
    ORA-06550: line 1, column 105:
    PLS-00201: identifier 'TABA_200901' must be declared
    ORA-06550: line 1, column 8:
    PL/SQL: Statement ignored
    ORA-06512: at "ABC.DYN_TEST", line 22
    ORA-06512: at line 2
    I declared the parameter '200901' although i hardcoded to check if its working fine. What else is wrong here?

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What else is wrong here?
    What you really invoked is not what you posted since no DBMS_OUTPUT got displayed.

    prove single static invocation works to yourself & us then compare to the dynamic string.
    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
    Jul 2005
    Posts
    276
    "What you really invoked is not what you posted..... " -- I didnt follow what you are trying to say here. Can you please elaborate.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You need to put quotes around the partition name in the string.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Or, the best, call GATHER_TABLE_STATS statically, as there is no reason for using EXECUTE IMMEDIATE in this example.

    To the current error: you should enable DBMS_OUTPUT in your client to see the generated SQL statement. Note the difference between
    partname=>TAB1_200901 (here TAB1_200901 is identifier) and
    partname=>'TAB1_200901' (here 'TAB1_200901' is literal).

    But, as here is no need to dynamic call, you should not use it.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    you are like recreating the wheel when a wheel is right in front of you.
    A simple call to the original procedure would suffice.

    Code:
    begin
    dbms_stats.gather_table_stats (
    ownname => 'OWNER1', 
    tabname =>'TABLE1', 
    estimate_percent => 20); 
    END;
    /
    this is the exact same as the complicated thing you are trying to do.
    it's not like you are passing in any variables. the variables you have are hardcoded.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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