1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error ORA 06502 and 00923

Discussion in 'SQL PL/SQL' started by pusuma, Feb 20, 2015.

  1. pusuma

    pusuma Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Code (Text):
     
    Hi I got an error when I execute my procedure

    ORA 00923 From Keyword not found expected
    ORA 06502 Numeric or Value error character string buffer too small

    These are the Errors I get while I execute my procedure
    I'm so sorry to post my procedure as it is too long.

    The problem is when I Change my V_ddl as varchar2(20000), I get error ORA 00923 and
    if I change back V_ddl as varchar2(10000) , I get error ORA 06502 ,
    Here is my procedure, someone please help to figure out this problem , really appreciate it.


    CREATE OR REPLACE PROCEDURE DEV1
    AS

    V_DDL VARCHAR2(10000);

    BEGIN

    V_DDL := 'CREATE TABLE EMP AS select..................';


    EXECUTE IMMEDIATE V_DDL;
    -- COMMIT;
    END;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We can't help you until you provide the whole create table statement you're assigning to V_DDL.
     
  3. pusuma

    pusuma Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi I'm sorry , my statement is too long, I execute this query everything was fine, but when I execute within procedure , I have problem.

    Please find the code from attachment as not allow me to post Seems to be Spam error

    Thank you
     

    Attached Files:

  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    your query not readable.....bring it into a readable look

    what this :
    1)
    - ?
    2) ''2014' - ???
    using the quoting mechanism in dynamic sql



    Code (SQL):

    DECLARE
     v_sql VARCHAR2(1024);
     v_cnt PLS_INTEGER;
    BEGIN
         v_sql := q'[SELECT COUNT(*) FROM user_objects WHERE object_type = 'TABLE']';
    EXECUTE IMMEDIATE v_sql INTO v_cnt;  
    DBMS_OUTPUT.PUT_LINE(         TO_CHAR(v_cnt) || ' tables in USER_OBJECTS.' );
    END;
    /
     
     
  5. pusuma

    pusuma Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I just found out that my procedure works well without HREMP file . when I join with that one I got an error. I'm thinking to join with HREMP file in another procedure , not sure yet, have to try.

    Thank you