DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Identifier is too long error

  1. #1
    Join Date
    Dec 2008
    Posts
    6

    Identifier is too long error

    I am running the below script and continue to get the below error. According to research I have conducted online, either table name or one of the column has more than 30 characters name. But I think there should be a go around - any idea?

    Error: "Identifier is too long"

    My code:
    DECLARE
    X NUMBER;
    BEGIN
    SYS.DBMS_JOB.SUBMIT
    (
    job => X
    ,what => execute immediate 'create table traffic_test as SELECT* FROM data.tbl_traffic WHERE upper(SUBSTR(controlnumber,-4,4))' = '001"'"W';
    delete from data.tbl_traffic;
    commit;'
    ,next_date => to_date('17/12/2008 14:22:02','dd/mm/yyyy hh24:mi:ss')
    ,interval => 'NEXT_DAY(TRUNC(SYSDATE), ''SUNDAY'')'
    ,no_parse => FALSE
    );
    :JobNumber := to_char(X);
    END;
    /

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    put the code you want into a procedure and put that in the what field

  3. #3
    Join Date
    Dec 2008
    Posts
    6

    Error: "Identifier is too long"

    Yes I may create a procedure, but I don't understand what you mean by "put that in the what field".

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Your syntax is wrong... no space in between "SELECT" and "*", look closely.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You were missing a space between the select and *. You should have made this a procedure, you didn't next the next date parameter, you should have added one to the sysdate so that it resets at the same time the next week, you also need to change the 17/24 to be what ever time of day you want it to run. If you do all of that it should run at the same time every week.

    Code:
    CREATE PROCEDURE recreateTrafficTable
    AS
    BEGIN
       EXECUTE IMMEDIATE 
       'create table traffic_test as '   ||
       ' SELECT * FROM data.tbl_traffic '|| 
       ' WHERE upper(SUBSTR(controlnumber,-4,4)) = ''001''';
    
       DELETE data.tbl_traffic;
       COMMIT;
    END recreateTrafficTable;
    /
    
    
    
    DECLARE
    X NUMBER;
    BEGIN
    SYS.DBMS_JOB.SUBMIT ( 
    job => X, what => recreateTrafficTable,
    interval => 'NEXT_DAY(TRUNC(SYSDATE + 1), ''SUNDAY'')+17/24',
    no_parse => FALSE);
    :JobNumber := to_char(X);
    END;
    /
    Have you thought a partitioning the table by date range and then you can simply drop partitions that are too old?

  6. #6
    Join Date
    Dec 2008
    Posts
    6

    ?????

    Thank you very much - I am out of the office today and will be back tomorrow.

  7. #7
    Join Date
    Dec 2008
    Posts
    6
    gandolf989,

    I really apprecaite your help - I was able to run my scritp sucessfully.

    I was just a little supprised how did not see that coming. Anyway thank you so much.

    Albert

Posting Permissions

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


Click Here to Expand Forum to Full Width