How to build dynamic SQL query inside stored procedure using input parameters?

Question

We have a requirement where we need to build SQL query depending upon the inputs received to stored procedure. For the sake of simplicity I have reduced number of fields in input and select. Facing error mentioned below.

Can you please help me resolve this error and let me know how can I execute this query.

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS
AS
BEGIN
    p_requestnumber    VARCHAR2(256);
    p_username         VARCHAR2(256);
    sql_query          VARCHAR2(1000);
BEGIN
    sql_query := 'select message_type, timestamp, request_number, username, useraction, component_name, module_name, process_name, task, version, response_code, response_message, audit_message from myapp_audit where ';

    if p_username is not null and p_requestnumber is not null then
        sql_query := sql_query || 'username = ' :p_username ' and request_number = ' :p_requestnumber;
    elsif p_username is null and p_requestnumber is not null then
        sql_query := sql_query || 'request_number =' :p_requestnumber;
    else
        sql_query := sql_query || 'username = ' :username;
    end if;

    DBMS_OUTPUT.PUT_LINE(sql_query);
END MYAPP_AUDIT_GET_RECORDS;

Error:

PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the following: := . ( @ % ; The symbol ":=" was substituted for "VARCHAR2" to continue.
PLS-00103: Encountered the symbol "," when expecting one of the following: . ( * % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

Answer 1

Don't use dynamic SQL when you do not need to:

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS(
  o_cur OUT SYS_REFCURSOR
)
AS
  p_requestnumber    VARCHAR2(256);
  p_username         VARCHAR2(256);
BEGIN
  OPEN o_cur FOR
    select message_type,
           timestamp,
           request_number,
           username,
           useraction,
           component_name,
           module_name,
           process_name,
           task,
           version,
           response_code,
           response_message,
           audit_message
    FROM   myapp_audit
    WHERE  ( p_username IS NULL OR username = p_username )
    AND    ( p_requestnumber IS NULL OR request_number = p_requestnumber )
    AND    ( p_username IS NOT NULL OR p_requestnumber IS NOT NULL);
END MYAPP_AUDIT_GET_RECORDS;

If you do need to use dynamic SQL (you do not in your example) then use bind parameters and NEVER use (unsanitised) string concatenation for variables (as that is how you introduce SQL injection vulnerabilities):

CREATE OR REPLACE PROCEDURE MYAPP_AUDIT_GET_RECORDS
AS
  p_requestnumber    VARCHAR2(256);
  p_username         VARCHAR2(256);
  sql_query          VARCHAR2(1000);
  somevariable       AN_APPROPRIATE_DATATYPE;
BEGIN
  sql_query := 'select message_type, timestamp, request_number, username, useraction, component_name, module_name, process_name, task, version, response_code, response_message, audit_message from myapp_audit where ';

  if p_username is not null and p_requestnumber is not null then
    sql_query := sql_query || 'username = :username and request_number = :requestnumber';
  elsif p_username is null and p_requestnumber is not null then
    sql_query := sql_query || ':username IS NULL AND request_number = :requestnumber';
  else
    sql_query := sql_query || 'username = :username AND :requestnumber IS NULL';
  end if;

  DBMS_OUTPUT.PUT_LINE(sql_query);
  EXECUTE IMMEDIATE sql_query
    BULK COLLECT INTO somevariable
    USING p_username, p_requestnumber;
END MYAPP_AUDIT_GET_RECORDS;