Home » SQL & PL/SQL » SQL & PL/SQL » Number of Bind variables passed AND Ref Cursor problem
Number of Bind variables passed AND Ref Cursor problem [message #226104] Thu, 22 March 2007 09:41 Go to next message
andy_ora123
Messages: 7
Registered: March 2007
Junior Member
hi all

well i have an interesting problem

i need to construct a query for a refcursor besade on conditions....

as:

input params: in_lname and in_fname
______________________________________________________


sql_stmt := 'Select first_name, ' || 'last_name, ' ||
'from table1';

IF in_lname is not null then
-- we are selecting guests by Lastname, Lastname & Firstname,
-- Lastname & Firstname & Zip, or Lastname & Zip

sql_stmt := sql_stmt || 'where gc_last_name = :1 ';

IF in_fname is not null then
sql_stmt := sql_stmt || ' and gc_first_name = :2 ';
END IF;

END IF;

open fm_lom_cv for sql_stmt using in_lname, in_fname;


___________________________________________


Now here the in_lname is required BUT in_fname can be NULL

hence we dont excute or create the sql with sql_stmt || ' and gc_first_name = :2 ';

so :2 will not be used

hence it gives error IF in_fname is null ie ORA-01006: bind variable does not exist

Please suggest how to handle it....

thanks

Re: Number of Bind variables passed AND Ref Cursor problem [message #226108 is a reply to message #226104] Thu, 22 March 2007 09:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The easiest way is to replace your numbered binds by calls to sys_context.
You can always define context-variables without using them.

If you don't know sys_context, search for it.
Re: Number of Bind variables passed AND Ref Cursor problem [message #226133 is a reply to message #226104] Thu, 22 March 2007 11:22 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why don't you use 2 OPEN statements or (another solution)

Declare
t_fname VARCHAR2;

Change the code:
IF in_lname is not null then
   sql_stmt := sql_stmt || 'where gc_last_name = :1 AND gc_first_name LIKE :2 ';
    IF in_fname is not null then
       t_fname := in_fname;
    ELSE
       t_fname := '%';
    END IF;
    
END IF; 
open fm_lom_cv for sql_stmt using in_lname, in_fname;


HTH.
Re: Number of Bind variables passed AND Ref Cursor problem [message #226171 is a reply to message #226133] Thu, 22 March 2007 13:05 Go to previous messageGo to next message
andy_ora123
Messages: 7
Registered: March 2007
Junior Member
hey thanks guys

well i can open to OPEN FOR statements

but in my case i wil have to open many as i have given only an example

so more open statements will only clutter he code

you have any other solution for this

its will be great if a single OPEN FOR statement can handle it

thanks
Re: Number of Bind variables passed AND Ref Cursor problem [message #226196 is a reply to message #226104] Thu, 22 March 2007 16:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You can use following approach ( I assumed 3 parameters are passed to procedure/function;

CREATE OR REPLACE ... ( pLName VARCHAR2, pFName VARCHAR2, pZIPCode NUMBER) IS
stmt VARCHAR2(300) := 'SELECT * FROM EMP WHERE last_name LIKE :p1 AND first_name LIKE :p2 AND ZIPCODE BETWEEN :p3 AND :p4';
  tLName VARCHAR2(30);
  tFName VARCHAR2(30);
  fromZIP NUMBER;
  toZIP NUMBER;
BEGIN
   IF pLName IS NULL THEN
     tLName := '%';
   ELSE
     tLName := pLName;
   END IF;
   IF pFName IS NULL THEN
     tFName := '%';
   ELSE
     tFName := pFName;
   END IF;
   IF pZIPCode IS NULL THEN
     fromZIP := 0;
     toZIP := 99999;
   ELSE
     fromZIP := pZIPCode;
     toZIP := pZIPCode;
   END IF;
  
   exec statement ... USING tLName , tFName, fromZIP , toZIP;


HTH.
Re: Number of Bind variables passed AND Ref Cursor problem [message #226246 is a reply to message #226171] Fri, 23 March 2007 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ankit_vaid@keane.com wrote on Thu, 22 March 2007 19:05
you have any other solution for this

its will be great if a single OPEN FOR statement can handle it

thanks


Did you even READ my suggestion??
Re: Number of Bind variables passed AND Ref Cursor problem [message #226248 is a reply to message #226246] Fri, 23 March 2007 01:33 Go to previous messageGo to next message
andy_ora123
Messages: 7
Registered: March 2007
Junior Member
hey frank

i am already working on that solution but thing is

using Context variable is making my system slow... the query runs quite slow...

any ways if i am successfull will let you know if it worked

or not then what are the probelms with it
Re: Number of Bind variables passed AND Ref Cursor problem [message #226251 is a reply to message #226248] Fri, 23 March 2007 01:39 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I used this construct in production code. Prior to that I used an alternative in which I dynamically built my using-list for execute immediate.
I found no difference in performance whatsoever.
Note: Make sure you convert the types in your cursor. sys_context will always return strings, so you have to use to_date/to_number
Re: Number of Bind variables passed AND Ref Cursor problem [message #226548 is a reply to message #226171] Sat, 24 March 2007 22:08 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Here is an example of another method:

SCOTT@10gXE> CREATE OR REPLACE PROCEDURE your_proc
  2    (p_results  OUT SYS_REFCURSOR,
  3  	p_job	   IN  VARCHAR2,
  4  	p_ename    IN  VARCHAR2 DEFAULT NULL)
  5  AS
  6    v_sql_stmt      VARCHAR2 (32767);
  7  BEGIN
  8    v_sql_stmt := 'SELECT job, ename FROM emp';
  9    v_sql_stmt := v_sql_stmt || ' WHERE job = :b_job';
 10    v_sql_stmt := v_sql_stmt || ' AND ename = NVL (:b_ename, ename)';
 11    OPEN p_results FOR v_sql_stmt USING p_job, p_ename;
 12  END your_proc;
 13  /

Procedure created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> VARIABLE g_results REFCURSOR
SCOTT@10gXE> EXEC your_proc (:g_results, 'CLERK', 'ADAMS')

PL/SQL procedure successfully completed.

SCOTT@10gXE> PRINT g_results

JOB       ENAME
--------- ----------
CLERK     ADAMS

SCOTT@10gXE> EXEC your_proc (:g_results, 'CLERK')

PL/SQL procedure successfully completed.

SCOTT@10gXE> PRINT g_results

JOB       ENAME
--------- ----------
CLERK     SMITH
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER

SCOTT@10gXE> 

Previous Topic: Existing state of packages has been discarded...?
Next Topic: Spool only SQL query result
Goto Forum:
  


Current Time: Sun Dec 04 06:45:22 CST 2016

Total time taken to generate the page: 0.12406 seconds