Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL + Cursor + Bind variable isn't working (PL/SQL Release 10.2.0.3.0 - Production / Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
Dynamic SQL + Cursor + Bind variable isn't working [message #436266] Tue, 22 December 2009 09:44 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi,

I was trying to test the usage of bind variables in the FOR-OPEN cursor using the dynamic SQLs. I wrote below piece of code, but it doesn't seem to be working.

DECLARE

TYPE cursor_type IS REF CURSOR;
l_empid                 varchar2(25);
l_startdate             integer(8);
l_sql                   varchar2(1000);
dynamiccursor           cursor_type;
l_empname               varchar2(25);

BEGIN

l_empid := 'vd12345';
select to_char(sysdate, 'YYYYMMDD') into l_startdate from dual;

l_sql := 'select empname from employee where empid = :xid ';
            
    OPEN dynamiccursor FOR l_sql using l_empid;

    dbms_output.put_line (l_sql);
    dbms_output.put_line (l_empid);
    
    LOOP
     FETCH dynamiccursor
      INTO l_empname ;

     EXIT WHEN dynamiccursor%NOTFOUND;
    END LOOP;

    CLOSE dynamiccursor;

dbms_output.put_line (l_empname);

END;


When I run this code, it actually jumps out on "EXIT WHEN dynamiccursor%NOTFOUND" condition.

But when I'm running the below statement separately I'm getting resultset as 'vikasduhan'.

select empname from employee where empid = 'vd12345';


Please suggest what exactly I'm missing out here ...

Thanks!
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436267 is a reply to message #436266] Tue, 22 December 2009 09:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Please suggest what exactly I'm missing out here .

"l_empid" is not the same as "xid"
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436268 is a reply to message #436267] Tue, 22 December 2009 09:57 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Quote:
"l_empid" is not the same as "xid"


I'm sorry I didn't get it ... xid is a bind variable and l_empid is the value that replaces that variable.
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436269 is a reply to message #436266] Tue, 22 December 2009 10:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you cut and paste what happens when you run the code in SQL*Plus with ServerOutput on - I'm interested to see what the contents of l_sql and l_empid are.

Please don't just type in what you see - cut and paste the screen output.
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436270 is a reply to message #436269] Tue, 22 December 2009 10:07 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Sure.. Here it is .. I used Quest Toad to get the output.

select empname from employee where empid = :xoper 
vd12345
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436271 is a reply to message #436270] Tue, 22 December 2009 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Output:
select empname from employee where empid = :xoper 

Code:
l_sql := 'select empname from employee where empid = :xid ';

Are you sure what you get is from what you posted?

Regards
Michel
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436272 is a reply to message #436270] Tue, 22 December 2009 10:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL*Plus would have been better - I'd have got to see everything that you were doing.

I didn't specify a client by accident.

There's at least one oddity - the bind variable you've used in the original post isn't the same as the bind variable you've used in the code you ran - are there any other changes?

I've built a test case based on your posted code, and it worke.

Run this, and tell us what you get:
create table test_120 (empid    varchar2(10), empname  varchar2(30));

insert into test_120 values ('vd12345','Test test_120');

DECLARE

TYPE cursor_type IS REF CURSOR;
l_empid                 varchar2(25);
l_startdate             integer(8);
l_sql                   varchar2(1000);
dynamiccursor           cursor_type;
l_empname               varchar2(25);

BEGIN

l_empid := 'vd12345';
select to_char(sysdate, 'YYYYMMDD') into l_startdate from dual;

l_sql := 'select empname from test_120 where empid = :xid ';
            
    OPEN dynamiccursor FOR l_sql using l_empid;

    dbms_output.put_line (l_sql);
    dbms_output.put_line (l_empid);
    
    LOOP
     FETCH dynamiccursor
      INTO l_empname ;

     EXIT WHEN dynamiccursor%NOTFOUND;
    END LOOP;

    CLOSE dynamiccursor;

dbms_output.put_line (l_empname);

END;
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436273 is a reply to message #436270] Tue, 22 December 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using SQL*Plus I don't get what you get:
SQL> DECLARE
  2  
  3  TYPE cursor_type IS REF CURSOR;
  4  l_empid                 varchar2(25);
  5  l_startdate             integer(8);
  6  l_sql                   varchar2(1000);
  7  dynamiccursor           cursor_type;
  8  l_empname               varchar2(25);
  9  
 10  BEGIN
 11  
 12  l_empid := '12345';
 13  select to_char(sysdate, 'YYYYMMDD') into l_startdate from dual;
 14  
 15  l_sql := 'select ename from emp where empno = :xid ';
 16              
 17      OPEN dynamiccursor FOR l_sql using l_empid;
 18  
 19      dbms_output.put_line (l_sql);
 20      dbms_output.put_line (l_empid);
 21      
 22      LOOP
 23       FETCH dynamiccursor
 24        INTO l_empname ;
 25  
 26       EXIT WHEN dynamiccursor%NOTFOUND;
 27      END LOOP;
 28  
 29      CLOSE dynamiccursor;
 30  
 31  dbms_output.put_line (l_empname);
 32  
 33  END;
 34  /
