Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Ines.

Asked: August 19, 2000 - 10:58 am UTC

Answered by: Tom Kyte - Last updated: February 01, 2011 - 10:02 am UTC

Category: Database - Version: version 8.0.5

Viewed 1000+ times

You Asked

Hi Tom,
is there an easier way to handle a 'select field1, ... from table' by
dynamic sql instead of using dbms_sql.bind_variable and these stuff?
The problem is that the number of fields can be different.

thanks for help
Ines


and we said...


In all releases -- you'll have to use dbms_sql.* when you do not know the number of fields.

What I typically do in a case like this, when I do not know the number of fields/types of fields -- is just bind everything to 1 varchar2 field. Since I don't know their types at compile time it really does not matter if I fetch them into a date or a string -- so I fetch into a string. This makes it very easy to use.

For example, here is a plsql snippet i have for 8.0. It lets me execute something like:


scott@8.0> @printtbl8 'select * from emp where ename = "KING" '
EMPNO : 7839
ENAME : KING
JOB : PRESIDENT
MGR :
HIREDATE : 17-nov-2081 00:00:00
SAL : 5000
COMM :
DEPTNO : 10
-----------------

PL/SQL procedure successfully completed.


(note my use of " instead of ' to pass in a CHARACTER STRING constant -- this is just my convention, i use a replace in the code later on)....

The code to do this is simply:

scott@8.0> l
1 declare
2 l_theCursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_descTbl dbms_sql.desc_tab;
6 l_colCnt number;
7 procedure execute_immediate( p_sql in varchar2 )
8 is
9 BEGIN
10 dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
11 l_status := dbms_sql.execute(l_theCursor);
12 END;
13 begin
14 execute_immediate( 'alter session set nls_date_format=
15 ''dd-mon-yyyy hh24:mi:ss'' ');
16 dbms_sql.parse( l_theCursor,
17 replace( '&1', '"', ''''),
18 dbms_sql.native );
19 dbms_sql.describe_columns( l_theCursor,
20 l_colCnt, l_descTbl );
21 for i in 1 .. l_colCnt loop
22 dbms_sql.define_column( l_theCursor, i,
23 l_columnValue, 4000 );
24 end loop;
25 l_status := dbms_sql.execute(l_theCursor);
26 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
27 for i in 1 .. l_colCnt loop
28 dbms_sql.column_value( l_theCursor, i,
29 l_columnValue );
30 dbms_output.put_line
31 ( rpad( l_descTbl(i).col_name,
32 30 ) || ': ' || l_columnValue );
33 end loop;
34 dbms_output.put_line( '-----------------' );
35 end loop;
36 execute_immediate( 'alter session set nls_date_format=
37 ''dd-MON-yy'' ');
38 exception
39 when others then
40 execute_immediate( 'alter session set
41 nls_date_format=''dd-MON-yy'' ');
42 raise;
43* end;
scott@8.0>


See how I use a describe table to find the number of columns and then simply loop to bind all of them to a varchar2(4000). (very easy, very compact, very safe). Then I just fetch each of the columns into a single variable -- again very compact, very easy.

another alternative would be for you to code a "my_dbms_sql" helper package, something like:

scott@8.0> create or replace package my_dbms_sql
2 as
3 procedure define_all( p_cursor in integer );
4 type varchar2_table is table of varchar2(4000) index by
5 binary_integer;
6 function fetch_row( p_cursor in integer )
7 return varchar2_table;
8 end;
9 /

Package created.

scott@8.0> create or replace package body my_dbms_sql
2 as
3 g_number_of_columns dbms_sql.number_table;
4 procedure define_all( p_cursor in integer )
5 as
6 l_columnValue varchar2(4000);
7 l_descTbl dbms_sql.desc_tab;
8 l_colCnt number;
9 begin
10 dbms_sql.describe_columns( p_cursor,
11 l_colCnt, l_descTbl );
12 for i in 1 .. l_colCnt loop
13 dbms_sql.define_column( p_cursor, i,
14 l_columnValue, 2000 );
15 end loop;
16 g_number_of_columns(p_cursor) := l_colCnt;
17 end;
18 function fetch_row( p_cursor in integer )
19 return varchar2_table
20 is
21 l_return varchar2_table;
22 begin
23 for i in 1 .. g_number_of_columns(p_cursor) loop
24 l_return(i) := NULL;
25 dbms_sql.column_value( p_cursor, i,
26 l_return(i) );
27 end loop;
28 return l_return;
29 end;
30 end;
31 /

Package body created.


and now you could code more simply

scott@8.0> set verify off
scott@8.0> declare
2 l_theCursor integer default dbms_sql.open_cursor;
3 l_status integer;
4 l_data my_dbms_sql.varchar2_table;
5
5 procedure execute_immediate( p_sql in varchar2 )
6 is
7 BEGIN
8 dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
9 l_status := dbms_sql.execute(l_theCursor);
10 END;
11 begin
12 execute_immediate( 'alter session set nls_date_format=
13 ''dd-mon-yyyy hh24:mi:ss'' ');
14 dbms_sql.parse( l_theCursor,
15 replace( 'select * from emp where ename = "KING"',
16 '"', ''''),
17 dbms_sql.native );
18 my_dbms_sql.define_all( l_theCursor );
19 l_status := dbms_sql.execute(l_theCursor);
20 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
21 l_data := my_dbms_sql.fetch_row( l_theCursor );
22 for i in 1 .. l_data.count loop
23 dbms_output.put_line( l_data(i) );
24 end loop;
25 end loop;
26 execute_immediate( 'alter session set nls_date_format=
27 ''dd-MON-yy'' ');
28 exception
29 when others then
30 execute_immediate( 'alter session set
31 nls_date_format=''dd-MON-yy'' ');
32 raise;
33 end;
34 /
7839
KING
PRESIDENT
17-nov-2081 00:00:00
5000
10

PL/SQL procedure successfully completed.






and you rated our response

  (39 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

dynamic sql in pl*sql in V8

April 10, 2001 - 10:36 am UTC

Reviewer: chris barr from Cambridge, MA USA

wonderful code examples, etc. ...
for DBMS_SQL, "execute immediate" as execute_immediate



Performance Consideration

January 21, 2002 - 6:51 am UTC

Reviewer: Lakshmi Narasimhan R from Dubai, UAE

Tom

What is the impact of Performance if we are using
dynamic sql (Execute immdiate in particular) in packages. particularly if i am using in a loop of large number of iteration. We are having 8.1.7 with RBO as optimizer. I am forced to use execute immediate in a package which will loop through 1000's of time to dynamically check few values.


Tom Kyte

Followup  

January 21, 2002 - 9:53 am UTC

Well, if the query is dynamic (not known at compile time) but is "static" for the life of the session (eg: once you built it once in the session -- its built) -- DBMS_SQL will be more performant. See

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:455220177497 <code>

for examples. If you can PARSE once and execute 1,000's of times -- that is better then parse/execute/close 1,000's of time (execute immediate will ALWAYS parse/execute/close - no cursor reuse, with DBMS_SQL, you control it)

using Only Selects

January 23, 2002 - 1:07 am UTC

Reviewer: Lakshmi narasimhan R from Dubai , UAE

Tom

Extremely thanks for the answer to my performance issue. As you said i am using package Plsql tables only to construct the Select statement and parse it for checking. I am using it to avoid writting huge hierarchy of If statements. I am having around 9 columns in a big table each column having upto 4 values and the combination is 9*4*4. So instead of writting lot of if nested if i thought of constructing a select statement and just parsing it into exec immediate like below

Select 1 from dual where (my where clause constructed) and if it returns then my condition satisfied otherwise condition failed. i am hoping this is the best way for maintenance also and for performance also.

am i correct in my belief.



Tom Kyte

Followup  

January 23, 2002 - 7:11 am UTC

Static code is going to be faster then dynamically constructed code.

If you do this dynamic stuff - make sure you use BIND VARIABLES, if you build unique statements and execute them without using bind varaibles, you will trash your shared pool, kill performance and inhibit scalability.

Can you be truly dynamic in bulk?

February 25, 2002 - 9:51 am UTC

Reviewer: Adrian Billington from UK

Tom

This dynamic cursor processing is excellent - but can we do it in array processing in any way? I want to combine array fetching with UTL_FILE to dump out csv data from tables unknown until runtime. So I thought I would take the benefits of bulk fetching and then memory looping to write out the data, rather than row-by-row looping.

I'm sure I could construct some dynamic PL/SQL using the DBMS_SQL.DESCRIBE procedure but this would be long-winded. Do you think my approach is worth it or should I stick to row-by-row?

Many thanks

Adrian

Tom Kyte

Followup  

February 25, 2002 - 10:44 am UTC

If you have my book -- I demonstrate fully array fetching with DBMS_SQL. There are array binds and array fetches available, read the docs on DBMS_SQL.

Using Native method.

February 25, 2002 - 11:46 pm UTC

Reviewer: Sandeep from India, Bangalore

Very useful examples.

Is there a way to use native sql for "Method 4", where we are not sure about the number of columns in the cursor.

Tom Kyte

Followup  

February 26, 2002 - 9:00 am UTC

Nope, not in plsql -- if you have my book I cover this in depth in a chapter on dynamic sql. I show you when dbms_sql is right and when NDS is right. dbms_sql is not dead!

Array fetching of unknown query

February 26, 2002 - 3:38 am UTC

Reviewer: Adrian Billington from UK

Tom

Thanks for the response. I do have your book and have recommended it to many others as it is the best :)

As I have the book, I know that all your examples have EITHER pre-determined queries for array fetching OR DBMS_SQL.DESCRIBE examples that fetch into a V2(4000) variable.

I am trying to combine the two. I don't know the query until runtime and therefore don't know how many PL/SQL tables to declare. So I'm constructing a "v_plsql" variable which holds a dynamically built anonymous block based on the DBMS_SQL.DESCRIBE of the query to dump the data.

Now I have completed an example and am about to benchmark it by comparing it to something similar to your dump_table_to_csv row-by-row procedure. I'm pretty sure that the bulk fetching will provide some benefit on large tables, so if you wish, I can suplpy you with the details when done.

Thanks

Adrian

It's quicker in dynamic pl/sql and array fetching

February 26, 2002 - 7:17 am UTC

Reviewer: Adrian Billington from UK

Tom

My benchmarks have shown that to dynamically construct a PL/SQL block to include arrays for each column in the original query and use bulk fetching is quicker than row-by-row fetching.

To dump 150K ALL_OBJECTS rows using row-by-row and concantenating the columns into a V2(4000) variable took almost twice as long as building the PLSQL code and dumping the data into arrays of 1000 and writing these out to file.

Just thought you might be interested ;)

Adrian

Tom Kyte

Followup  

February 26, 2002 - 9:11 am UTC

post the code! share and share alike ;)

print_table

February 27, 2002 - 12:24 am UTC

Reviewer: Yogeeraj from Mauritius

Hello,

I wanted to create the print_table procedure and demonstrate it all my developers. Unfortunately, it is working eratically. is it that i did something wrong?

First time, i created the procedure print_table, everything was fine:
-------------------------------------------------------
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

yd@cmtdb-SQL>@print_table

Procedure created.

Elapsed: 00:00:00.62
yd@cmtdb-SQL>

yd@cmtdb-SQL>exec print_table( 'select * from t where name = ''aaa''');
NAME                          : aaa
ADDRESS1                      : aaaa
ADDRESS2                      : aaaa
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
yd@cmtdb-SQL>
yd@cmtdb-SQL>grant execute on print_table to public;

Grant succeeded.

Elapsed: 00:00:00.64
yd@cmtdb-SQL>grant select on t to cmtstore;

Grant succeeded.

Elapsed: 00:00:00.11

yd@cmtdb-SQL>create public synonym print_table for yd.print_table;

Synonym created.

Elapsed: 00:00:00.09
---------------------------------------------------------

Now, i connect as the user to whom i granted the procedure:

---------------------------------------------------------
yd@cmtdb-SQL>connect cmtstore/cmtstore@cmtdb
Connected.
cmtstore@cmtdb-SQL>exec print_table( 'select * from yd.t where name = ''aaa''');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
----------------------------------------------------------
Oops! does not work... ;(

Now, i get back to my own account:
----------------------------------------------------------
cmtstore@cmtdb-SQL> connect yd/yd@cmtdb
Connected.
yd@cmtdb-SQL> exec print_table('select * from yd.t where name=''aaa''');

PL/SQL procedure successfully completed.

SQL>
----------------------------------------------------------
Oops! here too it does not work... ;(

Can you see something wrong? or is it that i did not do the steps right?

thanks
Yogeeraj 

Tom Kyte

Followup  

February 27, 2002 - 8:10 am UTC

when you go "connect u/p" -- you lost the SET SERVEROUTPUT ON setting

I use a script "connect.sql" myself that is:

connect &1
@login


and have trained myself to always:

SQL> @connect scott/tiger

that way, the login.sql gets run and my default settings are always there. 

How do I post code and results?

February 27, 2002 - 4:37 am UTC

Reviewer: Adrian from UK

Tom

How do I post my bulk fetch code as requested? Review says no more than 1,000 words...

Thanks

Adrian

Tom Kyte

Followup  

February 27, 2002 - 8:15 am UTC

send it to me, thomas.kyte@oracle.com, I'll add it.

Adrian Sent it and here it is:

Tom

Re-written to use bulk collect and limit and also declare the correct type of arrays...

I've included the standalone procedure (that I'll now have re-engineer into my package), the output code
and a summary of the benchmark results.

Note ABB_DUMP_TABLE used to benchmark is a copy and multiple-insert of ALL_OBJECTS table up to about 150K rows...

----------------------
-- Procedure...
----------------------
CREATE OR REPLACE PROCEDURE dump_data_bulk_collect (
query_in IN VARCHAR2,
file_prefix_in IN VARCHAR2,
directory_in IN VARCHAR2,
array_size_in IN PLS_INTEGER,
delimiter_in IN VARCHAR2 DEFAULT NULL) IS

v_fh UTL_FILE.FILE_TYPE;
v_ch BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
v_sql VARCHAR2(32767) := query_in;
v_plsql VARCHAR2(32767);
v_dir VARCHAR2(512) := directory_in;
v_outfile VARCHAR2(128) := file_prefix_in||'.dat';
v_sqlfile VARCHAR2(128) := file_prefix_in||'.sql';
v_arr_size PLS_INTEGER := array_size_in;
v_col_cnt PLS_INTEGER := 0;
v_delimiter VARCHAR2(1) := NULL;
v_type VARCHAR2(8);
t_describe DBMS_SQL.DESC_TAB;

/* Procedure to output code for debug and assign plsql variable... */
PROCEDURE put (
fh_in IN UTL_FILE.FILE_TYPE,
string_in IN VARCHAR2
) IS
BEGIN
UTL_FILE.PUT_LINE(fh_in,string_in);
v_plsql := v_plsql||string_in;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END put;

BEGIN

/* Open the file that the dynamic PL/SQL will be written to for debug... */
v_fh := UTL_FILE.FOPEN(v_dir, v_sqlfile, 'W');

/* Parse the query that will be used to fetch all the data to be written out... */
DBMS_SQL.PARSE(v_ch, v_sql, DBMS_SQL.NATIVE);

/* Now describe the dynamic SQL to analyze the number of columns in the query... */
DBMS_SQL.DESCRIBE_COLUMNS(v_ch, v_col_cnt, t_describe);

/* Now begin the dynamic PL/SQL... */
put(v_fh,'DECLARE');
put(v_fh,' v_fh UTL_FILE.FILE_TYPE;');
put(v_fh,' CURSOR cur_sql IS');
put(v_fh,' '||REPLACE(v_sql,'''','''''')||';');

/* Now loop through the describe table to declare arrays in the dynamic PL/SQL... */
FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
IF t_describe(i).col_type = 2 THEN
v_type := 'NUMBER';
ELSIF t_describe(i).col_type = 12 THEN
v_type := 'DATE';
ELSE
v_type := 'VARCHAR2';
END IF;
put(v_fh,' t_'||t_describe(i).col_name||' DBMS_SQL.'||v_type||'_TABLE;');
END LOOP;

/* Syntax to set the date format to preserve time in the output, open the out file and start to collect... */
put(v_fh,'BEGIN');
put(v_fh,' EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = ''''DD-MON-YYYY HH24:MI:SS'''''';');
put(v_fh,' v_fh := UTL_FILE.FOPEN('''||v_dir||''','''||v_outfile||''',''W'');');
put(v_fh,' OPEN cur_sql;');
put(v_fh,' LOOP');
put(v_fh,' FETCH cur_sql');
put(v_fh,' BULK COLLECT INTO t_'||t_describe(t_describe.FIRST).col_name||',');

/* Add all other arrays into the fetch list except the last... */
FOR i IN t_describe.FIRST + 1 .. t_describe.LAST - 1 LOOP
put(v_fh,' t_'||t_describe(i).col_name||',');
END LOOP;

/* Add in the last array and limit... */
put(v_fh,' t_'||t_describe(t_describe.LAST).col_name||' LIMIT '||v_arr_size||';');

/* Now add syntax to loop though the fetched array and write out the values to file... */
put(v_fh,' IF t_'||t_describe(t_describe.FIRST).col_name||'.COUNT > 0 THEN');
put(v_fh,' FOR i IN t_'||t_describe(t_describe.FIRST).col_name||'.FIRST .. '||
't_'||t_describe(t_describe.FIRST).col_name||'.LAST LOOP');

FOR i IN t_describe.FIRST .. t_describe.LAST LOOP
put(v_fh,' UTL_FILE.PUT(v_fh,'''||v_delimiter||'''||t_'||t_describe(i).col_name||'(i));');
v_delimiter := NVL(delimiter_in,',');
END LOOP;

/* Add a new line marker into the file and move on to next record... */
put(v_fh,' UTL_FILE.NEW_LINE(v_fh);');
put(v_fh,' END LOOP;');

/* Complete the IF statement... */
put(v_fh,' END IF;');

/* Add in an EXIT condition and complete the loop syntax... */
put(v_fh,' EXIT WHEN cur_sql%NOTFOUND;');
put(v_fh,' END LOOP;');
put(v_fh,' CLOSE cur_sql;');
put(v_fh,' UTL_FILE.FCLOSE(v_fh);');

/* Add in some exception handling... */
put(v_fh,'EXCEPTION');
put(v_fh,' WHEN UTL_FILE.INVALID_PATH THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - invalid path.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.INVALID_MODE THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - invalid mode.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.INVALID_OPERATION THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - invalid operation.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.INVALID_FILEHANDLE THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - invalid filehandle.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.WRITE_ERROR THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - write error.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.READ_ERROR THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - read error.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN UTL_FILE.INTERNAL_ERROR THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(''Error - internal error.'');');
put(v_fh,' RAISE;');
put(v_fh,' WHEN OTHERS THEN');
put(v_fh,' DBMS_OUTPUT.PUT_LINE(SQLERRM);');
put(v_fh,' RAISE;');
put(v_fh,'END;');

/* Now close the cursor and sql file... */
DBMS_SQL.CLOSE_CURSOR(v_ch);
UTL_FILE.FCLOSE(v_fh);

/* Execute the v_plsql to dump the data... */
EXECUTE IMMEDIATE v_plsql;

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - write error.');
RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - read error.');
RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - internal error.');
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END dump_data_bulk_collect;
/

---------------------------------
-- Generated code...
---------------------------------
DECLARE
v_fh UTL_FILE.FILE_TYPE;
CURSOR cur_sql IS
SELECT * FROM abb_dump_table;
t_OWNER DBMS_SQL.VARCHAR2_TABLE;
t_OBJECT_NAME DBMS_SQL.VARCHAR2_TABLE;
t_SUBOBJECT_NAME DBMS_SQL.VARCHAR2_TABLE;
t_OBJECT_ID DBMS_SQL.NUMBER_TABLE;
t_DATA_OBJECT_ID DBMS_SQL.NUMBER_TABLE;
t_OBJECT_TYPE DBMS_SQL.VARCHAR2_TABLE;
t_CREATED DBMS_SQL.DATE_TABLE;
t_LAST_DDL_TIME DBMS_SQL.DATE_TABLE;
t_TIMESTAMP DBMS_SQL.VARCHAR2_TABLE;
t_STATUS DBMS_SQL.VARCHAR2_TABLE;
t_TEMPORARY DBMS_SQL.VARCHAR2_TABLE;
t_GENERATED DBMS_SQL.VARCHAR2_TABLE;
t_SECONDARY DBMS_SQL.VARCHAR2_TABLE;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MON-YYYY HH24:MI:SS''';
v_fh := UTL_FILE.FOPEN('/tmp','abb_dump_table.dat','W');
OPEN cur_sql;
LOOP
FETCH cur_sql
BULK COLLECT INTO t_OWNER,
t_OBJECT_NAME,
t_SUBOBJECT_NAME,
t_OBJECT_ID,
t_DATA_OBJECT_ID,
t_OBJECT_TYPE,
t_CREATED,
t_LAST_DDL_TIME,
t_TIMESTAMP,
t_STATUS,
t_TEMPORARY,
t_GENERATED,
t_SECONDARY LIMIT 1000;
IF t_OWNER.COUNT > 0 THEN
FOR i IN t_OWNER.FIRST .. t_OWNER.LAST LOOP
UTL_FILE.PUT(v_fh,''||t_OWNER(i));
UTL_FILE.PUT(v_fh,','||t_OBJECT_NAME(i));
UTL_FILE.PUT(v_fh,','||t_SUBOBJECT_NAME(i));
UTL_FILE.PUT(v_fh,','||t_OBJECT_ID(i));
UTL_FILE.PUT(v_fh,','||t_DATA_OBJECT_ID(i));
UTL_FILE.PUT(v_fh,','||t_OBJECT_TYPE(i));
UTL_FILE.PUT(v_fh,','||t_CREATED(i));
UTL_FILE.PUT(v_fh,','||t_LAST_DDL_TIME(i));
UTL_FILE.PUT(v_fh,','||t_TIMESTAMP(i));
UTL_FILE.PUT(v_fh,','||t_STATUS(i));
UTL_FILE.PUT(v_fh,','||t_TEMPORARY(i));
UTL_FILE.PUT(v_fh,','||t_GENERATED(i));
UTL_FILE.PUT(v_fh,','||t_SECONDARY(i));
UTL_FILE.NEW_LINE(v_fh);
END LOOP;
END IF;
EXIT WHEN cur_sql%NOTFOUND;
END LOOP;
CLOSE cur_sql;
UTL_FILE.FCLOSE(v_fh);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid path.');
RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid mode.');
RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid operation.');
RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('Error - invalid filehandle.');
RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - write error.');
RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - read error.');
RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error - internal error.');
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END;

---------------------------------------
-- Results...
---------------------------------------
What arraysize to use? 1000
Row-by-row fetch on arraysize 1000..
Elapsed: 00:04:03.62

DBMS_SQL bulk fetch on arraysize 1000..
Elapsed: 00:02:17.38

Bulk collect on arraysize 1000..
Elapsed: 00:02:15.36

**********************************

What arraysize to use? 5000
Row-by-row fetch on arraysize 5000..
Elapsed: 00:03:56.28

DBMS_SQL bulk fetch on arraysize 5000..
Elapsed: 00:02:20.03

Bulk collect on arraysize 5000..
Elapsed: 00:02:15.86

**********************************

What arraysize to use? 10000
Row-by-row fetch on arraysize 10000..
Elapsed: 00:04:00.77

DBMS_SQL bulk fetch on arraysize 10000..
Elapsed: 00:02:20.42

Bulk collect on arraysize 10000..
Elapsed: 00:02:20.60




"cursor reuse" <> "parsed plan reuse" ?

October 14, 2002 - 2:00 pm UTC

Reviewer: Robert C from PA

Cool stuff ! thanks Adrian for sharing.

Tom in your followup, you said...
>>(execute immediate will ALWAYS
parse/execute/close - no cursor reuse, with DBMS_SQL, you control it) <<

"cursor reuse" is NOT the same,exact thing as "parsed plan reuse", is it ?

Also, I've been wondering why about this....
is it a fact that the search feature of this site
is not designed to look for article id like I searched for
"584023239495" & "ID 584023239495" for this one.

Thanks


Tom Kyte

Followup  

October 14, 2002 - 3:57 pm UTC

correct, cursors are what cause you to parse.

You parse a statement and you have a cursor that points to it. You can then execute that cursor over and over and over without having to do a soft parse again.

With EXECUTE IMMEDIATE, you have no cursor -- you do not explicitly control the cursor -- hence it will soft parse each time.


I index the text - I do not have the id's (which are basically just random numbers) in the body of the text I index. Just the question, the answer and the followups.

Whoops...unnecessary loop ?

October 14, 2002 - 4:55 pm UTC

Reviewer: Robert Chin from PA

Tom, please confirm that in Adrian's "Generated Code"

>>OPEN cur_sql;
LOOP
FETCH cur_sql
BULK COLLECT INTO t_OWNER,
<snip>
<<

THAT LOOP is not necessary but a bug.
I thought the idea of BULK COLLECT is so that we don't
"FETCH...LOOP" no more....
thanks



Tom Kyte

Followup  

October 14, 2002 - 7:45 pm UTC

I didn't read his code, caveat emptor - use at your own risk (just like my code)

It's great!

October 14, 2002 - 10:19 pm UTC

Reviewer: Rory Concepcion from Philippines

Your solutions and scripts are great and very useful.
Just one question. I generate an error everytime I execute the @table/printtable 'select table_name from user_tables order by table_name';

TABLE_NAME : ACCOUNT
-----------------
TABLE_NAME : ACCOUNT_BILLING_INFO
-----------------
TABLE_NAME : ACCOUNT_PAYMENT_INFO
-----------------
TABLE_NAME : ACTIVITY
-----------------
TABLE_NAME : ADDRESS
-----------------
TABLE_NAME : APPCASE
-----------------
TABLE_NAME : AUDIT_LOG
-----------------
TABLE_NAME : BATCH_TEMP
-----------------
TABLE_NAME : BILLING
-----------------
TABLE_NAME : CALLCARDINFO
-----------------
TABLE_NAME : CAMPAIGN
-----------------
TABLE_NAME : CHAINED_ROWS
-----------------
TABLE_NAME : CONTACT
-----------------
TABLE_NAME : CURRENCY
-----------------
TABLE_NAME : CUSTACCTINFO
-----------------
TABLE_NAME : CUSTSERV
-----------------
TABLE_NAME : DATA_TEMP
-----------------
TABLE_NAME : DIGIFAULT
-----------------
TABLE_NAME : DISCOUNT
-----------------
TABLE_NAME : EP_ACC_REL
-----------------
TABLE_NAME : EP_CERTIFICATEREG
-----------------
TABLE_NAME : EP_CODESINTEMPLATES
-----------------
TABLE_NAME : EP_CON_REL
-----------------
TABLE_NAME : EP_CROSS_SALE
-----------------
TABLE_NAME : EP_MERGECODE
-----------------
TABLE_NAME : EP_PAR_REL
-----------------
TABLE_NAME : EP_TRANSACTION
-----------------
TABLE_NAME : EVENTS
-----------------
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at line 42

Does the script have a limit in size?

Thanks

Tom Kyte

Followup  

October 15, 2002 - 7:39 am UTC

SQL> set serveroutput on size 1000000

1,000,000 bytes of output will be the max using this technique. 

Robert, read my code properly

October 15, 2002 - 3:48 am UTC

Reviewer: Adrian Billington from UK

Tom

The reason I'm looping in my generated code is because I'm using LIMITs of 1000 rows to my BULK COLLECTs. Robert Chin completely failed to notice that when "reading" the code...

Regards

Adrian

Dynamic PL/SQL blocks

December 12, 2002 - 7:43 am UTC

Reviewer: Praveen KV from Bangalore

Dear Tom,

I came across the following problem.

How to dynamically change the 'conditional part' of an IF statement?


We are required to write a procedure to check the data in a table 'DataTab' such that the data satisfy a set of criterias given in another table 'ConditionTab'.
For ex: if 'DataTab' contains a record with A=a1 then value of B must be b1. We need to make note of those records that does not satisfies these conditions.


"DataTab"

A B C D
--------------------------
a1 b1 c1 d1 correct record
a2 b2 c2 d2 correct record
a1 b0 c1 d1 incorrect record
a2 b2 c2 d0 incorrect record
--------------------------

"ConditionTab"

Field Criteria
-------------------------
A 'A=a1 and B=b1' The idea is to just pick the string in the
A 'A=a2 and B=b2' criteria field and use this as the condition
C 'C=c1 and D=d1' in an 'IF' clause
C 'C=c2 and D=d2'
-------------------------


One approch would be to directly hard code the logic, ignoring the existance of 'ConditionTab' table.

<for each loans loop>
<for each conditions loop>

if (loans.A =a1 and loans.b1) then --criteria from ConditionTab is hardcoded here.
<<do something>>
end if;

end loop;
end loop;


As you can guess this approch is tedious when conditions need to be changed later.

In the following block I use the dynamic PL/SQL structure.

<<for each v_data in LoanTab loop>>

<<for each v_conditions in ConditionTab loop>>
v_sql := 'BEGIN ' ||
'IF NOT (' || v_conditions.criteria || ' ) THEN ' ||
<<do something>>
'END IF;' ||
'END; ';
end loop;
end loop;

EXECUTE IMMEDIATE v_sql;

Now 'v_conditions.criteria' is supposed to contain the string "if v_data.A=a1 and v_data.B=b1" (Assume 'v_data' is prefixed to A & B somehow)


I am getting the following error:

ERROR at line 1:
ORA-06550: line 1, column 11:
PLS-00201: identifier 'v_data.A' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "mydb.quality_check", line 51
ORA-06512: at line 1

Is there any other way to handle this problem? Or is it possible with dynamic PL/SQL itself?

Kindly advise

Thanks and Regards

Praveen


Tom Kyte

Followup  

December 12, 2002 - 10:04 am UTC

tedious generally implies "most performant, assured to work at runtime".

I'm all for generic code, but if this is something that needs to be run a lot of times, real code will massively outperf the dynamic code.


IF the criteria is really:

"ConditionTab"

Field Criteria
-------------------------
A A='a1' and B='b1'
A A='a2' and B='b2'
C C='c1' and D='d1'
C C='c2' and D='d2'



Then, a simple

for x in ( select * from conditionTab )
loop
execute immediate 'alter session set cursor_sharing=force';
open ref_cursor for
'select * from loanTab where not( ' || x.criteria || ' )';
execute immediate 'alter session set cursor_sharing=exact';
loop
fetch ref_cursor into l_record;
exit when ref_cursor%notfound;
..... process "bad" data ......
end loop;
close ref_cursor;
end loop;



Re:Dynamic PL/SQL blocks

December 16, 2002 - 5:48 am UTC

Reviewer: Praveen KV from Bangalore

Hi Tom,

Thankyou very much for the answer. But the answer did arise a few more doubts.

a) Why exactly we need to force a cursor sharing ?

b) Why we need to revert the cursor sharing property with in the loop itself ?

c) By using a different way of coding ie, using dynamic SQL instead of dynamic PL/SQL, do you mean we cannot use the dynamic PL/slq structure to dynamically change the condition-part of an 'IF' structure, as I had coded?

Please advise.

Thanks and regards

Praveen


Tom Kyte

Followup  

December 16, 2002 - 8:02 am UTC

a) because we want this to scale. If you read this site on a periodic basis, you must have hear me say at some point "use bind variables". that is what cursor_sharing is all about (search for

cursor_sharing

on this site to read more about it)

b) because we only want cursor sharing when we need it -- for that dynamically opened cursor. I would not blindly just set cursor sharing on for all other SQL in there. There are side effects from it that might not be good for your existing code.

c) you don't need to -- so I didn't? You asked a question and I showed you how I would approach it. I would do AS MUCH WORK AS POSSIBLE in SQL (which I did and you were not) and as little as possible in PLSQL -- dynamic/static, doesn't make a difference. Let SQL do the work whenever possible.

