Oracle PL/SQL Problem with Dynamic Bind Variables (Multiple)

[Jigar M Gohil]

CREATE OR REPLACE PROCEDURE sp_proc_report_101 (
   approver IN VARCHAR2,
   po_result        OUT SYS_REFCURSOR)
   
IS
      v_approver VARCHAR(30000):='(''%''||:approver||''%'')';
      query_string  VARCHAR(30000);
  
BEGIN

    query_string := 'SELECT * FROM USERS WHERE USERS.FIRSTNAME LIKE '|| v_approver || ' AND USERS.LASTNAME LIKE '||v_approver;
    
    OPEN po_result FOR query_string
     USING
             approver ;
      
END;  
/

Issue:
When I use only first filter of FIRSTNAME with Bind variable it works fine but when I use more than one bind variable with LIKE operator it fails.

"ORA-06553: PLS-103: Encountered the symbol "=" when expecting one of the following:

   mod <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> continue current sql execute forall merge
   pipe purge
ORA-01008: not all variables bound
ORA-06512: at "XIGNCMN.SP_PROC_REPORT_101", line 13
ORA-06512: at line 11
"

Any Help?

Thanks in Advance!!!
Jigar.

[Jigar M Gohil]

Got the root cause. The problem was with number of Bind variables I was applying with USING.
Instead of just single use of approver with USING I have to use it for each bind (here twice).

query_string := 'SELECT * FROM USERS WHERE USERS.FIRSTNAME LIKE '|| v_approver || ' AND USERS.LASTNAME LIKE '||v_approver;

OPEN po_result FOR query_string   
     USING   
             approver, approver;

This raised another question: is there a way to re-use the repeated bind variables? (similar to what I was trying earlier)

[Martin Vashko]

Congrats for nailing it down!

This is just a proof of concept, right? Otherwise you could use static SQL in this case.

Edit: I've originaly overlooked your question:

Jigar M Gohil wrote:This raised another question: is there a way to re-use the repeated bind variables? (similar to what I was trying earlier)

Not in your case. See the documentation.