EXECUTE IMMEDIATE question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: EXECUTE IMMEDIATE question

Hybrid View

  1. #1
    Join Date
    May 2001
    Posts
    285

    Question EXECUTE IMMEDIATE question

    Hi gurus,

    I'd like to create a view in a block using dynamic sql (execute immediate statement, so I can catch error and do other house keeping in case an error occurs. But the definition of the view is so long that it's over 5000 characters -- far more than the 2000 limit for char/varchar data types in PL/SQL. So whenever I tried to create the view using 'execute immediate', it throws out error, but if I simply run the create view statement itself, it worked great.

    Is there anyway for me to overcome this problem?

    FYI -- when I create the view using dynamic sql, the error message it threw out was 'PLS-00382: expression is of wrong type'. I was thinking it should say something like 'over the limit' etc. So did I miss anything here?

    Thanks a bunch,
    Elaine

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    VarChar2 limit in PL/SQL is 32,767 bytes.

    Can you post the code? (feel free to trim down that view def'n!)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2001
    Posts
    285
    Originally posted by slimdave
    VarChar2 limit in PL/SQL is 32,767 bytes.

    Can you post the code? (feel free to trim down that view def'n!)
    Thanks a lot for your reply! I revisited the views I have and was able to make all of them work except one (the longest one).

    Here is the stored procedure I have (and the dynamic sql is inside the stored procedure) --


    CREATE OR REPLACE PROCEDURE crView
    (i_vname IN CHAR,
    i_vstring IN CHAR,
    i_product IN CHAR,
    i_status IN CHAR)
    IS
    v_rcnt INT := 0;

    BEGIN
    IF (i_vname IS NULL) THEN
    RETURN;
    END IF;
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || i_vname || ' AS ' || i_vstring; -- dynamic sql

    spinin_update_CkSum (i_product, i_status);

    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(sqlerrm);
    END crView;
    /


    Here is where I got error --

    begin
    CRVIEW('View_Name(list_of_column_names)' , 'view_definition_which_is_about_4000_chars',other_Param','other_param');
    exception
    ..
    end;
    /


    Note: In above 'view_definition_which_is_about_4000_chars', there is a UNION operator inside. Could that be the cause? I don't think so, but I can't find other reason that prevent this from working.

    Here is the error message I got --

    ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    Thanks a lot!!!

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You're missing a quote before other_Param, but that might just be a forum typo?

    Perhaps you could change the CHAR declarations to varchar2, just to see what that does.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2001
    Posts
    285
    Originally posted by slimdave
    You're missing a quote before other_Param, but that might just be a forum typo?

    Perhaps you could change the CHAR declarations to varchar2, just to see what that does.
    Yeah, that missing quote is a typo. And I tried to change that to varchar2, still got the same error

    Any other thoughts?

    Millions of thanks!

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm wondering whether the problem is not with the execute immediate, but with the view definition itself.

    Have you tried creating the view directly through SQL*Plus?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    May 2001
    Posts
    285

    Unhappy

    Originally posted by slimdave
    Have you tried creating the view directly through SQL*Plus?
    Yes I did, and that worked just fine.

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Create a dummy table and insert the SQL_STMT which u r creating dynamically using execute immediate.

    then try executing the generated script from the table...
    Cheers!
    OraKid.

  9. #9
    Join Date
    May 2001
    Posts
    285

    Smile

    In case you are still wondering this, I got this one solved by removing all the unnecessary parenthesis from the view definition. Those parenthesis don't seem to be a problem when I run the query from sql+ directly, but it kept giving me that weird error when I run it from the store procedure (crView) I wrote.

    Don't know how to explain this, but hey, it's now working like a charm

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