How to use NVL in Refcursor.

January 25, 2003 - 6:30 am UTC

Reviewer: Chandra S.Reddy from Hyd, India

Hi Tom,
Your answers are very great and useful.

I have one issue related usage of NVL in dynamic query.
Please look into the below code.
Here I definately want to construct dynamic query and must be executed through Refcursor.

PROCEDURE SP_SWIP_GetWires( --some more params--- ,out_cvGenric OUT PKG_SWIP_CommDefi.Gencurtyp) IS
v_cSelectQuery VARCHAR2(1000) ;
BEGIN
v_cSelectQuery := 'SELECT WR.WIRE_TRN_NUMBER,
TO_CHAR(WR.WIRE_DATE,' || CHR(39)|| 'MM/DD/YY' || CHR(39)||'),
WR.SU,
'||CHR(39)||'Y'||CHR(39)||' POST_TO_LNCTR_STATUS_FLAG,
WR.PROCESS_STATUS_ID
FROM T_SWIP_WIRE WR
WHERE WR.REDIRECT_OPERATION_CENTER_ID = :OperationCenterId ' ;

OPEN out_cvGenric FOR v_cSelectQuery USING in_nOperationCenterId;

EXCEPTION
-- more code here

END SP_SWIP_GetWires; --End of procedure

