Hello every ODC experts ,
My oracle db server is 11.2.0.4.0 on Linux, then how to trouble-shooting the following 4 number of compilation errors of my procedure being written on schema SZD_BBS_V2,
43,9 "PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: := . ( @ % ; ERROR
47,7 "PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: ; ERROR
74,9 "PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: := . ( @ % ; ERROR
78,7 "PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: ; ERROR
Firstly I need to configure some stuff on schema SYS before running my user-defined procedure brgs_role_syn_tab on schema SZD_BBS_V2,
PROMPT =========================
PROMPT Executing on "SYS" schema
PROMPT =========================
DROP USER qwz;
CREATE USER qwz IDENTIFIED BY qwz;
GRANT connect, resource TO qwz;
GRANT create public synonym TO szd_bbs_v2;
GRANT drop public synonym TO szd_bbs_v2;
GRANT select ON dba_synonyms TO szd_bbs_v2;
GRANT create view TO szd_bbs_v2;
GRANT drop any view TO szd_bbs_v2;
GRANT create job TO szd_bbs_v2;
CREATE ROLE bbs;
GRANT bbs TO qwz;
Secondly this is all of code section of my define procedure brgs_role_syn_tab,
CREATE OR REPLACE PROCEDURE brgs_role_syn_tab
IS
v_usr_tables VARACHAR2(200);
-- v_usr_tab_privs VARCHAR2(200);
v_utp_number NUMBER; -- utp is the abbreviation of view "user_tab_privs"
v_ds_number NUMBER; -- ds is the abbreviation of view "dba_synonyms"
BEGIN
v_usr_tables := 'CREATE OR REPLACE VIEW usr_tables'
|| ' AS SELECT table_name, num_rows, partitioned FROM all_tables'
|| ' WHERE owner = ''SZD_BBS_V2'''
|| ' ORDER BY table_name'
|| ' WITH READ ONLY';
EXECUTE IMMEDIATE v_usr_tables;
-- v_usr_tab_privs := 'SELECT table_name, grantor, privilege FROM user_tab_privs WHERE grantee = ''BBS''';
SELECT COUNT(*) INTO v_utp_number FROM user_tab_privs WHERE grantee = 'BBS';
SELECT COUNT(*) INTO v_ds_number FROM dba_synonyms WHERE table_owner = 'SZD_BBS_V2';
DBMS_OUTPUT.enable(1000000);
IF v_utp_number = 0 THEN
FOR r IN (
SELECT 'GRANT SELECT ON ' || t.table_name || ' TO bbs' x_sql
FROM user_tables t
ORDER BY t.table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE r.x_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(r.x_sql, 1, 255));
DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
END;
END LOOP;
ELSE
FOR r IN (
SELECT 'REVOKE SELECT ON ' || t.table_name || ' FROM bbs' x_sql
FROM user_tables t
ORDER BY t.table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE r.x_sql;
EXECEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(r.x_sql, 1, 255));
DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
END;
END LOOP;
END IF;
IF v_ds_number = 0 THEN
FOR r IN (
SELECT 'CREATE PUBLIC SYNONYM ' || t.table_name || ' FOR ' || t.table_name y_sql
FROM user_tables t
ORDER BY t.table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE r.y_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(r.y_sql, 1, 255));
DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
END;
END LOOP;
ELSE
FOR r IN (
SELECT 'DROP PUBLIC SYNONYM ' || t.table_name y_sql
FROM user_tables t
ORDER BY t.table_name
)
LOOP
BEGIN
EXECUTE IMMEDIATE r.y_sql;
EXECEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SUBSTR(r.y_sql, 1, 255));
DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
END;
END LOOP;
END IF;
END;
/
Thanks in advance.
Best Regards
Quanwen Zhao