select ename from emp where empno = :xid
12345


PL/SQL procedure successfully completed.

Regards
Michel
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436274 is a reply to message #436266] Tue, 22 December 2009 10:30 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Seem to work for me.

SQL> drop table employee;
 
Table dropped
SQL> create table employee
  2  (empid varchar2(25),
  3  empname varchar2(25));
 
Table created
SQL> insert into employee (empid,empname)
  2  values ('vd12345','vikasduhan');
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> DECLARE
  2  
  3  TYPE cursor_type IS REF CURSOR;
  4  l_empid                 varchar2(25);
  5  l_startdate             integer(8);
  6  l_sql                   varchar2(1000);
  7  dynamiccursor           cursor_type;
  8  l_empname               varchar2(25);
  9  
 10  BEGIN
 11  
 12  l_empid := 'vd12345';
 13  select to_char(sysdate, 'YYYYMMDD') into l_startdate from dual;
 14  
 15  l_sql := 'select empname from employee where empid = :xid ';
 16  
 17      OPEN dynamiccursor FOR l_sql using l_empid;
 18  
 19      dbms_output.put_line (l_sql);
 20      dbms_output.put_line (l_empid);
 21  
 22      LOOP
 23       FETCH dynamiccursor
 24        INTO l_empname ;
 25  
 26       EXIT WHEN dynamiccursor%NOTFOUND;
 27      END LOOP;
 28  
 29      CLOSE dynamiccursor;
 30  
 31  dbms_output.put_line (l_empname);
 32  
 33  END;
 
 34  /
 
select empname from employee where empid = :xid 
vd12345
vikasduhan
 
PL/SQL procedure successfully completed
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436275 is a reply to message #436273] Tue, 22 December 2009 10:31 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
@Michel: What is your Oracle version? JRowbottom's testcase writes 'Test test_120' for L_EMPNAME for me on Oracle 10.2.0.3 on Linux.

Anyway, there is one strange thing in the code - the content of L_EMPNAME is written after the loop (= after the FETCH which was not successful). It may clean the variable, although it did do clean it for me. Anyway, it would be worth trying to output the content of L_EMPNAME inside the LOOP (between EXIT and END LOOP statements).

[Edit: Rephrased the first paragraph as it was too vague]

[Updated on: Tue, 22 December 2009 10:38]

Report message to a moderator

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436276 is a reply to message #436273] Tue, 22 December 2009 10:44 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
@Michel
I substituted some of the values in the posted code. I'm using xoper in my original code, thats why the output didnt show xid.
I also tried to move the dbmsoutput between EXIT and END LOOP statements but as there is no value in the cursor, its returning as NULL and thats why it exits.
I'm thinking the bind variable is not capturing the value for l_empid properly.

@JRowbottom
Your test script works perfect. I need to check now what exactly I'm missing.

Thanks guys for your inputs!
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #436277 is a reply to message #436275] Tue, 22 December 2009 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@flyboy,
My version is 10.2.0.4, I changed employee to emp to use a table I have.

For your question, when data is not found the variables are not clear but keep their values (adding line 13 to initialize l_empname):
SQL> DECLARE
  2  
  3  TYPE cursor_type IS REF CURSOR;
  4  l_empid                 integer;
  5  l_startdate             integer(8);
  6  l_sql                   varchar2(1000);
  7  dynamiccursor           cursor_type;
  8  l_empname               varchar2(25);
  9  
 10  BEGIN
 11  
 12  l_empid := 0;
 13  l_empname := 'UNKNOWN';
 14  select to_char(sysdate, 'YYYYMMDD') into l_startdate from dual;
 15  
 16  l_sql := 'select ename from emp where empno = :xid ';
 17              
 18      OPEN dynamiccursor FOR l_sql using l_empid;
 19  
 20      dbms_output.put_line (l_sql);
 21      dbms_output.put_line (l_empid);
 22      
 23      LOOP
 24       FETCH dynamiccursor
 25        INTO l_empname ;
 26  
 27       EXIT WHEN dynamiccursor%NOTFOUND;
 28      END LOOP;
 29  
 30      CLOSE dynamiccursor;
 31  
 32  dbms_output.put_line (l_empname);
 33  
 34  END;
 35  /
