I have 2 procedures procA & procB defined inside a package. I am trying to call procB from ProcA. procB is using DBMS_SQL.PARSE & EXECUTE etc. to execute SQL statements. procB works as expected when run individually procB fails with
ORA-29471: DBMS_SQL access denied when called from procA
CREATE OR REPLACE PACKAGE BODY PKG_R3_ADV_FILTER
as
PROCEDURE P_GET_FILE(
PO_R3FILTERRECORDS OUT SYS_REFCURSOR,
PO_MEDDRA_CONFIG OUT SYS_REFCURSOR)
AS
V_SEL VARCHAR2 (20000);
V_FRM VARCHAR2 (20000);
V_WRY VARCHAR2 (20000);
V_OTHER VARCHAR2 (20000);
V_MAIN CLOB;
V_FILTER CLOB;
V_FILTER_PARAM CLOB;
GV_FROM_DATE DATE := TO_DATE (sysdate, 'DD-MON-YYYY');
GV_TO_DATE DATE := TO_DATE (sysdate, 'DD-MON-YYYY') + 1;
--V_MEDDRA_CONFIG CHAR(1):='N';
V_FN_CNT NUMBER;
EXP_SQL_INJECTION EXCEPTION;
RETURN_NO VARCHAR2(4000);
BEGIN
V_FILTER := 'lower(event_country) = :event_country_1';
V_FILTER_PARAM := '{"event_country_1":"de"}';
GV_MEDDRA_CONFIG := 'N';
V_SEL:= 'WITH PARAM AS (SELECT :P1 V_FROM_DATE,:P2 V_TO_DATE, :P3 V_MEDDRA_CONFIG FROM DUAL),
CFG_FILES
AS
( SELECT FILES.USER_ID USER_ID_USR, USR.USER_FULLNAME USER_NAME, FILES.*,A.V_FROM_DATE,A.V_TO_DATE
FROM E2B_R3_CFG_FILES FILES, E2B_CFG_USERS USR, PARAM A
WHERE FILES.USER_ID = USR.USER_ID(+) AND TRUNC(FILES.PROCESSED_DATE_GMT) >= A.V_FROM_DATE AND TRUNC(FILES.PROCESSED_DATE_GMT) < A.V_TO_DATE
)
SELECT FILES.FILE_ID,
FILES.FILE_NAME,
FILES.SAFETY_REPORT_ID,
FILES.PRIM_RPT_COUNTRY,
FILES.PRODUCT_NAME AS PROD_NAME,
FILES.FORMULATION,
FILES.ACTIVE_SUBSTANCE as ACTIVE_SUB,
FILES.PRODUCT_NAME_NULL,
FILES.FORMULATION_NULL,
FILES.ACTIVE_SUBSTANCE_NULL,
FILES.DATE_REPORTED,
FILES.DATE_MOST_RECENT,
FILES.PAT_ADMIN_ROUTE,
FILES.EVENT_COUNTRY,
FILES.E2B_COMP_NUM,
FILES.E2B_DUP_NUM,
FILES.PROCESSED_DATE,
DECODE (FILES.IS_RULE_PASSED, 0, ''No'', 1, ''Yes'', 2, ''NA'')
IS_RULE_PASSED,
FILES.JUST_COMMENTS,
/*DECODE (ACTION_TAKEN, 0, ''NONE'', 1, ''ACCEPT'', 2, ''REJECT'')*/
(SELECT FLAGVALUE12 FROM E2B_CMN_FLAG_LOOKUP WHERE ID = FILES.ACTION_TAKEN) ACTION_TAKEN,
FILES.USER_ID_USR USER_ID,
FILES.IS_MOVED,
FILES.MODIFIED_DATE,
DECODE (FILES.PREVIOUS_ACTION, 0, ''None'', 1, ''Accept'', 2, ''Reject'')
PREVIOUS_ACTION,
/*DECODE (IS_CASE_SERIOUS, 0, ''NO'', 1, ''YES'', 2, ''FATAL'')*/
(SELECT FLAGVALUE7 FROM E2B_CMN_FLAG_LOOKUP WHERE ID = to_char(FILES.IS_CASE_SERIOUS))
IS_CASE_SERIOUS,
FILES.USER_NAME,
(CASE WHEN DBMS_LOB.GETLENGTH(FILES.LITERATURE_REFERENCE)>2000
THEN CAST (DBMS_LOB.SUBSTR (FILES.LITERATURE_REFERENCE, 2000, 1) AS VARCHAR2 (4000))||''[...Data Truncated]''
ELSE CAST (DBMS_LOB.SUBSTR (FILES.LITERATURE_REFERENCE, 2000, 1) AS VARCHAR2 (4000))
END)
LITERATURE_REFERENCE,
FILES.HTML_VW_GENERATED,
(CASE A.V_MEDDRA_CONFIG WHEN ''N'' THEN ''NA'' ELSE FILES.MEDDRA_CRITICAL_TERM END) MEDDRA_CRITICAL_TERM';
V_FRM:=' FROM CFG_FILES FILES,
PARAM A';
/*V_WRY:=' WHERE FILES.FILE_ID = PROD.FILE_ID(+)
AND FILES.FILE_ID = FRM.FILE_ID(+)
AND FILES.FILE_ID = FRM_N.FILE_ID(+)
AND FILES.FILE_ID = ACT.FILE_ID(+)
AND FILES.FILE_ID = ACT_N.FILE_ID(+)';*/
V_MAIN:='SELECT * FROM ('||V_SEL||V_FRM||V_WRY||') WHERE '||V_FILTER;
V_FN_CNT:=PKG_COMMON_UTILITIES.FN_SEARCH_KEYWORD(V_FILTER);
IF V_FN_CNT = 1 THEN
RAISE EXP_SQL_INJECTION;
END IF;
IF V_FILTER_PARAM IS NOT NULL THEN
PKG_R3_ADV_FILTER.P_FILTER_SQL_INJ(V_MAIN,V_FILTER_PARAM);
END IF;
BEGIN
IF V_FILTER_PARAM IS NULL THEN
OPEN PO_R3FILTERRECORDS FOR V_MAIN USING GV_FROM_DATE, GV_TO_DATE, GV_MEDDRA_CONFIG;
ELSE
OPEN PO_R3FILTERRECORDS FOR SELECT * FROM GT_R3_ADV_FILTER;
END IF;
OPEN PO_MEDDRA_CONFIG FOR SELECT GV_MEDDRA_CONFIG MEDDRA_CONFIG FROM DUAL;
PO_ERROR_CODE := 0;
PO_ERROR_MSG := 'Success';
EXCEPTION
WHEN EXP_SQL_INJECTION THEN
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
PROCEDURE P_FILTER_SQL_INJ
(
PI_TEMPFILTER IN CLOB,
PI_TEMPFILTERPARAMS IN CLOB
)
IS
TYPE TYP_FILE_ID IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_FILE_ID TYP_FILE_ID;
TYPE TYP_FILE_NAME IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_FILE_NAME TYP_FILE_NAME;
TYPE TYP_SAFETY_REPORT_ID IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_SAFETY_REPORT_ID TYP_SAFETY_REPORT_ID;
TYPE TYP_PRIM_RPT_COUNTRY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PRIM_RPT_COUNTRY TYP_PRIM_RPT_COUNTRY;
TYPE TYP_PROD_NAME IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PROD_NAME TYP_PROD_NAME;
TYPE TYP_FORMULATION IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_FORMULATION TYP_FORMULATION;
TYPE TYP_ACTIVE_SUB IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_ACTIVE_SUB TYP_ACTIVE_SUB;
TYPE TYP_PROD_NAME_NULL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PROD_NAME_NULL TYP_PROD_NAME_NULL;
TYPE TYP_FORMULATION_NULL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_FORMULATION_NULL TYP_FORMULATION_NULL;
TYPE TYP_ACTIVE_SUB_NULL IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_ACTIVE_SUB_NULL TYP_ACTIVE_SUB_NULL;
TYPE TYP_DATE_REPORTED IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_DATE_REPORTED TYP_DATE_REPORTED;
TYPE TYP_DATE_MOST_RECENT IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_DATE_MOST_RECENT TYP_DATE_MOST_RECENT;
TYPE TYP_PAT_ADMIN_ROUTE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PAT_ADMIN_ROUTE TYP_PAT_ADMIN_ROUTE;
TYPE TYP_EVENT_COUNTRY IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_EVENT_COUNTRY TYP_EVENT_COUNTRY;
TYPE TYP_E2B_COMP_NUM IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_E2B_COMP_NUM TYP_E2B_COMP_NUM;
TYPE TYP_E2B_DUP_NUM IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_E2B_DUP_NUM TYP_E2B_DUP_NUM;
TYPE TYP_PROCESSED_DATE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PROCESSED_DATE TYP_PROCESSED_DATE;
TYPE TYP_IS_RULE_PASSED IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_IS_RULE_PASSED TYP_IS_RULE_PASSED;
TYPE TYP_JUST_COMMENTS IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_JUST_COMMENTS TYP_JUST_COMMENTS;
TYPE TYP_ACTION_TAKEN IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_ACTION_TAKEN TYP_ACTION_TAKEN;
TYPE TYP_USER_ID IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_USER_ID TYP_USER_ID;
TYPE TYP_IS_MOVED IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_IS_MOVED TYP_IS_MOVED;
TYPE TYP_MODIFIED_DATE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_MODIFIED_DATE TYP_MODIFIED_DATE;
TYPE TYP_PREVIOUS_ACTION IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_PREVIOUS_ACTION TYP_PREVIOUS_ACTION;
TYPE TYP_IS_CASE_SERIOUS IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_IS_CASE_SERIOUS TYP_IS_CASE_SERIOUS;
TYPE TYP_USER_NAME IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_USER_NAME TYP_USER_NAME;
TYPE TYP_LITERATURE_REFERENCE IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_LITERATURE_REFERENCE TYP_LITERATURE_REFERENCE;
TYPE TYP_HTML_VW_GENERATED IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_HTML_VW_GENERATED TYP_HTML_VW_GENERATED;
TYPE TYP_MEDDRA_CRITICAL_TERM IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
TAB_MEDDRA_CRITICAL_TERM TYP_MEDDRA_CRITICAL_TERM;
TYPE CURTYPE IS REF CURSOR;
SRC_CUR SYS_REFCURSOR;
KEYS_STRING CLOB;
KEYS_VALUE CLOB;
V_KEYS_STRING VARCHAR2(32000);
V_KEYS_VALUE VARCHAR2(32000);
V_CURSOR PLS_INTEGER;
V_RESULT PLS_INTEGER;
JSNOBJ JSON_OBJECT_T;
V_KEYS JSON_KEY_LIST;
PO_ERROR_CODE VARCHAR2(4000);
PO_ERROR_MSG VARCHAR2(4000);
--MY_QUERY_STR RAHUL_TEST.FILTER_JSON%TYPE;
--JSONARR JSON_ARRAY_T;
--JV JSON_ARRAY_T;
BEGIN
--JA := NEW JSON_ARRAY_T;
--JV := NEW JSON_ARRAY_T;
JSNOBJ := JSON_OBJECT_T.PARSE(PI_TEMPFILTERPARAMS);
V_KEYS := JSNOBJ.GET_KEYS;
**/*getting the error in the below line*/**
DBMS_SQL.PARSE(V_CURSOR, PI_TEMPFILTER, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'P1', GV_FROM_DATE);
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'P2', GV_TO_DATE);
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'P3', GV_MEDDRA_CONFIG);
FOR I IN 1..V_KEYS.COUNT LOOP
V_KEYS_STRING:=V_KEYS(I);
IF JSNOBJ.GET_STRING (V_KEYS(I)) IS NOT NULL
THEN
V_KEYS_VALUE:= JSNOBJ.GET_STRING (V_KEYS(I));
ELSIF JSNOBJ.GET_NUMBER (V_KEYS(I)) IS NOT NULL
THEN
V_KEYS_VALUE:= JSNOBJ.GET_NUMBER (V_KEYS(I));
END IF;
DBMS_SQL.BIND_VARIABLE(V_CURSOR, V_KEYS_STRING, V_KEYS_VALUE);
END LOOP;
-- DELETE FROM GT_INPUT_PARAMETERS;
-- INSERT INTO GT_INPUT_PARAMETERS(PARACLOB1,PARACLOB2) VALUES(V_KEYS_STRING,V_KEYS_VALUE);
--
-- FOR J IN(SELECT PARACLOB1,PARACLOB2 FROM GT_INPUT_PARAMETERS)LOOP
-- DBMS_SQL.BIND_VARIABLE(MY_CURSOR, J.PARACLOB1, J.PARACLOB2);
-- END LOOP;
--KEYS_STRING := JA.TO_STRING;
--KEYS_VALUE := JV.TO_STRING;
V_RESULT := DBMS_SQL.EXECUTE(V_CURSOR);
SRC_CUR := DBMS_SQL.TO_REFCURSOR(V_CURSOR);
FETCH SRC_CUR BULK COLLECT INTO
TAB_FILE_ID, TAB_FILE_NAME, TAB_SAFETY_REPORT_ID, TAB_PRIM_RPT_COUNTRY,
TAB_PROD_NAME, TAB_FORMULATION, TAB_ACTIVE_SUB, TAB_PROD_NAME_NULL,
TAB_FORMULATION_NULL, TAB_ACTIVE_SUB_NULL, TAB_DATE_REPORTED, TAB_DATE_MOST_RECENT,
TAB_PAT_ADMIN_ROUTE, TAB_EVENT_COUNTRY, TAB_E2B_COMP_NUM, TAB_E2B_DUP_NUM,
TAB_PROCESSED_DATE, TAB_IS_RULE_PASSED, TAB_JUST_COMMENTS, TAB_ACTION_TAKEN,
TAB_USER_ID, TAB_IS_MOVED, TAB_MODIFIED_DATE, TAB_PREVIOUS_ACTION,
TAB_IS_CASE_SERIOUS, TAB_USER_NAME, TAB_LITERATURE_REFERENCE, TAB_HTML_VW_GENERATED,
TAB_MEDDRA_CRITICAL_TERM;
FOR H IN 1..TAB_FILE_ID.COUNT
LOOP
INSERT INTO GT_R3_ADV_FILTER
(
FILE_ID, FILE_NAME, SAFETY_REPORT_ID, PRIM_RPT_COUNTRY,
PRODUCT_NAME, FORMULATION, ACTIVE_SUBSTANCE, PRODUCT_NAME_NULL,
FORMULATION_NULL, ACTIVE_SUBSTANCE_NULL, DATE_REPORTED, DATE_MOST_RECENT,
PAT_ADMIN_ROUTE, EVENT_COUNTRY, E2B_COMP_NUM, E2B_DUP_NUM,
PROCESSED_DATE, IS_RULE_PASSED, JUST_COMMENTS, ACTION_TAKEN,
USER_ID, IS_MOVED, MODIFIED_DATE, PREVIOUS_ACTION,
IS_CASE_SERIOUS, USER_NAME, LITERATURE_REFERENCE, HTML_VW_GENERATED,
MEDDRA_CRITICAL_TERM
)
VALUES
(
TAB_FILE_ID(H), TAB_FILE_NAME(H), TAB_SAFETY_REPORT_ID(H), TAB_PRIM_RPT_COUNTRY(H),
TAB_PROD_NAME(H), TAB_FORMULATION(H), TAB_ACTIVE_SUB(H), TAB_PROD_NAME_NULL(H),
TAB_FORMULATION_NULL(H), TAB_ACTIVE_SUB_NULL(H), TAB_DATE_REPORTED(H), TAB_DATE_MOST_RECENT(H),
TAB_PAT_ADMIN_ROUTE(H), TAB_EVENT_COUNTRY(H), TAB_E2B_COMP_NUM(H), TAB_E2B_DUP_NUM(H),
TAB_PROCESSED_DATE(H), TAB_IS_RULE_PASSED(H), TAB_JUST_COMMENTS(H), TAB_ACTION_TAKEN(H),
TAB_USER_ID(H), TAB_IS_MOVED(H), TAB_MODIFIED_DATE(H), TAB_PREVIOUS_ACTION(H),
TAB_IS_CASE_SERIOUS(H), TAB_USER_NAME(H), TAB_LITERATURE_REFERENCE(H), TAB_HTML_VW_GENERATED(H),
TAB_MEDDRA_CRITICAL_TERM(H)
);
END LOOP;
--CLOSE SRC_CUR;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
end;
/
There is way too much code to analyze in your question. Maybe this basic principles of using DBMS_SQL package could help you find your way through the code provided.
Here is the procedure creating SYS_REFCURSOR for outside use...
CREATE OR REPLACE PROCEDURE TESTPROC
AS
V_CURSOR SYS_REFCURSOR;
BEGIN
OPEN V_CURSOR FOR
SELECT 1 "A_NUMBER", 'A' "A_LETTER" FROM Dual UNION ALL
SELECT 2 "A_NUMBER", 'B' "A_LETTER" FROM Dual;
DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;
... and another Procedure using the resultset from the firs - using DBMS_SQL Package too ... (comments in code)
SET SERVEROUTPUT ON
DECLARE
p_sql_cursor PLS_INTEGER;
p_ref_cursor SYS_REFCURSOR;
p_return PLS_INTEGER;
p_number NUMBER(6);
p_letter VARCHAR2(12);
BEGIN
-- 1. Initialize cursor with DBMS_SQL.open_cursor()
p_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE);
-- 2. Parse TESTPROC Using DBMS_SQL.parse()
DBMS_SQL.parse(c => p_sql_cursor, -- <--- parse to cursor initialized in step 1.
statement => 'BEGIN TESTPROC; END;', -- <--- running first procedure as PL/SQL Block to get cursor
language_flag => DBMS_SQL.native);
-- 3. Execute cursor initialized in step 1. (with parsed cursor from step 2.)
p_return := DBMS_SQL.execute(p_sql_cursor);
-- 4. Get the resultset and process it ...
BEGIN
DBMS_SQL.get_next_result(p_sql_cursor, p_ref_cursor);
EXCEPTION
WHEN NO_DATA_FOUND THEN
Null;
END;
-- Processing it
LOOP
FETCH p_ref_cursor
INTO p_number, p_letter;
EXIT WHEN p_ref_cursor%NOTFOUND;
DBMS_OUTPUT.put_line('p_number=' || p_number || ' ' ||
'p_letter=' || p_letter);
END LOOP;
CLOSE p_ref_cursor;
END;
/
/* R e s u l t :
p_number=1 p_letter=A
p_number=2 p_letter=B
PL/SQL procedure successfully completed. */
As an alternative you can just EXEC the procedure (SQLDeveloper) to get the resultset:
EXEC TESTPROC;
/* R e s u l t :
A_NUMBER A_LETTER
---------- --------
1 A
2 B */