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!

ORA-00971 and ORA-06512 errors

Discussion in 'SQL PL/SQL' started by charlichaplin, Apr 6, 2010.

  1. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi
    I have 3 errors , whould you help me ?

    ORA-00971 : missing SET keyword
    ORA-06512 : at "SUGARCRM1.PROCEDUREELHAM" , line 14
    ORA-06512 : at line 2


    Code (SQL):


    CREATE OR REPLACE
    FUNCTION FunctionElham (NAMES IN VARCHAR2) RETURN VARCHAR2
      IS
      STR VARCHAR2(4000);
      BEGIN
      STR := TRIM(NAMES);
      STR := NVL(STR,NAMES);
      STR := REPLACE(STR, chr(55690) , chr(56204));
      STR := REPLACE(STR, chr(55977) , chr(55683));
      RETURN STR;
      END FunctionElham;




    CREATE OR REPLACE
    PROCEDURE ProcedureElham
     IS
    column_name varchar2(4000);
     TABLE_NAME varchar2(4000);
    cursor CursorELham IS
     SELECT c.column_name,c.TABLE_NAME
    FROM user_tab_columns c INNER JOIN user_tables t ON c.TABLE_NAME = t.TABLE_NAME
    WHERE data_type='NVARCHAR2' OR data_type='VARCHAR2';
    BEGIN
     OPEN CursorELham;
     loop
     fetch CursorELham INTO column_name,TABLE_NAME;
     exit WHEN CursorELham%NOTFOUND;
     EXECUTE immediate 'update ' || TABLE_NAME || ' set ' || column_name || ' = FunctionElham(' || column_name || ')';
     END loop;
     close CursorELham;
     END;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Looks fine.. Where did you get the error ?
     
  3. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi , Thanks for your attention.
    this is all of my solution , and when i run it for test data it works well and no problem , but when i check it for my real database those 3 errors occurres.

    Code (C):

     void CreateQuery()
            {
                string cmdQuery1 = " CREATE OR REPLACE \n " +
                                     " FUNCTION FunctionElham (NAMES IN VARCHAR2) RETURN VARCHAR2 \n " +
                                     " is \n " +
                                     " STR VARCHAR2(7000); \n " +
                                     " begin \n " +
                                     " STR := TRIM(NAMES); \n " +
                                     " STR := NVL(STR,NAMES); \n " +
                                     " STR := REPLACE(STR, chr(55690) , chr(56204)); \n " +
                                     " STR := REPLACE(STR, chr(55977) , chr(55683)); \n " +
                                     " RETURN STR; \n " +
                                     " END FunctionElham;";
                ExecuteQuery(cmdQuery1);
                string cmdQuery2 = "CREATE OR REPLACE Procedure ProcedureElham \n " +
                                    "IS \n" +
                                    "column_name varchar2(7000); \n " +
                                    "table_name varchar2(7000); \n" +
                                    "cursor CursorELham is \n " +
                                    "select c.column_name,c.table_name \n" +
                                    "from user_tab_columns c inner join user_tables t on c.table_name = t.table_name \n" +
                                    "where data_type='NVARCHAR2' or data_type='VARCHAR2'; \n" +
                                    "BEGIN \n " +
                                    "open CursorELham; \n " +
                                    "loop \n " +
                                    "fetch CursorELham into column_name,table_name; \n " +
                                    "exit when CursorELham%NOTFOUND; \n " +
                                    "execute immediate 'UPDATE '||table_name||' SET '||column_name||' = FunctionElham('||column_name||')'; \n " +
                                    "end loop; \n " +
                                    "close CursorELham; \n " +
                                    "END;";
                ExecuteQuery(cmdQuery2);
                string cmdQuery3 = "begin \n " +
                                    "ProcedureElham(); \n " +
                                    "end;";
                ExecuteQuery(cmdQuery3);

                string cmdQuery4 = "drop procedure ProcedureElham";
                ExecuteQuery(cmdQuery4);

                string cmdQuery5 = "drop function FunctionElham";
                ExecuteQuery(cmdQuery5);

                tbString.Clear();
                MessageBox.Show("best Job elham");
            }
            void ExecuteQuery(string cmdQuery)
            {
                OracleConnection con = new OracleConnection();
                con.ConnectionString = "Data Source=CRMTEST;Persist Security Info=True;User ID=sugarcrm1;Password=sugarcrm1;Unicode=True;";
                try
                {
                    con.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                OracleCommand cmd = new OracleCommand(cmdQuery);
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;

                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("This query was ok!");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                    con.Dispose();
                }
            }
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi, the error may be in your C++ code. But still you have not shown us what error you are getting?
     
  5. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    when the procedure is executing , after 5 min I get those errors.
    I think the c# code is correct , because it works on my test data !!!


    *************************************************
    ORA-00971 : missing SET keyword
    ORA-06512 : at "SUGARCRM1.PROCEDUREELHAM" , line 14
    ORA-06512 : at line 2
    *************************************************
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Debug and see where exactly the error comes ..

    Test the following code in DB and find it out.


    Code (SQL):
    CREATE OR REPLACE
    PROCEDURE ProcedureElham
     IS
    column_name varchar2(4000);
     TABLE_NAME varchar2(4000);
    sql_v varchar2(4000);
    cursor CursorELham IS
     SELECT c.column_name,c.TABLE_NAME
    FROM user_tab_columns c INNER JOIN user_tables t ON c.TABLE_NAME = t.TABLE_NAME
    WHERE data_type='NVARCHAR2' OR data_type='VARCHAR2';
    BEGIN
     OPEN CursorELham;
     loop
     fetch CursorELham INTO column_name,TABLE_NAME;
     exit WHEN CursorELham%NOTFOUND;
      sql_v := 'update ' || TABLE_NAME || ' set ' || column_name || ' = FunctionElham(' || column_name || ')';
      DBMS_OUTPUT.PUL_LINE(sql_v ) ;
    EXECUTE immediate  sql_v ;
     END loop;
     close CursorELham;
     END;
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You may want to change your procedure to this:

    Code (SQL):
    CREATE OR REPLACE
    PROCEDURE ProcedureElham
     IS
    column_name varchar2(40);
     TABLE_NAME varchar2(40);
     sql_txt  varchar2(2000);
    cursor CursorELham IS
     SELECT c.column_name,c.TABLE_NAME
    FROM user_tab_columns c INNER JOIN user_tables t ON c.TABLE_NAME = t.TABLE_NAME
    WHERE data_type='NVARCHAR2' OR data_type='VARCHAR2';
    BEGIN
     OPEN CursorELham;
     loop
     fetch CursorELham INTO column_name,TABLE_NAME;
     exit WHEN CursorELham%NOTFOUND;
     sql_text:= 'update ' || TABLE_NAME || ' set ' || column_name || ' = FunctionElham(' || column_name || ')';
     EXECUTE immediate sql_txt;
     END loop;
     close CursorELham;
     END
    Making that change allows you to display the errant statement so you can fix the code or the cursor.
     
  8. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi , I have checked the procedure in DB and with one table and get this new error :

    ORA-06502: PL/SQL: numeric or value error
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Do update with that table manually and see what happens..
     
  10. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have tested it , by creating a table like this :

    create table t1 (id varchar2(1000))
    insert into t1 values('5c0cc24b-b222-04ee-b354-46f6846a1a65')

    and create a table to keep t1 information and use in procedure :

    create table testtotal (column_name varchar2(1000) ,table_name varchar2(1000) )
    insert into testtotal values('id','t1')

    and so i test the procedure with " select * from testtotal "
    but i get this error again :

    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "SUGARCRM1.PROCEDUREELHAM", line 15
    ORA-06512: at line 2
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your dynamic statement is at fault. Rewriite your procedure as I indicated in a prior response so you can see what statement is being generated.
     
  12. charlichaplin

    charlichaplin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I have found it ........
    in may Real DB , there was a table with "product-assign" name.
    so that was my problem , because in Update syntax in oracle you should not have "," and "-" in your table`s name.
    Thanks alot.
     
  13. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Just wondering How come "-" and "," in table names in "REAL DB" ?