select ename from emp where empno = :xid
0
UNKNOWN

PL/SQL procedure successfully completed.

But you are right, unless you only want the LAST value, the output should be inside the loop.

Regards
Michel

[Updated on: Tue, 22 December 2009 10:54]

Report message to a moderator

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438270 is a reply to message #436266] Thu, 07 January 2010 14:28 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Not sure if I should start a new thread for this issue or not ... this problem is somewhat related to the bind variables, thats why I replied to this post itself. The example discussed earlier worked perfect. I just had to use TRIMS for the empid.
Here's another issue. I had to use the bind variables in the dynamic SQL having same variable occuring several times. Can you please let me know if I'm using the OPEN cursor call properly (or the way I defined bind variables in dynamic SQLs)? Here is the code.

DECLARE
    l_comptskidentSqlStatement   VARCHAR2 (2000);
    l_tskidentSqlStatement       VARCHAR2 (2000);
    L_OPERID                     CHAR (8);
    l_startDate                  NUMBER (8);
    l_sql                        VARCHAR2 (10000);
    LL_TASKID                    CHAR (11);

    TYPE CURSOR_TYPE IS REF CURSOR;
    DynamicCursor                CURSOR_TYPE;

BEGIN

    l_tskidentSqlStatement := 'SELECT tskid FROM tskident WHERE  tskid IN 
(SELECT tskid FROM tskident WHERE idcode = 8 AND fieldnbr = 1  AND (fieldvalue) = ''123456789'')';

    l_comptskidentSqlStatement := 'SELECT tskid FROM comptskident WHERE  tskid IN 
(SELECT tskid FROM comptskident 
WHERE idcode = 8 AND fieldnbr = 1  AND (fieldvalue) = ''123456789'')';

    l_operid := 'nc10237';

    l_startdate := '20090107';

          l_sql :=
                'SELECT tskreq.tskid from ' 
             || 'tskreq,authdpt,operprofilex '
             || 'WHERE tskreq.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND tskreq.createdate >= :xstartdate '
             || ' AND tskreq.TSKID IN ( :tskidentsql ) UNION ALL ';
             
          l_sql :=
                l_sql
             || 'SELECT QAtskreq.tskid from ' 
             || 'QAtskreq,authdpt,operprofilex '
             || 'WHERE QAtskreq.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND QAtskreq.createdate >= :xstartdate '
             || ' AND QAtskreq.TSKID IN ( :tskidentsql ) UNION ALL ';
             
          l_sql :=
                l_sql
             || 'SELECT SUSPtskreq.tskid from ' 
             || 'SUSPtskreq,authdpt,operprofilex '
             || 'WHERE SUSPtskreq.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND SUSPtskreq.createdate >= :xstartdate '
             || ' AND SUSPtskreq.TSKID IN ( :tskidentsql ) UNION ALL ';
             
          l_sql :=
                l_sql
             || 'SELECT COMPTSK.tskid from ' 
             || 'COMPTSK,authdpt,operprofilex '
             || 'WHERE COMPTSK.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND COMPTSK.createdate >= :xstartdate '
             || ' AND COMPTSK.TSKID IN ( :Comptskidentsql ) ORDER BY TSKID DESC ';
             

            OPEN dynamiccursor FOR l_sql 
            USING l_operid,l_startdate,l_tskidentsqlstatement,
                  l_operid,l_startdate,l_tskidentsqlstatement,
                  l_operid,l_startdate,l_tskidentsqlstatement,
                  l_operid,l_startdate,l_comptskidentsqlstatement ; 

dbms_output.put_line (l_tskidentsqlstatement);
dbms_output.put_line (l_comptskidentsqlstatement);

                  
          LOOP
             FETCH DynamicCursor INTO LL_TASKID;
             dbms_output.put_line ('************');
             dbms_output.put_line (LL_TASKID);
             dbms_output.put_line ('************');
             EXIT WHEN DynamicCursor%NOTFOUND ; 
                                
          END LOOP;

          CLOSE DynamicCursor;
END;

[Updated on: Thu, 07 January 2010 14:32] by Moderator

Report message to a moderator

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438272 is a reply to message #438270] Thu, 07 January 2010 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I had to use the bind variables in the dynamic SQL having same variable occuring several times.

The name of the variables inside the SQL string does not matter they are just placeholders for PL/SQL. You could name them :1, :2, and so on.
As a corollary, you have to give in OPEN as much as variables as there are placeholders in the string.