In the above code, SU column of T_SWIP_WIRE WR table will contain NULL values.There I should apply NVL function here and it should be like NVL(WR.SU,'').i.e empty string.
If I do so, code is throwing errors and did not get records.
And also NULL values should be displayed at top in order if I apply sort on SU column.

Please suggest me.



Tom Kyte

Followup  

January 25, 2003 - 11:23 am UTC

Looks like financial stuff -- important data -- money involved. Yet only 3 short years after y2k and you are using yy?????? bbbbbaaaaaadddddd idea don't you think?

empty string is null as well. that is like saying nvl(x,null). pick something else.


order by su NULLS FIRST

or

order by su NULLS LAST

you know, you can avoid all of those extra concats by:


v_cSelectQuery := 'select wr.wire_trn_number,
to_char( wr.wire_date, ''mm/dd/yyyy'' ),
wr.su,
''Y'' post_to_lnctr_status_flag,
wr.process_status_id


Also -- if that code accurately reflects your code -- don't use dynamic sql at all, use static sql, no need for dynamic.

DBMS_SQL Array fetching

April 14, 2003 - 6:19 am UTC

Reviewer: AndreyP from Germany

Hello,
what if we need to "array fetch" a Varchar2(4000) field using DBMS_SQL? Or we can do this only with row-by-row fetching? You know what I mean, since the definition in DBMS_SQL is:

