Hi All,
We have requirement , below block i try to execute using dynamic sql by binding variable with like operator produces null result.
Query
Ananymous Block;
DECLARE
I_TBL_NAME VARCHAR2(100) := 'T_PBAMLGLOBUSSPRING';
V_QUERY VARCHAR2(4000) := ' SELECT MAX(SQL.SQL_ID)
FROM V$SQL SQL, V$SESSION SES
WHERE SQL.SQL\_ID = SES.SQL\_ID';
V_SQLID VARCHAR2(100);
BEGIN
V_QUERY := V_QUERY || ' AND SQL.PARSING_SCHEMA_NAME = SYS_CONTEXT(''userenv'', ''CURRENT_SCHEMA'')
AND SES.SCHEMANAME = SYS\_CONTEXT(''userenv'', ''CURRENT\_SCHEMA'')
AND SES.STATUS = ''ACTIVE''';
V_QUERY := V_QUERY ||
' AND UPPER(SQL.SQL\_FULLTEXT) LIKE ''%''||:1||''%''';
IF I_TBL_NAME IS NOT NULL THEN
EXECUTE IMMEDIATE V\_QUERY
INTO V\_SQLID
USING I\_TBL\_NAME;
END IF;
DBMS_OUTPUT.PUT_LINE('sql id:' || V_SQLID);
EXCEPTION
WHEN OTHERS THEN
DBMS\_OUTPUT.PUT\_LINE(' ERROR : ' || SQLERRM || 'ERROR CODE :' ||
SQLCODE);
END;
if i try to execute above block , it doesn't product the output , i suspect like operator with single quotes binding cause empty resultset. Kindly advise how can i bind the single quotes value.
