Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL in REF Cursor (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
Dynamic SQL in REF Cursor [message #328381] Fri, 20 June 2008 00:30 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Want to know some expert advice on dynamic SQL. Below I have 2 ways of building REF Cursor. There are parameters in the SELECT and I am using Dynamic SQL for reporting purpose and this report will hardly run once in a day, so there is no point in using BIND variables and I don’t see any performance impact as well?

DECLARE 
TYPE t_line_cursor IS REF CURSOR;
l_cursor   	t_line_cursor;
sql_stmt VARCHAR2(1000);

BEGIN
sql_stmt := 'Select 'X' from DUAL';
OPEN l_cursor for sql_stmt;
END;
/


DECLARE 
TYPE t_line_cursor IS REF CURSOR;
l_cursor   	t_line_cursor;
BEGIN
OPEN l_cursor for Select 'X' from DUAL;
END;
/


My reviewer commented to avoid Dynamic SQL in the query but for me both the way it’s same. Is there any performance or any good practice issue using dynamic SQL?
Re: Dynamic SQL in REF Cursor [message #328384 is a reply to message #328381] Fri, 20 June 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Apart from the fact that the first one does not compile and if it did it'd require a little bit more parsing than the second one, not really.
Only valid for this case.

Don't use dynamic SQL when you can use static one.
Use dynamic SQL only when you can't use static one.
Simple rule.

Regards
Michel

[Updated on: Fri, 20 June 2008 00:45]

Report message to a moderator

Re: Dynamic SQL in REF Cursor [message #328387 is a reply to message #328384] Fri, 20 June 2008 00:42 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Great!! Thanks for reply..

Will try to follow the simple rule Razz
Re: Dynamic SQL in REF Cursor [message #328412 is a reply to message #328387] Fri, 20 June 2008 01:51 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Just wanted to check if this is possible without dynamic query. I am passing a string of values (comma seperated) and wanted to compare with a value in table. With Dynamic SQL it is easy to just replace the variabe and build the SQL but not sure how directly it would happen. I understand there is COMMA_TO_TABLE and str2tab functions available but that would need extra TYPES to be created and more programming effort... It there any other direct way? or else Dynamic SQL is better option here??

CREATE TABLE P1 (COL1 VARCHAR2(100));

INSERT ALL  
INTO P1 VALUES (1)
INTO P1 VALUES (2)
INTO P1 VALUES (3)
INTO P1 VALUES (4)
INTO P1 VALUES (5)
INTO P1 VALUES (6)
INTO P1 VALUES (7)
SELECT * FROM DUAL
/

COMMIT;



DECLARE
  pi_val VARCHAR2(100) := '1,2,3,5';
  TYPE t_line_cursor IS REF CURSOR;
  l_cursor   	t_line_cursor;
BEGIN
	OPEN l_cursor for SELECT * from P1 WHERE COL1 IN (pi_val);
END;
/

Re: Dynamic SQL in REF Cursor [message #328414 is a reply to message #328412] Fri, 20 June 2008 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
varying elements in IN list

Regards
Michel
Re: Dynamic SQL in REF Cursor [message #328417 is a reply to message #328414] Fri, 20 June 2008 02:15 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Fri, 20 June 2008 12:34
varying elements in IN list

Regards
Michel



hmm.. as I mentioned earlier I am aware of str2tbl function Razz .. was just wondering if Oracle has provided any inbuilt function till now.. That would require extra Types and Function to be created which I don't want... I suppose may be dynamic query is a better option here
Re: Dynamic SQL in REF Cursor [message #328430 is a reply to message #328417] Fri, 20 June 2008 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the difference between Oracle gives a built-in type and you create a type?

Here another way but less efficient:
SQL> var maliste varchar2(100)
SQL> exec :maliste := '5,11,13,22,23,31,44,45'

PL/SQL procedure successfully completed.

SQL> select id, valeur
  2  from t
  3  where id IN (
  4    select substr(:maliste,
  5                  instr(','||:maliste||',', ',', 1, rn),
  6                  instr(','||:maliste||',', ',', 1, rn+1)
  7                  - instr(','||:maliste||',', ',', 1, rn) - 1) value
  8    from (select rownum rn from dual 
  9          connect by level 
 10                       <= length(:maliste)-length(replace(:maliste,',',''))+1)
 11    )
 12  order by id
 13  /
        ID VALEUR
---------- ------------------------------
         5 SYSTEM
        11 OUTLN
        22 MICHEL
        23 OPS$MCADOT101205
        31 SCOTT

5 rows selected.

Regards
Michel
Re: Dynamic SQL in REF Cursor [message #328446 is a reply to message #328430] Fri, 20 June 2008 03:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you put a leading and trailing comma on the list in pi_val, you can do it pretty easily. You just need to wrap the COL1 value in commas as well, and use INSTR:
  1  DECLARE
  2    pi_val VARCHAR2(100) := ',1,2,3,5,';
  3    TYPE t_line_cursor IS REF CURSOR;
  4    l_cursor    t_line_cursor;
  5  BEGIN
  6    for rec in (SELECT * from P1 WHERE instr(pi_val, ','||COL1||',')>0) loop
  7      dbms_output.put_line(rec.col1);
  8    end loop;
  9* END;
SQL> /
1
2
3
5

PL/SQL procedure successfully completed.
Re: Dynamic SQL in REF Cursor [message #328470 is a reply to message #328446] Fri, 20 June 2008 05:10 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Nice and simple idea... As the database is not too heavy and so performance is not a big issue... (as I suppose this would ignore the indexes on the column)...

Thanks
Previous Topic: ORA-01652 when running procedure
Next Topic: how to use the & symbol in text (merged)
Goto Forum:
  


Current Time: Mon Dec 05 13:11:25 CST 2016

Total time taken to generate the page: 0.05410 seconds