pl sql error :ORA-00933: SQL command not properly ended
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: pl sql error :ORA-00933: SQL command not properly ended

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184

    pl sql error :ORA-00933: SQL command not properly ended

    CREATE OR REPLACE procedure sp_process_terminated_users(p_user in varchar2,p_pwd varchar2,p_db_name varchar2)
    as
    TYPE RefCurTyp1 IS REF CURSOR;
    cv1 RefCurTyp1;

    TYPE RefCurTyp2 IS REF CURSOR;
    cv2 RefCurTyp2;

    v_database_name db_names.database_name%type;

    v_sql varchar2(1000);
    v_sql1 varchar2(1000);

    v_db_user all_users.username%type;
    v_terminated_user terminated_users.user_name%type;
    v_drop_user terminated_users.user_name%type;

    v_table_name varchar2(30);
    v_table_name_1 varchar2(30);

    v_export_file varchar2(200);
    v_export_user varchar2(100);

    v_cmd_string varchar2(1000);
    v_count_objects number;



    cursor db is
    select database_name from db_names;

    cursor tu is
    select user_name from terminated_users;

    begin

    v_cmd_string:='sqlldr '||p_user||'/'||p_pwd||'@'||p_db_name ||' control=c:\\terminated_users.ctl';
    Execute_cmd(v_cmd_string);

    open tu;
    loop
    fetch tu into v_terminated_user;
    exit when tu%notfound;
    open db;
    loop
    fetch db into v_database_name;
    exit when db%notfound;
    v_table_name:='all_users@'||v_database_name;
    v_sql:= 'select username from '||v_table_name||' where username ='''||v_terminated_user||'''';
    OPEN cv1 FOR
    v_sql;
    fetch cv1 into v_db_user;

    if v_db_user is null then
    INSERT INTO TERMINATED_USERS_STATUS(USER_NAME, STATUS_COMMENT)
    VALUES(v_terminated_user,'Not In '||v_database_name||' Database');
    commit;
    elsif (v_db_user is not null ) then

    -- select count(*) into v_count_objects from all_objects where owner = v_db_user;
    v_table_name_1:='all_objects@'||v_database_name;
    v_sql1:= 'select count(*) from '||v_table_name_1 ||'where owner = '''||v_db_user||'''';
    OPEN cv2 FOR v_sql1;
    loop
    fetch cv2 into v_count_objects;
    exit when cv2%notfound;
    if v_count_objects = 0 then
    insert into TERMINATED_USERS_DETAILS (TERMINATED_USER_NAME,
    DATABASE_NAME,
    OWNED_OBJECTS_COUNT
    )
    values(v_db_user,
    v_database_name,
    v_count_objects);
    commit;
    elsif v_count_objects > 0 then
    v_export_file:=v_db_user||'_'||v_database_name||'_'||to_char(sysdate,'mmddyyyy')||'.dmp';
    v_export_user:= p_user||'/'||p_pwd||'@'||v_database_name;


    if v_database_name='SRINIVAS' then
    v_cmd_string:=('d:\\oracle\\ora81\\bin\\exp '||v_export_user ||' file=c:\\'||v_export_file ||' OWNER='||v_db_user||' log=c:\\'||v_db_user||'_'||v_database_name||'.log');

    elsif v_database_name='KNOW9I' then
    v_cmd_string:='d:\\oracle\\ora91\\bin\\exp '||v_export_user ||' file=c:\\'||v_export_file ||' OWNER='||v_db_user||' log=c:\\'||v_db_user||'_'||v_database_name||'.log';

    end if;

    Execute_cmd(v_cmd_string);
    insert into TERMINATED_USERS_DETAILS (TERMINATED_USER_NAME,
    DATABASE_NAME,
    OWNED_OBJECTS_COUNT,
    EXPORT_DEST_AND_FILE_NAME,
    EXPORT_DATE
    )
    values (v_db_user,
    v_database_name,
    v_count_objects,
    v_export_file,
    sysdate
    );
    commit;

    update TERMINATED_USERS_STATUS
    set STATUS_COMMENT = 'User data Exported.Not Dropped'
    where USER_NAME = v_db_user;
    commit;
    end if;
    end loop;
    close cv2;
    end if;
    end loop;
    close db;
    end loop;
    close tu;

    exception
    when no_data_found then
    dbms_output.put_line('No users in the database to drop');
    end;
    /

    ==============
    scott@SRINIVAS >exec SP_PROCESS_TERMINATED_USERS('scott','tiger','srinivas');
    I am done
    BEGIN SP_PROCESS_TERMINATED_USERS('scott','tiger','srinivas'); END;

    *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    ORA-06512: at "SCOTT.SP_PROCESS_TERMINATED_USERS", line 63
    ORA-06512: at line 1
    ========================
    sonofsita
    http://www.ordba.net

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And your question is?

    Oh, btw, your procedure has an error - when it tries to execute line 63 it can't because your SQL command constructed in line 62 makes has semantic error.
    v_sql1:= 'select count(*) from '||v_table_name_1 ||'where owner = '''||v_db_user||'''';
    You need a space between the quotation mark and "where".....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    Fort Smith
    Posts
    184
    thanks a lot...I was missing that and there you have helped me.. Thanks again
    sonofsita
    http://www.ordba.net

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