type Varchar2_Table is table of varchar2(2000) index by binary_integer;

Tom Kyte

Followup  

April 14, 2003 - 7:49 am UTC

it is currently limited at 2000.

Related to original question

June 17, 2003 - 11:29 am UTC

Reviewer: sam from NY, USA

Tom can we do 'for update' in dynamic cursors??
I am trying to do something like this


TYPE reference_cur IS REF CURSOR;
c1 reference_cur;
open c1 for 'select cust_num from ' || table_name || ' for update';
-- table_name is passed as parameter into procedure
loop
fetch c1 into custnum;
exit when c1%notfound;

some calculations here

execute immediate 'update ' || table_name || ' set
deleted_ind=' || '''N''' || ' where current of c1';

end loop;


What I want to do is use ' for update / current of '
clause in ref cursor.

Thanks in advance





Tom Kyte

Followup  

June 17, 2003 - 12:52 pm UTC

select out the rowid and just 'update .... where rowid = :x' using ROWID;

and make sure to use binds everywhere....

and try to avoid dynamic sql unless you totally 100% have no other way to do it.

dynamic sql in pl*sql in V8

November 10, 2003 - 4:30 pm UTC

Reviewer: Reader from MA

Hi Tom:

Your solution to handling a cursor with variable number of columns is great.

Searched a lot but did not find answer

April 05, 2004 - 3:10 pm UTC

Reviewer: sonali from waltham, ma

I am using Oracle 9i
I have a table called work.

I want to dynamically select the columns from the same table depending on work_level value..

In other words I want to select work_par2 column if work_level =2 and work_par3 column if work_level is 3 so on til work_par9 column the work_level is 9.

We usually do DECODE or CASE to do this...
But it becomes very expensive when its in cursor or subquery etc.

Declare cursor main_cursor
IS
Select AuthAttr_ID, to_date( to_char(AuthAttr_Start_Date, 'YYYY-MM-DD "00:00:00.000"'), 'YYYY-MM-DD "00:00:00.000"')
from Authattrib
where AuthAttr_Work_ID IN (
Select Work_ID From Work
Where DECODE(inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
8, Work_par8, 9, Work_Par9) = inProjectID
);

I came up with this type of dynamic sql, but don't know which is more efficient and how to do this better...

I set inSAPPENT03 value before hand in this query -
execute immediate' Update mwebwork
set Work_Baseline_Finish = Work_Plan_Finish
where Work_Par'||inSAPPENT03 || '='|| inWorkId;


Tom Kyte

Followup  

April 05, 2004 - 5:26 pm UTC

why are we comparing a select vs update?

and the update should be


... where work_par' || variable || ' = :x' using inWorkId;


at worst.

Sorry I did not phrase my question right

April 12, 2004 - 11:45 am UTC

Reviewer: sonali from waltham, ma

Sorry I did not phrase my question correctly
What I am trying to compare is use of dynamic sql vs DECODE or CASE in Subquery, or cursor or update statement.
The work table has work_id, work_level, work_par2, work_par3, work_par4.. work_par9 columns.
We want to select work_par2 column if work_level =2 and work_par3
column if work_level is 3 so on til work_par9 column the work_level is 9.

We usually do DECODE or CASE to do this...
But it becomes very expensive when its in CURSOR or SUBQUERY etc.


Following is the part of stored procedure -

This is what we do -
Declare cursor main_cursor
IS
Select AuthAttr_ID, authattr_date
from Authattrib
where AuthAttr_Work_ID IN (
Select Work_ID From Work
Where DECODE(inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
8, Work_par8, 9, Work_Par9) = inProjectID
);

Will this be better

execute immediate 'Declare cursor main_cursor
IS
Select AuthAttr_ID, authattr_date
from Authattrib
where AuthAttr_Work_ID IN (
Select Work_ID From Work
Where Work_Par' ||inwork_level|| = inProjectID
;

or this is better
If inwork_level = 2 then
Declare cursor main_cursor
IS
Select AuthAttr_ID, authattr_date
from Authattrib
where AuthAttr_Work_ID IN (
Select Work_ID From Work
Where Work_Par2 = inProjectID
);

Else if inwork_level = 3 then
Declare cursor main_cursor
IS
Select AuthAttr_ID, authattr_date
from Authattrib
where AuthAttr_Work_ID IN (
Select Work_ID From Work
Where Work_Par2 = inProjectID
);

and so on

Tom Kyte

Followup  

April 12, 2004 - 2:46 pm UTC

This:

        Select Work_ID From Work 
        Where DECODE(inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
            Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
            8, Work_par8, 9, Work_Par9) = inProjectID 


is the probable "slow" piece as it would be forced in all cases to full scan work to find the value.

So, another option is "OR".  Consider (assuming you have work_parN indexed for each N)


ops$tkyte@ORA9IR2> create table t ( work_id int,
  2  work_par2 int,
  3  work_par3 int,
  4  work_par4 int,
  5  work_par5 int,
  6  work_par6 int,
  7  work_par7 int,
  8  work_par8 int,
  9  work_par9 int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx2 on t(work_par2);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx3 on t(work_par3);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx4 on t(work_par4);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx5 on t(work_par5);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx6 on t(work_par6);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx7 on t(work_par7);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx8 on t(work_par8);
 
Index created.
 
ops$tkyte@ORA9IR2> create index t_idx9 on t(work_par9);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2 ( authattr_id int, authattr_date int, authattr_work_id int );
 
Table created.
 
ops$tkyte@ORA9IR2> create index t2_idx on t2( authattr_work_id );
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 100000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable inwork_level number
ops$tkyte@ORA9IR2> variable inprojectid number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> Select /*+ FIRST_ROWS */ Work_ID From t
  2   Where DECODE(:inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
  3                Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
  4                8, Work_par8, 9, Work_Par9) = :inProjectID
  5  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=1000 Bytes=100000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=1000 Bytes=100000)
 
<b>that has to full scan -- nothing will change that really -- not even a function based index (since you use a bind in the function itself)</b>

ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ work_id from t
  2   where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
  3      or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
  4      or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
  5      or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
  6      or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
  7      or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
  8      or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
  9      or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
 10  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=41 Card=7726 Bytes=772600)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=7726 Bytes=772600)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP OR
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1)
   6    3         BITMAP CONVERSION (FROM ROWIDS)
   7    6           INDEX (RANGE SCAN) OF 'T_IDX3' (NON-UNIQUE) (Cost=1)
   8    3         BITMAP CONVERSION (FROM ROWIDS)
   9    8           INDEX (RANGE SCAN) OF 'T_IDX4' (NON-UNIQUE) (Cost=1)
  10    3         BITMAP CONVERSION (FROM ROWIDS)
  11   10           INDEX (RANGE SCAN) OF 'T_IDX5' (NON-UNIQUE) (Cost=1)
  12    3         BITMAP CONVERSION (FROM ROWIDS)
  13   12           INDEX (RANGE SCAN) OF 'T_IDX6' (NON-UNIQUE) (Cost=1)
  14    3         BITMAP CONVERSION (FROM ROWIDS)
  15   14           INDEX (RANGE SCAN) OF 'T_IDX7' (NON-UNIQUE) (Cost=1)
  16    3         BITMAP CONVERSION (FROM ROWIDS)
  17   16           INDEX (RANGE SCAN) OF 'T_IDX8' (NON-UNIQUE) (Cost=1)
  18    3         BITMAP CONVERSION (FROM ROWIDS)
  19   18           INDEX (RANGE SCAN) OF 'T_IDX9' (NON-UNIQUE) (Cost=1)

<b>all index range scans and as we'll see in a moment -- it'll only range scan ONE of them for real!</b>


ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ *
  2    from t2
  3   where authattr_work_id in (
  4  Select Work_ID From t
  5   Where DECODE(:inwork_level, 2, Work_Par2 ,3, Work_Par3 ,4,
  6                Work_Par4,5,Work_Par5 ,6,Work_par6 ,7,Work_par7 ,
  7                8, Work_par8, 9, Work_Par9) = :inProjectID
  8  )
  9  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2028 Card=1000 Bytes=139000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=39)
   2    1     NESTED LOOPS (Cost=2028 Card=1000 Bytes=139000)
   3    2       SORT (UNIQUE)
   4    3         TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=1000 Bytes=100000)
   5    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
 
 
<b>so, that is probably what you are seeing now -- full scan, and then nested loops -- but you could be seeing:</b>

 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ *
  2    from t2
  3   where authattr_work_id in (
  4  select work_id from t
  5   where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
  6      or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
  7      or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
  8      or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
  9      or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
 10      or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
 11      or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
 12      or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
 13  )
 14  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=15616 Card=7726 Bytes=1073914)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=39)
   2    1     NESTED LOOPS (Cost=15616 Card=7726 Bytes=1073914)
   3    2       SORT (UNIQUE)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=41 Card=7726 Bytes=772600)
   5    4           BITMAP CONVERSION (TO ROWIDS)
   6    5             BITMAP OR
   7    6               BITMAP CONVERSION (FROM ROWIDS)
   8    7                 INDEX (RANGE SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=1)
   9    6               BITMAP CONVERSION (FROM ROWIDS)
  10    9                 INDEX (RANGE SCAN) OF 'T_IDX3' (NON-UNIQUE) (Cost=1)
  11    6               BITMAP CONVERSION (FROM ROWIDS)
  12   11                 INDEX (RANGE SCAN) OF 'T_IDX4' (NON-UNIQUE) (Cost=1)
  13    6               BITMAP CONVERSION (FROM ROWIDS)
  14   13                 INDEX (RANGE SCAN) OF 'T_IDX5' (NON-UNIQUE) (Cost=1)
  15    6               BITMAP CONVERSION (FROM ROWIDS)
  16   15                 INDEX (RANGE SCAN) OF 'T_IDX6' (NON-UNIQUE) (Cost=1)
  17    6               BITMAP CONVERSION (FROM ROWIDS)
  18   17                 INDEX (RANGE SCAN) OF 'T_IDX7' (NON-UNIQUE) (Cost=1)
  19    6               BITMAP CONVERSION (FROM ROWIDS)
  20   19                 INDEX (RANGE SCAN) OF 'T_IDX8' (NON-UNIQUE) (Cost=1)
  21    6               BITMAP CONVERSION (FROM ROWIDS)
  22   21                 INDEX (RANGE SCAN) OF 'T_IDX9' (NON-UNIQUE) (Cost=1)
  23    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off

<b>all indexes, if that makes sense.  Further, using SQL_TRACE, we find:</b>


select /*+ FIRST_ROWS */ *
  from t2 q1
 where authattr_work_id in (
select work_id from t
 where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
    or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
    or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
    or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
    or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
    or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
    or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
    or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
)
                                                                                                                                       
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0          4          0           1
                                                                                                                                       
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 115
                                                                                                                                       
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T2 (cr=4 r=0 w=0 time=186 us)
      3   NESTED LOOPS  (cr=3 r=0 w=0 time=168 us)
      1    SORT UNIQUE (cr=2 r=0 w=0 time=146 us)
      1     TABLE ACCESS BY INDEX ROWID T (cr=2 r=0 w=0 time=103 us)
      1      BITMAP CONVERSION TO ROWIDS (cr=1 r=0 w=0 time=89 us)
      1       BITMAP OR  (cr=1 r=0 w=0 time=85 us)<b>
      1        BITMAP CONVERSION FROM ROWIDS (cr=1 r=0 w=0 time=52 us)
      1         INDEX RANGE SCAN T_IDX2 (cr=1 r=0 w=0 time=35 us)(object id 36726)</b>
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN T_IDX3 (cr=0 r=0 w=0 time=0 us)(object id 36727)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN T_IDX4 (cr=0 r=0 w=0 time=1 us)(object id 36728)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
      0         INDEX RANGE SCAN T_IDX5 (cr=0 r=0 w=0 time=1 us)(object id 36729)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
      0         INDEX RANGE SCAN T_IDX6 (cr=0 r=0 w=0 time=1 us)(object id 36730)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN T_IDX7 (cr=0 r=0 w=0 time=0 us)(object id 36731)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN T_IDX8 (cr=0 r=0 w=0 time=0 us)(object id 36732)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN T_IDX9 (cr=0 r=0 w=0 time=1 us)(object id 36733)
      1    INDEX RANGE SCAN T2_IDX (cr=1 r=0 w=0 time=9 us)(object id 36735)
                                                                                                                                       


<b>only one index was actually even read on T, we can switch the index by switching the inputs:</b>

select /*+ FIRST_ROWS */ *
  from t2 q2
 where authattr_work_id in (
select work_id from t
 where (work_par2 = decode(:inwork_level,2,:inProjectId,null))
    or (work_par3 = decode(:inwork_level,3,:inProjectId,null))
    or (work_par4 = decode(:inwork_level,4,:inProjectId,null))
    or (work_par5 = decode(:inwork_level,5,:inProjectId,null))
    or (work_par6 = decode(:inwork_level,6,:inProjectId,null))
    or (work_par7 = decode(:inwork_level,7,:inProjectId,null))
    or (work_par8 = decode(:inwork_level,8,:inProjectId,null))
    or (work_par9 = decode(:inwork_level,9,:inProjectId,null))
)
                                                                                                                                       
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1
                                                                                                                                       
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 115
                                                                                                                                       
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID OBJ#(36734) (cr=4 r=0 w=0 time=180 us)
      3   NESTED LOOPS  (cr=3 r=0 w=0 time=163 us)
      1    SORT UNIQUE (cr=2 r=0 w=0 time=140 us)
      1     TABLE ACCESS BY INDEX ROWID OBJ#(36725) (cr=2 r=0 w=0 time=88 us)
      1      BITMAP CONVERSION TO ROWIDS (cr=1 r=0 w=0 time=73 us)
      1       BITMAP OR  (cr=1 r=0 w=0 time=68 us)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN OBJ#(36726) (cr=0 r=0 w=0 time=1 us)(object id 36726)<b>
      1        BITMAP CONVERSION FROM ROWIDS (cr=1 r=0 w=0 time=39 us)
      1         INDEX RANGE SCAN OBJ#(36727) (cr=1 r=0 w=0 time=31 us)(object id 36727)</b>
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
      0         INDEX RANGE SCAN OBJ#(36728) (cr=0 r=0 w=0 time=1 us)(object id 36728)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN OBJ#(36729) (cr=0 r=0 w=0 time=0 us)(object id 36729)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN OBJ#(36730) (cr=0 r=0 w=0 time=0 us)(object id 36730)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=2 us)
      0         INDEX RANGE SCAN OBJ#(36731) (cr=0 r=0 w=0 time=1 us)(object id 36731)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
      0         INDEX RANGE SCAN OBJ#(36732) (cr=0 r=0 w=0 time=1 us)(object id 36732)
      0        BITMAP CONVERSION FROM ROWIDS (cr=0 r=0 w=0 time=3 us)
      0         INDEX RANGE SCAN OBJ#(36733) (cr=0 r=0 w=0 time=1 us)(object id 36733)
      1    INDEX RANGE SCAN OBJ#(36735) (cr=1 r=0 w=0 time=9 us)(object id 36735)



<b>of course this assumes you are using the CBO as only it is smart enough to know this</b>

 

Bitmap conversions

April 12, 2004 - 3:21 pm UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

Tom,
In the execution plan in your example, why bitmap conversions are present even though there are no bitmap indexes on the table?
Thanks

Tom Kyte

Followup  

April 12, 2004 - 7:09 pm UTC

we've been able to dynamically bitmap on the fly for years.

it is saying "hey, we might have N indexes to 'or' together maybe", the most efficient way to do that is bitmaps (rather than a big concatenation of N indexes with a distinct at the end)"

Thanks that was great !

April 13, 2004 - 1:43 pm UTC

Reviewer: Sonali from Waltham, ma


Why is pl/sql block faster than your decode with or statement

May 26, 2004 - 10:54 am UTC

Reviewer: sonali from waltham, ma

I created following procedure to test the Bitmap index query you mentioned. I have indexed all the work_parN's for this test. 1st procedure is the way you suggested and the 2nd one is with IF- THEN-Else block. I ran these over and over and always got the same results as below. The block seem to be more faster than the single Update .. why ?

SQLWKS> create or replace procedure DECODEtest
2> ( p_work_id IN number, inProjLevel IN number )
3> as
4>
5> begin
6>
7> Update mwebwork
8> set WORK_TEXT_EXT1 = 'test'
9> where (
10> (work_par2 = decode(inProjLevel,2,p_work_id,0))
11> or (work_par3 = decode(inProjLevel,3,p_work_id,0))
12> or (work_par4 = decode(inProjLevel,4,p_work_id,0))
13> or (work_par5 = decode(inProjLevel,5,p_work_id,0))
14> or (work_par6 = decode(inProjLevel,6,p_work_id,0))
15> or (work_par7 = decode(inProjLevel,7,p_work_id,0))
16> or (work_par8 = decode(inProjLevel,8,p_work_id,0))
17> or (work_par9 = decode(inProjLevel,9,p_work_id,0))
18> ) ;
19> end;
20> /
Statement processed.
Parse 0.06 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.00 (Elapsed) 0.00 (CPU)
Total 0.06 0.00
SQLWKS> execute DECODEtest ( 67405, 6);
Statement processed.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 3.17 (Elapsed) 0.00 (CPU)
Total 3.17 0.00
SQLWKS>
SQLWKS>
SQLWKS> create or replace procedure DECODEtest
2> ( p_work_id IN number, inProjLevel IN number )
3> as
4>
5> begin
6> IF inprojlevel = 2
7> THEN
8>
9> Update mwebwork
10> set WORK_TEXT_EXT1 = 'test'
11> where work_par2 = p_work_id ;
12>
13>
14> ELSIF inprojlevel = 3
15> THEN
16>
17> Update mwebwork
18> set WORK_TEXT_EXT1 = 'test'
19> where work_par3 = p_work_id ;
20> ELSIF inprojlevel = 4
21> THEN
22> Update mwebwork
23> set WORK_TEXT_EXT1 = 'test'
24> where work_par4 = p_work_id ;
25> ELSIF inprojlevel = 5
26> THEN
27> Update mwebwork
28> set WORK_TEXT_EXT1 = 'test'
29> where work_par5 = p_work_id ;
30> ELSIF inprojlevel = 6
31> THEN
32> Update mwebwork
33> set WORK_TEXT_EXT1 = 'test'
34> where work_par6 = p_work_id ;
35> ELSIF inprojlevel = 7
36> THEN
37> Update mwebwork
38> set WORK_TEXT_EXT1 = 'test'
39> where work_par7 = p_work_id ;
40> ELSIF inprojlevel = 8
41> THEN
42> Update mwebwork
43> set WORK_TEXT_EXT1 = 'test'
44> where work_par8 = p_work_id ;
45> ELSIF inprojlevel = 9
46> THEN
47> Update mwebwork
48> set WORK_TEXT_EXT1 = 'test'
49> where work_par9 = p_work_id ;
50> END IF;
51> end;
52> /
Statement processed.
Parse 0.35 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.00 (Elapsed) 0.00 (CPU)
Total 0.35 0.00
SQLWKS> execute DECODEtest ( 67405, 6);
Statement processed.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 0.12 (Elapsed) 0.00 (CPU)
Total 0.13 0.00



Tom Kyte

Followup  

May 26, 2004 - 1:11 pm UTC

if you give me a full example and explain what we are looking at -- maybe I can comment.  but you seem to be comparing an update to a select I tuned?

wall clock = nothing to rely on
tkprof = source of all truth

no details on what data you used, you materially changed the query (used 0 instead of NULL),  no plans, etc.

When I run the test my way:


ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 10000, numblks => 1000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure DECODEtest
  2  ( p_work_id IN number, inProjLevel IN number )
  3  as
  4  begin
  5  Update
  6  (  select * from t
  7      where
  8          (
  9         (work_par2 = decode(inProjLevel,2,p_work_id,null))
 10      or (work_par3 = decode(inProjLevel,3,p_work_id,null))
 11      or (work_par4 = decode(inProjLevel,4,p_work_id,null))
 12      or (work_par5 = decode(inProjLevel,5,p_work_id,null))
 13      or (work_par6 = decode(inProjLevel,6,p_work_id,null))
 14      or (work_par7 = decode(inProjLevel,7,p_work_id,null))
 15      or (work_par8 = decode(inProjLevel,8,p_work_id,null))
 16      or (work_par9 = decode(inProjLevel,9,p_work_id,null))
 17      )
 18  )
 19  set WORK_TEXT_EXT1 = 'test';
 20  end;
 21  /
 
Procedure created.


I get the access plan I would expect, one that would be comparable to your 52 lines of code:

UPDATE ( SELECT * FROM T
WHERE
 ( (WORK_PAR2 = DECODE(:B2,2,:B1,NULL)) OR (WORK_PAR3 = DECODE(:B2,3,:B1,NULL)
  ) OR (WORK_PAR4 = DECODE(:B2,4,:B1,NULL)) OR (WORK_PAR5 = DECODE(:B2,5,:B1,
  NULL)) OR (WORK_PAR6 = DECODE(:B2,6,:B1,NULL)) OR (WORK_PAR7 = DECODE(:B2,7,
  :B1,NULL)) OR (WORK_PAR8 = DECODE(:B2,8,:B1,NULL)) OR (WORK_PAR9 =
  DECODE(:B2,9,:B1,NULL)) ) ) SET WORK_TEXT_EXT1 = 'test'
                                                                                                                                          
                                                                                                                                          
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.00          0          1          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          1          0           0
                                                                                                                                          
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 143     (recursive depth: 1)
                                                                                                                                          
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  (cr=1 r=0 w=0 time=63 us)
      0   CONCATENATION  (cr=1 r=0 w=0 time=59 us)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=4 us)
      0     INDEX RANGE SCAN T_IDX9 (cr=0 r=0 w=0 time=1 us)(object id 45324)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=2 us)
      0     INDEX RANGE SCAN T_IDX8 (cr=0 r=0 w=0 time=1 us)(object id 45323)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=2 us)
      0     INDEX RANGE SCAN T_IDX7 (cr=0 r=0 w=0 time=0 us)(object id 45322)
      0    TABLE ACCESS BY INDEX ROWID T (cr=1 r=0 w=0 time=21 us)
      0     INDEX RANGE SCAN T_IDX6 (cr=1 r=0 w=0 time=19 us)(object id 45321)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=3 us)
      0     INDEX RANGE SCAN T_IDX5 (cr=0 r=0 w=0 time=1 us)(object id 45320)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=2 us)
      0     INDEX RANGE SCAN T_IDX4 (cr=0 r=0 w=0 time=1 us)(object id 45319)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=2 us)
      0     INDEX RANGE SCAN T_IDX3 (cr=0 r=0 w=0 time=1 us)(object id 45318)
      0    TABLE ACCESS BY INDEX ROWID T (cr=0 r=0 w=0 time=2 us)
      0     INDEX RANGE SCAN T_IDX2 (cr=0 r=0 w=0 time=1 us)(object id 45317)



 

Another solution

June 03, 2004 - 3:39 pm UTC

Reviewer: David from Guelph

Here's the approach that I've taken to dynamically create a sql statement to retrieve data from our financial reporting system (we had similiar requirements to sonali's example). Bind variables are used to assign values as well as column field names to the sql_stmt variable as shown below:


PROCEDURE MAIN_PROC
(month_in VARCHAR2,
fund_in VARCHAR2,
unit_in VARCHAR2 ,
object_in VARCHAR2,
project_in VARCHAR2 ,
group_in VARCHAR2 ,
balance_cur IN OUT balance_refcur)
IS

v_month DATE := TO_DATE(month_in,'YYYY-MM-DD');
v_unit VARCHAR2(20);
v_unit_value VARCHAR2(70);
v_unit_label VARCHAR2(70); . .

sql_stmt VARCHAR2(10000);

BEGIN

IF unit_in IS NULL
THEN
v_unit := ''''||'ALL'||'''';
. . .

ELSE
-- The HierarchyParser procedure takes the input paramater (that consists of hierarchy level || hierarchy value)
-- and returns the field that corresponds with the next level of the unit hierarchy. This will be used when constructing
-- the dynamic sql predicate
SELECT HierarchyParser(unit_in,'U','field') INTO v_unit FROM dual;

. . .
END IF;

SELECT HierarchyParser(unit_in,'U','value') INTO v_unit_value FROM dual;
SELECT HierarchyParser(unit_in,'U','label') INTO v_unit_label FROM dual;




sql_stmt := 'SELECT b.month,
hf.CHILD fund, '
||v_unit_label||v_unit||'
SUM(ROUND(b.budget_base ,0)) budget_base
FROM fr.FR_ACCOUNT a,
fr.FR_EXPANDED_FUND ef,
fr.FR_HIERARCHY_FUND hf,
fr.FR_BALANCE b,
fr.FR_EXPANDED_UNIT eu,
WHERE a.fund_seg=ef.fund
AND (ef.fund =hf.CHILD OR ef.p1=hf.CHILD OR ef.p2=hf.CHILD)
AND a.code_combination_id=b.code_combination_id
AND eu.unit=a.unit_seg
AND hf.CHILD = :1
AND b.MONTH = :2
AND '||v_unit ||'= :3
GROUP BY b.month,
hf.CHILD fund, '
||v_unit_label||v_unit';


OPEN balance_cur FOR sql_stmt USING fund_in, v_month, v_unit_value;

END;


Bind variables - is a package variable okay?

July 19, 2004 - 6:56 am UTC

Reviewer: Dave Hemming from West Midlands, UK

We have a package PTS_PDA that contains a procedure with a dynamic query, since we have one view per day that aggregates all the information we need. The columns will always be the same.

So the view name will be the same for around 24 hours, and we'd really like the cursor to not be parsed any more often than need be.

Code looks like this:
********

-- 2. Set up record retrieval.
v_query_string := 'select * from v_dayplan_pjny_'||
to_char( p_journey_date, 'RRRRMMDD' )||
' where pj_journey_number = pts_pda.pkg_journey_number'||
' order by pj_pickup_time, pj_drop_time';

OPEN v_cursor FOR v_query_string;

-- 3. Loop through patients in journey.
LOOP
fetch v_cursor INTO v_pjny_rec;

EXIT WHEN
v_cursor%NOTFOUND;

<process each patient>
END LOOP;

***************

My question is about journey number - I'm putting it in a package spec variable and referencing that rather than stringing the actual value into the query. Will that ensure cursor reuse? cursor_sharing is set to EXACT.



Tom Kyte

Followup  

July 19, 2004 - 7:59 am UTC

in dynamic sql -- pts_pda.pkg_journey_number will not be "in scope". that query will not parse at all.

You would need to use a bind variable:

l_stmt := 'select ..... from ...' || to_char( .... ) ||
' where column = :x order by ....';

open c for l_stmt using pts_pda.pkg_journey_number;

loop
fetch c into ....;



That's it!

July 20, 2004 - 6:21 am UTC

Reviewer: Dave Hemming from West Midlands, UK

I'd forgotten the scope issue. Thanks!

Any Example you may give?

August 20, 2004 - 12:39 am UTC

Reviewer: Annie from Dominican Republic

Dear Sir,
I came to know in Oracle 9i,the Dbms_sql Package has the
Table type dbms_sql.varchar2s.I would like to pass it as an
argument to a procedure like
 SQL> Create or replace procedure proc(x in out dbms_sql.
      varchar2s) as
      ...
      -- I would like to do some processing.
Do you have any example for this type of procedure?
Please do reply.
 

Tom Kyte

Followup  

August 20, 2004 - 10:46 am UTC

you just gave one -- perfect.

seriously -- you just gave *the example*

Great help

February 16, 2005 - 5:45 am UTC

Reviewer: Ajit Kumar from BANGALORE, INDIA

Sir,
This was a great help for me. I also had a problem which i wantet to pose before you. In my SQL prompt after typing a long for eg. select command when i type ED command to edit the command the buffer does not open and my only last option is to close the window and reenter once again. I have loaded the sql plus from Developer 6i CD. I have been doing it but this is only happening to one of my PC's.

Tom Kyte

Followup  

February 16, 2005 - 8:18 am UTC

define _editor=editor_of_your_choice




Method 4 using CLOBS

February 18, 2005 - 5:30 am UTC

Reviewer: Dave Moss from Bristol, UK

Hi Tom,
Your code sample for implementing Method 4 using the DBMS_SQL.describe_columns etc. has been extremely useful to me - many thanks.

I now need to throw another clog in the works by asking if there's any way of doing exactly what you did in your first reply - but using CLOB columns. It seems that DBMS_SQL is not able to support LOBs and yet I need to dynamically select an unknown number of columns from an unknown table where those columns could potentially be CLOBS.

Thanks for your help

Dave

Tom Kyte

Followup  

February 18, 2005 - 8:50 am UTC

why do you think dbms_sql does not do clobs?

I'm fairly new to dynamic SQL but...

February 18, 2005 - 9:09 am UTC

Reviewer: Dave Moss from Bristol, UK.

...I'd assumed that the DBMS_SQL package didn't offer CLOB support because it was created for Oracle versions 6 and 7 and hadn't been updated to include support for all the types in version 8 onwards (I was basing this on the fact that Native Dynamic SQL does support these types).
I suspect I am wrong and await your scathing response with curiosity!

Tom Kyte

Followup  

February 18, 2005 - 9:28 am UTC

no scathing response, just a pointer to the documentation:

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_sql2.htm#998541 <code>

Am I missing something Where's the call to dbms_sql.Bind_variable?

January 17, 2006 - 11:12 am UTC

Reviewer: ali king from uk

way at the top Tom says:

See how I use a describe table to find the number of columns and then simply
loop to bind all of them to a varchar2(4000).

but I can only see a loop for define_column

I can see how to do it using bind_variable, but just wanted to make sure I hadn't missed some fundamental step?

but - very useful all the same

Tom Kyte

Followup  

January 17, 2006 - 4:09 pm UTC

bind/define - they even rhyme.

Technically, I am "defining" what I want to output to - and then using column_value to retrieve the actual output value.

Technically "binds" are inputs, "defines" are outputs - but I generalized....

alter session set nls_xxx and bind vars

February 07, 2006 - 11:31 am UTC

Reviewer: ali from london, uk

thanks for the prompt response

just had a Question re the execute_immediate proc:

should I be able to pass the format string as a var and use:
execute immediate (p_sql) using my_format_string

where p_sql is for instance = 'alter session set nls_date_format = :format_1' and my_format_str = 'dd-mon-yy hh24:mi:ss'

as I will be setting and unsetting nls_date around each dyn sql call.

can't seem to get it to work, should I be able to and should I be worried about it?

cheers ali...



Tom Kyte

Followup  

February 08, 2006 - 1:16 am UTC

You cannot bind ALTER, CREATE, GRANT (eg: ddl/dcl statements)

ops$tkyte@ORA10GR2> create or replace procedure p( p_format in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_nls( 'nls_date_format', '"'||p_format||'"' );
  5  end;
  6  /

Procedure created.

ops$tkyte@ORA10GR2> select sysdate from dual;

SYSDATE
---------
01-FEB-06

ops$tkyte@ORA10GR2> exec p( 'dd-month-yyyy' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select sysdate from dual;

SYSDATE
-----------------
01-february -2006

 

alter session set nls_xxx and bind vars

February 08, 2006 - 5:06 am UTC

Reviewer: ali from london uk

excellent and thanks again

Thanks!

May 07, 2007 - 7:34 am UTC

Reviewer: Can Eren from Istanbul, Turkey

Mr Tom,

I'm very very new to pl/sql and i spent my whole day searching a solution exactly like that. Thank you so much.
Tom Kyte

Followup  

May 08, 2007 - 10:50 am UTC

the database did not give an error saying the SGA was full.

There is not any such condition as "sga full"

processes do not really consume "SGA" (well, there is the UGA that can be stored in the large pool when using shared server)....

I presume you mean 'unable to allocate X bytes in the SHARED pool' - which is typically caused by "lack of bind variables" - meaning the developers have a nasty bug in their developed code.

DBMS_SESSION.SET_NLS does not work for NLS_NUMERIC_CHARACTERS

May 15, 2007 - 5:48 am UTC

Reviewer: SFT from Norway

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. ';
SELECT 1/2 FROM DUAL;
 1/2
----
  .5

BEGIN
 DBMS_SESSION.Set_NLS('NLS_NUMERIC_CHARACTERS', ''', ''');
END;
/
SELECT 1/2 FROM DUAL;
 1/2
----
  .5

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ', ';
SELECT 1/2 FROM DUAL;
 1/2
----
  ,5

select * from v$version;
BANNER
----------------------------------------
Oracle9i Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
............


Actually I bumped into this behaviour when trying to run
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS...'
but it looks like the problem is common for any technique you try to use from within PL/SQL.

Any ideas?


Tom Kyte

Followup  

May 15, 2007 - 4:48 pm UTC

le9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> select 1/2 from dual;

       1/2
----------
        .5

ops$tkyte%ORA9IR2> exec     DBMS_SESSION.Set_NLS('NLS_NUMERIC_CHARACTERS', ''', ''');

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select 1/2 from dual;

       1/2
----------
        ,5



I cannot reproduce the issue.

dynamic sql

January 23, 2009 - 11:38 am UTC

Reviewer: asl from tr

hi tom
I have a problem ,my code is following

BEGIN

fHandler := UTL_FILE.FOPEN(v_dir,v_text, v_mod);

dbms_sql.parse(l_cur, 'select * from sys.all_objects',dbms_sql.native);
dbms_sql.describe_columns( c => l_cur,
col_cnt => l_ncols,
desc_t => l_desc);


FOR data IN (SELECT * FROM sys.all_objects) LOOP

for i in 1..l_ncols loop
select concat(l_desc(i).col_name,'')
into input_string
from dual;


input_string := do_make_8s(input_string);
raw_input := UTL_RAW.CAST_TO_RAW(input_string);

raw_encrypted :=dbms_crypto.Encrypt(src => raw_input,
typ => DBMS_CRYPTO.DES_CBC_PKCS5,
key =>raw_key );

UTL_FILE.put_line(fHandler,utl_raw.cast_to_varchar2(raw_encrypted));

END LOOP;
end loop;

if I reach all_objects's row,I will encrypt this row but I cannot get this row. if and only if I can reach all_object's column name with this line =>l_desc(i).col_name

how can I get this all_objects's other rows ??
Tom Kyte

Followup  

January 23, 2009 - 1:12 pm UTC

first and foremost, encrypted data is binary data, you cannot - you CANNOT use a text API to write it, you MUST use put raw. Character set conversion will someday assure you of total and complete data corruption - loss of all data in your output file.

Second, I don't know what you mean? I could not semantically parse this bit:

...
if I reach all_objects's row,I will encrypt this row but I cannot get this row.
if and only if I can reach all_object's column name with this line
=>l_desc(i).col_name

how can I get this all_objects's other rows ??
.....

thanks

January 27, 2009 - 5:36 am UTC

Reviewer: as from ttr

thanks tom I solve the problem,again with your answer the address is::
http://asktom.oracle.com/pls/asktom/f?p=100:11:1199884361806051::::P11_QUESTION_ID:235814350980

Show sysdate+15 anf if that date if exist in Holiday table incraese day by one

January 24, 2011 - 3:32 am UTC

Reviewer: dineshkakani from india

Following is the SQL Statement To create and populate Holiday Table.
create table holiday (calendardate date);

SQL> desc holiday;
 Name   Null?    Type
 --------------------- -------- ----------------------------
 CALENDARDATE        NOT NULL DATE

insert into holiday values('10-JAN-2011');
insert into holiday values('15-JAN-2011');
insert into holiday values('16-JAN-2011');
insert into holiday values('20-JAN-2011');
insert into holiday values('25-JAN-2011');

SQL> select calendardate from holiday 

CALENDARDATE
-------------
10-JAN-2011
15-JAN-2011
16-JAN-2011
20-JAN-2011
25-JAN-2011

I want to Get sysdate+15 days and sholud not be entry in HOLIDAY Table if exist increment by one 
SUPPOSE SYSTEM DATE IS 31-DEC-2010. I NEED A QUERY THAT WOULD RETURN 17-JAN-2011 AFTER SKIPPING 15,16-JAN-2011 AS THEY ARE PRESENT IN THE HOLIDAY Table

Tom Kyte

Followup  

February 01, 2011 - 10:02 am UTC

assuming you never have a TON of holidays right next to each other - just over produce a little and then filter:

ops$tkyte%ORA11GR2> with dates
  2  as
  3  (select to_date('31-dec-2010','dd-mon-yyyy')+level-1 dt
  4     from dual
  5  connect by level <= 30
  6  )
  7  select *
  8    from (
  9  select *
 10    from dates
 11   where dt not in (select * from holiday)
 12   order by dt
 13         )
 14   where rownum <= 15
 15  /

DT
---------
31-DEC-10
01-JAN-11
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
11-JAN-11
12-JAN-11
13-JAN-11
14-JAN-11
17-JAN-11

15 rows selected.

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here