Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    india,hyderabad
    Posts
    43

    Unanswered: re: need help with ORA-00947

    hi! ,

    I am using sqlplus ...

    I have a procedure x in user abc.
    I am trying to create the same procedure x in user jkl.
    I selected the text from user_source into notepad and saved it
    as a sql file.

    The formatting got all screwed up so I tried my best to format it
    and run the sql file from the sqlplus prompt of user jkl.

    I am geting the following errors ..

    SQL> @c:\tsrtrafficdtalinking.sql

    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE TSRTRAFFICDATALINKING:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    133/20 PL/SQL: SQL Statement ignored
    133/36 PL/SQL: ORA-00947: not enough values
    167/23 PL/SQL: SQL Statement ignored
    167/39 PL/SQL: ORA-00947: not enough values
    211/23 PL/SQL: SQL Statement ignored
    211/39 PL/SQL: ORA-00947: not enough values
    223/23 PL/SQL: SQL Statement ignored
    223/39 PL/SQL: ORA-00947: not enough values
    262/26 PL/SQL: SQL Statement ignored
    262/42 PL/SQL: ORA-00947: not enough values
    275/26 PL/SQL: SQL Statement ignored
    275/42 PL/SQL: ORA-00947: not enough values

    How do I rectify this ???

    Is there any where of directly creating the procedure in user jkl
    without having to create a sql file and then running the sqlfile ..??

    thanks,
    harish


    __________________
    harry

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Extracting the code from USER_SOURCE should work well enough, if you do it like this:

    SET LINESIZE 1000 PAGESIZE 0 TRIMSPOOL ON FEEDBACK OFF

    SELECT text
    FROM user_source
    WHERE name = 'X'
    AND type = 'PROCEDURE'
    ORDER BY line

    SPOOL x.sql
    /
    SPOOL OFF

    You may just need to clean up the top and bottom of the file to remove "SQL>" prompts etc.

  3. #3
    Join Date
    Sep 2003
    Location
    india,hyderabad
    Posts
    43

    re: still getting the same error messages

    is there any other way to do this ??????
    harry

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If you have Toad (which as far as I know you can still download for free from www.quest.com) then you can view the source and just copy and paste it.

  5. #5
    Join Date
    May 2004
    Posts
    29
    you can also download at http://www.toadsoft.com/

  6. #6
    Join Date
    Sep 2003
    Location
    india,hyderabad
    Posts
    43

    Problem Was ..

    FYI,

    the problem was an insert statement in the code that was inserting four values into five columns ....

    i thought it was a formatting error
    harry

  7. #7
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You can always list your columns in the INSERT statement as:

    INSERT INTO test (col1,col2,col3) VALUES (v_col1, v_col2, v_col3);

    It works if the rest of the columns are not "NOT NULL" by definition.

    HTH,

    clio_usa - OCP 8/8i/9i DBA

Posting Permissions

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