Regards
Michel
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438273 is a reply to message #438272] Thu, 07 January 2010 14:41 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
So as I'm using 12 variables inside the SQL string ... and I'm using 12 variables while opening the cursor, there's is nothing wrong with the syntax .. right?

Also is there any way to see what value exactly l_sql holds before opening the cursor? I tried using dbms_output for it but it didnt show anything in the output.

Thanks!
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438274 is a reply to message #438273] Thu, 07 January 2010 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So as I'm using 12 variables inside the SQL string ... and I'm using 12 variables while opening the cursor, there's is nothing wrong with the syntax .. right?

Right (I have to admit that I was too lazy to count them and just give the principles).

Quote:
Also is there any way to see what value exactly l_sql holds before opening the cursor?

If values are in bind variables they are not in SQL string. When I say they are not, I mean they are NEVER in the SQL string. Oracle SQL engine gets the SQL string as you wrote it and then it gets the bind variables. The two parts are not handle in the same step of the parse (see wiki page parsing).

Regards
Michel

[Updated on: Thu, 07 January 2010 14:58]

Report message to a moderator

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438275 is a reply to message #438274] Thu, 07 January 2010 14:50 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks Michel!

Now that I have an idea I'm going on the right track, I'll figure it out what exactly went wrong with my code.

Thanks again!

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438280 is a reply to message #438275] Thu, 07 January 2010 15:41 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Did some debugging and the code works when I remove the SQL statement variable (l_tskidentSqlStatement) from the code. So that leads to another question. Can't we assign a SQL statement to a bind variable placeholder? I read on oracle.com that Bind variables are used in SQL and PL/SQL statements for holding data or result sets.

Also the parser does the following:
1) Validate the syntax of the statement.
2) Validate semantic of the statement.
3) Search in the shared pool.
4) Allocate memory in shared pool, if step 3 returns false.
5) Get the values of the bind variables.
6) Optimize query execution.

As per my understanding from above, when the parser gets to step 5, its already done with validating the syntax and semantics of the query. Does that mean, if we assign a SQL query (like i did) to the placeholder, parser will treat it as "data" instead of a SQL query and will not pass the results of the placeholder query to the main SQL query?

Please let me know.

Old one:
   l_tskidentSqlStatement := 'SELECT tskid FROM tskident WHERE  tskid IN 
(SELECT tskid FROM tskident WHERE idcode = 8 AND fieldnbr = 1  AND (fieldvalue) = ''123456789'')';

          l_sql :=
                'SELECT tskreq.tskid from ' 
             || 'tskreq,authdpt,operprofilex '
             || 'WHERE tskreq.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND tskreq.createdate >= :xstartdate '
             || ' AND tskreq.TSKID IN ( :tskidentsql ) ';

            OPEN dynamiccursor FOR l_sql 
            USING l_operid,l_startdate,l_tskidentsqlstatement;



New One:
          l_sql :=
                'SELECT tskreq.tskid from ' 
             || 'tskreq,authdpt,operprofilex '
             || 'WHERE tskreq.dptcode = authdpt.dptcode '
             || 'AND authdpt.profileid = operprofilex.profileid '
             || 'AND trim(operprofilex.operid) = :xoper '
             || ' AND tskreq.createdate >= :xstartdate ';
             

            OPEN dynamiccursor FOR l_sql 
            USING l_operid,l_startdate;

Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438282 is a reply to message #436266] Thu, 07 January 2010 15:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think your main problem here is that you can't put sub-queries in bind variables. Bind variables are like normal variables in static sql (in fact normal variables in static sql are bind variables). They can hold scalar values - dates, numbers, text. They can't hold SQL.
If you want to put repeating bits of SQL in variables then you need to use concatenation to add it to other bits of SQL.
Re: Dynamic SQL + Cursor + Bind variable isn't working [message #438326 is a reply to message #438280] Fri, 08 January 2010 01:58 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As per my understanding from above, when the parser gets to step 5, its already done with validating the syntax and semantics of the query. Does that mean, if we assign a SQL query (like i did) to the placeholder, parser will treat it as "data" instead of a SQL query and will not pass the results of the placeholder query to the main SQL query?

You exactly got the point.
This is why bind variables can only be data and not column or table name or worse a subquery.

Regards
Michel
Previous Topic: Composite Index
Next Topic: Avoid breakup in the sequence
Goto Forum:
  


Current Time: Fri Dec 09 08:10:08 CST 2016

Total time taken to generate the page: 0.05506 seconds