Skip to Main Content

Application Development Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Bind variable

Rajesh LApr 15 2024

I got the errors message when i appling bind varibales

Error(64,9): PL/SQL: Statement ignored
Error(64,98): PLS-00201: identifier 'D.FSTNM' must be declared
Error(68,9): PL/SQL: Statement ignored
Error(68,97): PLS-00201: identifier 'D.LSTNME' must be declared

create or replace PROCEDURE Proc_Account_Logs (
P_Accid IN VARCHAR2,
p_acc_id IN VARCHAR2,
P_Prncp IN VARCHAR2,
from_log IN DATE,
to_Log IN DATE,
P_Sub IN VARCHAR2,
P_fstnm IN VARCHAR2,
P_Lstnme IN VARCHAR2,
P_Result OUT SYS_REFCURSOR,
P_Success_Msg OUT VARCHAR2,
P_Error_Msg OUT VARCHAR2
) IS
V_Account_Query VARCHAR2(4000);
v_Sub VARCHAR2(4000);
BEGIN
-- Construct the base query
V_Account_Query := '

WITH driver AS (SELECT
:P_Accid AS P_Accid,
:p_acc_id AS p_acc_id,
:P_Prncp AS P_Prncp,
:from_log AS from_log,
:to_Log AS to_Log,
:P_Sub AS v_Sub,
:P_fstnm AS P_fstnm,
:P_Lstnme AS P_Lstnme
FROM dual)
SELECT * FROM Account_Log
JOIN driver d ON 1 = 1
WHERE 1=1';

-- Add conditions based on input parameters
IF P_Accid IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Account_Log_Id = d.Accid';
END IF;

IF p_acc_id IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Account_Id = d.acc_id';
END IF;

IF P_Prncp IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Upper(Principal) = Upper(d.Prncp)';
END IF;

-- Add condition to filter for the date range specified by from_log and to_Log
IF from_log IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Log_Date >= d.from_log';
END IF;

IF to_Log IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Log_Date <= d.to_Log';
END IF;

-- Add additional conditions based on other input parameters
v_Sub := REPLACE(P_Sub, '*', '%'); -- Replace asterisk with SQL wildcard (%)

IF v_Sub IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Upper(Subject) LIKE UPPER(d.Sub)';
END IF;

IF P_fstnm IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Upper(first_name) LIKE UPPER(''%' || REPLACE(d.fstnm, '*', '%') || '%'')';
END IF;

IF P_Lstnme IS NOT NULL THEN
V_Account_Query := V_Account_Query || ' AND Upper(Last_Name) LIKE UPPER(''%' || REPLACE(d.Lstnme, '*', '%') || '%'')';
END IF;

V_Account_Query := V_Account_Query || ' ORDER BY Account_Log_Id';

DBMS_OUTPUT.PUT_LINE('Generated Query: ' || V_Account_Query);

-- Open cursor for the constructed query
OPEN P_Result FOR V_Account_Query USING
P_Accid,
p_acc_id,
P_Prncp,
from_log,
to_Log,
v_Sub,
P_fstnm,
P_Lstnme;

-- Set success message
P_Success_Msg := 'Query Executed Successfully';

EXCEPTION
WHEN OTHERS THEN
-- Set error message in case of exception
P_Error_Msg := 'Error Executing The Query: ' || SQLCODE || ' ' || SQLERRM;
END Proc_Account_Logs;

This post has been answered by Solomon Yakobson on Apr 18 2024
Jump to Answer
Comments
Post Details
Added on Apr 15 2024
15 comments
145 views