Thanks for the question, Cleo .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: July 16, 2013 - 1:54 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm trying out Dynamic SQL and I'm using the DBMS_SQL package to
do this but I am getting errors when executing the procedure.
Below is a simple version of the code and the errors given when
executing it. Could you please give me some guidance as to where
I am going wrong because I am having difficulty with it.

Thanks.

SQL> CREATE OR REPLACE PROCEDURE prTest (
2 p_tableName IN varchar2,
3 p_existingId IN number)
4 IS
5 v_cursorId integer;
6 v_id integer;
7 v_name varchar2(30);
8 v_selectString varchar2(1000);
9 v_dummy integer;
10
11 BEGIN
12 -- Open the cursor for processing.
13 v_cursorId :=DBMS_SQL.OPEN_CURSOR;
14
15 -- Construct the select string.
16 v_selectString := 'select id,name into v_id,v_name from '
17 || p_tableName || ' where id= (:id1)';
18
19 -- Parse the query.
20 DBMS_SQL.PARSE(v_cursorId, v_selectString, DBMS_SQL.V7);
21
22 -- Bind the input variables.
23 DBMS_SQL.BIND_VARIABLE(v_cursorId, ':id1', p_existingId);
24
25 -- Define the output variables.
26 DBMS_SQL.DEFINE_COLUMN(v_cursorId, 1, v_id);
27 DBMS_SQL.DEFINE_COLUMN(v_cursorId, 1, v_name, 30);
28
29 -- Execute the statement.
30 v_dummy := DBMS_SQL.EXECUTE(v_cursorId);
31
32 -- Fetch loop for the records returned.
33 LOOP
34 IF DBMS_SQL.FETCH_ROWS(v_cursorId)=0 THEN
35 EXIT;
36 END IF;
37
38 -- Retrieve the rows from the buffer into variables.
39 DBMS_SQL.COLUMN_VALUE(v_cursorId, 1, v_id);
40 DBMS_SQL.COLUMN_VALUE(v_cursorId, 2, v_name);
41
42 insert into test2 (id,name) values (v_id,v_name);
43
44 END LOOP;
45
46 DBMS_SQL.CLOSE_CURSOR(v_cursorId);
47 END;
48
49 /

Procedure created.

SQL> exec prTest('test',1);
BEGIN prTest('test',1); END;

*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "OXYGEN.PRTEST", line 20
ORA-06512: at line 1

SQL>

and Tom said...

You do not SELECT ... INTO ... in dynamic sql. YOu just select and bind the output columns. Here is a small example:

create or replace function get_rows( p_tname in varchar2 ) return number
as
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue number default NULL;
l_status integer;
begin
dbms_sql.parse( l_theCursor,
'select count(*) from ' || p_tname,
dbms_sql.native );

dbms_sql.define_column( l_theCursor, 1, l_columnValue );
l_status := dbms_sql.execute(l_theCursor);
if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
then
dbms_sql.column_value( l_theCursor, 1, l_columnValue );
end if;
dbms_sql.close_cursor( l_theCursor );
return l_columnValue;
end ;
/

it only gets one row but shows the point. You don't select the count(*) INTO anything -- you just select it.

Here is a larger, more generic routine. takes any sql and dumps it all to a flat file:

create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
return l_cnt;
end dump_csv;
/

create or replace procedure test_dump_csv
as
l_rows number;
begin
l_rows := dump_csv( 'select * from all_users where rownum < 25', ',', '/tmp', 'test.dat' );
end;
/

Rating

(39 ratings)

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

Comments

dbms_sql

atul, December 18, 2002 - 5:24 am UTC

Sir,

I want to run some alter system commands from normal account...

I found package written by JPL from his site

script is

The script:
rem
rem Script: flush.sql
rem Author: Jonathan Lewis
rem Dated: 25th April 1997
rem Purpose: Create packaged procedure to flush shared pool
rem
rem Notes: Script to be run by SYS or other user that has
rem received the ALTER SYSTEM privilege directly and
rem not through a role.
rem
create or replace package flush_pool as
procedure flush_pool;
end;
/

create or replace package body flush_pool as
procedure flush_pool is
flush_cursor integer;
m_junk integer;
begin
flush_cursor := dbms_sql.open_cursor;
dbms_sql.parse (flush_cursor,
'alter system flush shared_pool',
dbms_sql.v7
);
m_junk := dbms_sql.execute(flush_cursor);
dbms_sql.close_cursor(flush_cursor);
exception
when others then
if dbms_sql.is_open(flush_cursor) then
dbms_sql.close_cursor(flush_cursor);
end if;
end /* procedure */;
end /* package */;
/

create public synonym flush_pool for sys.flush_pool;
grant execute on flush_pool to public;

I ran this and it worked well...
Only thing i want is...
This only gives output like "procedure executed successfully"

But also i want to know the output of the command
"alter system flush shared_pool"...

Also by this can we shutdown oracle from normal user?how?

Thanks.
atul

December 18, 2002 - 11:07 am UTC

be careful with that axe eugene (obscure reference to a really old pink floyd song)...

I hope you know what you are doing with that one -- flushing the shared pool is NOT something you want people to be doing. Anyway....

there is no output from a flush shared pool -- it just either

o succeeds (procedure executed successfully)
o fails (ORA-01031 issuficient privs or something)

there isn't any output to take a gander at.

You cannot shutdown from "normal user" (and boy oh boy -- you don't want to). SYSDBA or SYSOPER accounts can shutdown.

Timeliness

Connor McDonald, December 19, 2002 - 3:40 am UTC

Atul,

You might want to take a look at the date on that script - its 1997. Flushing the shared pool might have been relevant back then when (pre8i) the shared pool lists could get excessively long...

This was resolved in 816, so as Tom says, its unlikely you'll need this anyway

hth
connor

error executing dump_csv

Anil, December 19, 2002 - 7:17 am UTC

Hi Tom
Version : Oracle 8.1.7.2 on Solaris 2.8

I get an error when I try to run this dump_csv function, I have changed your procedure to check if the file is opening or not. Please find
below the script.FYI , INIT.ORA UTL_FILE_DIR is set

create or replace function dump_csv1( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
is
l_output utl_file.file_type;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

if utl_file.is_open(l_output) then
utl_file.fclose( l_output );
return 1;
else
return 0;
end if;

exception
when others then
raise_application_error(-20001,sqlerrm);
end dump_csv1;

create table test(t number,tt number)

declare
begin
for i in 1..100
loop
insert into t values(i,i);
end loop;
end;

exec :x:= dump_csv1( 'select * from test', ',','/tmp', 'test.dat' )

create or replace function dump_csv1( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )
return number
is
l_output utl_file.file_type;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

if utl_file.is_open(l_output) then
utl_file.fclose( l_output );
return 1;
else
return 0;
end if;

exception
when others then
raise_application_error(-20001,sqlerrm);
end dump_csv1;

ERROR at line 1:
ORA-20001: USER-Defined exception
ORA-06512: at "TEST.DUMP_CSV1", line 20
ORA-06512: at line 1

Kindly give me a pointer on what could be the possible reasons for this error.

Thanks & Regards,
Anil

December 19, 2002 - 8:02 am UTC

your init.ora parameter is not set

or

it is set improperly (eg: not to /tmp)

or

the file test.dat exists and Oracle cannot open it for writing

suggestion: remove the exception handler so you can actually see what line is flinging the exception -- you don't know if it is the fopen, is_open or fclose right now.

error executing dump_csv

Anil, December 19, 2002 - 7:22 am UTC

Hi Tom:
Ignore the anonymous plsql block - I have changed it to table name test instead of t and ran it. THe table is created and the dump_csv1 gives this error.

Thanks
Anil

error executing dump_csv

Anil, December 19, 2002 - 8:22 am UTC

Hi tom:
Thanks tom, tmp is the sub directory of the UTL_FILE_DIR, I was under the impression that it can write to the sub directory.

Thanks & Regards,
Anil

December 19, 2002 - 9:39 am UTC

no, only explicit paths. no subdirectory traversal.

I am concerned that you put "/" in there -- you don't want to have people writing to "/"

Can you please guide me on how can I re-write this function using DBMS_SQL

Steve, September 18, 2003 - 12:02 pm UTC

FUNCTION view_cmc_products (
p_product IN VARCHAR2,
p_dosage IN VARCHAR2,
p_units IN VARCHAR2,
p_uom IN VARCHAR2,
p_code IN VARCHAR2,
p_comments IN VARCHAR2,
p_created_by IN VARCHAR2,
p_create_date_from IN VARCHAR2,
p_create_date_to IN VARCHAR2,
p_category IN VARCHAR2,
P_UPDATE IN VARCHAR2,
p_order IN VARCHAR2,
P_DESC IN VARCHAR2
)
--return varchar2
RETURN cmc_products_cursortype
IS
v_log cmc_products_cursortype;
str VARCHAR2 (3000);

BEGIN
/********************************************************************
RETURNED FIELDS, PASS BACK THE SAME NUMBER TO ORDER BY THAT FIELD
1 --ID
2 --PRODUCT
3 --DOSAGE
4 --UNITS
5 --UOM
6 --CODE
7 --CREATED BY
8 --DATE CREATED
9 --COMMENTS
10--REASON_FOR_CHANGE
11--CATEGORY
12--UPDATE STATUS

******************************************************************/
str :=
'SELECT /*+ RULE*/ ID,
CMC_PRODUCTS_PKG.GET_COMPONENT_NAME(COMPONENT_FK_ID) PRODUCT,
DOSAGE_PKG.GET_DOSAGE_DESC(DOSAGE_FK_ID) DOSAGE,
UNITS ,
UOM_PKG.GET_UOM(UOM_FK_ID) UOM,
NVL(CODE,'' '') CODE ,
COLUMNS_JAVA_PKG.get_chemist_name_by_usid(CREATED_BY) CREATED_BY,
TO_CHAR(DATE_CREATED,''DD-MON-YYYY'') DATE_CREATED,
NVL(COMMENTS,'' '') COMMENTS ,
NVL(REASON_FOR_CHANGE,'' '') REASON_FOR_CHANGE ,
CATEGORY_PKG.get_category_desc(CATEGORY_FK_ID) CATEGORY,
UPDATED UPDATE_STATUS
FROM CMC_PRODUCTS
WHERE RECORD_STATUS<>''HISTORY''';

IF P_UPDATE<>' ' THEN
STR:=STR||' AND UPPER(CMC_PRODUCTS_PKG.CHECK_FOR_UPDATE(ID)) LIKE UPPER('''||P_UPDATE||''')';
END IF;
IF P_CREATE_DATE_FROM<>' ' THEN

STR:=STR||' AND TO_CHAR(DATE_CREATED,''DD-MON-YYYY'')>='''||P_CREATE_DATE_FROM||'''';

END IF;
IF P_CREATE_DATE_TO<>' ' THEN
STR:=STR||' AND TO_CHAR(DATE_CREATED,''DD-MON-YYYY'')<='''||P_CREATE_DATE_TO||'''';
END IF;

IF p_product <> ' '
THEN
str :=
str
|| ' AND UPPER(CMC_PRODUCTS_PKG.GET_COMPONENT_NAME(COMPONENT_FK_ID)) LIKE UPPER(''%'
|| p_product
|| '%'')';
END IF;

IF p_dosage <> ' '
THEN
str :=
str
|| ' AND UPPER(DOSAGE_PKG.GET_DOSAGE_DESC(DOSAGE_FK_ID)) LIKE UPPER(''%'
|| p_dosage
|| '%'')';
END IF;

IF p_units <> ' '
THEN
str :=
str || ' AND UPPER(UNITS) LIKE UPPER(''%' || p_units
|| '%'')';
END IF;

IF p_uom <> ' '
THEN
str :=
str
|| ' AND UPPER(UOM_PKG.GET_UOM(UOM_FK_ID)) LIKE UPPER(''%'
|| p_uom
|| '%'')';
END IF;

IF p_code <> ' '
THEN
str :=
str || ' AND UPPER(CODE) LIKE UPPER(''%' || p_code
|| '%'')';
END IF;

IF p_comments <> ' '
THEN
str :=
str
|| ' AND UPPER(COMMENTS) LIKE UPPER(''%'
|| p_comments
|| '%'')';
END IF;

IF p_created_by <> ' '
THEN
str :=
str
|| ' AND UPPER( COLUMNS_JAVA_PKG.get_chemist_name_by_usid(CREATED_BY)) LIKE UPPER(''%'
|| p_created_by
|| '%'')';
END IF;

IF p_category <> ' '
THEN
str :=
str
|| ' AND UPPER(CATEGORY_PKG.get_category_desc(CATEGORY_FK_ID)) LIKE UPPER(''%'
|| p_category
|| '%'')';
END IF;

IF p_order <> ' '
THEN
str := str || ' ORDER BY UPPER(TRIM(' || p_order || ')) '||P_DESC||'';
END IF;
--return str;
OPEN v_log FOR str;
RETURN v_log;
CLOSE v_log;

END view_cmc_products;

September 18, 2003 - 12:27 pm UTC

you cannot use dbms_sql to open a ref cursor.

anyway, you want to use binds (i assume that is why you asked about dbms_sql) so...

here is how to do that:

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

you do understand that last line of code -- close v_log -- never actually happens right? it is never executed (good thing -- else you would never get your data!!!)

delete that line of code, it'll only confuse someone.

(all of those function calls to plsql in the predicate are pretty scary -- seems like you could just use SQL probably - be a big increase in performance!)

THANKS

A reader, September 18, 2003 - 1:26 pm UTC

THE REASON I WANTED TO USE DBMS_SQL IS BECAUSE YOU STATED IN YOUR NEW BOOK THAT IF I AM GOING TO USE THE CODE ALL THE TIME
I SHOULD USE DBMS_SQL INSTEAD OF EXECUTE IMMEDIATE BECAUSE THE PERFORMANCE WILL FASTER. ANYWAY, I JUST READ THE LINK AND I WAS WONDERING IF YOU HAVE SOMETHING LIKE THIS USING DBMS_SQL.

THANKS

September 18, 2003 - 2:43 pm UTC

but you are dynamically building a query based on inputs (and you caps lock key is stuck?)

you cannot use dbms_sql here. the query changes from execution to execution.

but -- i also say you should use bind variables (over and over i say that). that -- that NEEDS (as in MUST) be fixed....

i'd lose those function calls as much as possible as well.

bravo!!!!!

A reader, September 18, 2003 - 2:52 pm UTC

Please guide me to enlighten this problem

DacHo, June 15, 2004 - 1:54 pm UTC

I've created 2 procedures and a function to search & replace the accent character of every text field in our DB. But unfortunately, It's always given me the error "ORA-01001: invalid cursor" even I've increased the max cursor in the ORACLE 8.0.6 setup file whenever I tried to run the process.

***The main procedure:
CREATE OR REPLACE PROCEDURE char_replace is
CURSOR Cur_info IS
select table_name, column_name
from user_tab_columns
where (column_name like '%_NAME%' or column_name like '%_ADD%' or
column_name like '%DESC%' or column_name like '%TEXT%')
and data_length > 14 and data_type in ('CHAR','VARCHAR2')
and substr(table_name,1,2) not in ('A0', 'RF', 'V0', 'VT', 'QU', 'CN', 'V1', 'UL', 'UD', 'UP',
'TR', 'AI', 'DM', 'EM', 'MS', 'OP', 'ME', 'GR', 'LO', 'BA', 'CH', 'CH', 'PO', 'T1', 'AC',
'T2', 'HS', 'OU', 'PR', 'SE', 'ST', 'RO', 'AF', 'NE', 'AL', 'ID', 'E_', 'C_', 'e_', 'MI',
'fi', 'TA', 'GA')
and table_name not in
(select table_name from user_tab_columns
where (column_name like '%_NAME%' or column_name like '%_ADD%' or
column_name like '%DESC%' or column_name like '%TEXT%')
and data_length > 14 and data_type in ('CHAR','VARCHAR2')
and substr(table_name,6,2) in ('UP', 'HO', 'PD', 'OY', 'HS')
or substr(table_name,10,2) in ('_S', 'OL', 'LD', 'NU', 'DE'));

BEGIN
dbms_output.put_line('Starting time: ' || to_char(sysdate, 'hh:mi:ss'));

FOR c_rec IN Cur_info LOOP
srch_str(c_rec.table_name,c_rec.column_name);
End loop;

dbms_output.put_line('Ending time: ' || to_char(sysdate, 'hh:mi:ss'));
END char_replace;
/

*** Second procedure
CREATE OR REPLACE PROCEDURE srch_str (tbl_name varchar2,fld_name varchar2) is
TYPE cur_type IS REF CURSOR;
c_type cur_type;
pos integer := 1;
len integer;
cid integer;
result integer;
letter char(1);
id_row varchar2(18);
query_str varchar2(1000);
sql_stmt varchar2(1000);
string varchar2(300);
string2 varchar2(300);
BEGIN
String := ''; ID_row := '';
query_str := 'Select rowid, '||fld_name|| ' from '||tbl_name;
Open c_type for query_str;
Loop
Fetch c_type into ID_row, String;
EXIT WHEN c_type%NOTFOUND;
len := length(rtrim(String));
While pos <= len Loop
letter := ''; letter := substr(string,pos,1);
If (letter = 'À') or (letter = 'Á') or (letter = 'Â') or (letter = 'Ã') or (letter = 'Å')
or (letter = 'Ä') or (letter = 'Æ') or (letter = 'Ç') or (letter = 'É') or (letter = 'É')
or (letter = 'Ê') or (letter = 'Ë') or (letter = 'Ì') or (letter = 'Í') or (letter = 'Î')
or (letter = 'Ï') or (letter = 'Ð') or (letter = 'Ñ') or (letter = 'Ò') or (letter = 'Ó')
or (letter = 'Ô') or (letter = 'Õ') or (letter = 'Ø') or (letter = 'Ö') or (letter = 'Ù')
or (letter = 'Ú') or (letter = 'Û') or (letter = 'Ü') or (letter = 'Ý') or (letter = 'ß')
or (letter = 'à') or (letter = 'á') or (letter = 'â') or (letter = 'ã') or (letter = 'å')
or (letter = 'ä') or (letter = 'æ') or (letter = 'ç') or (letter = 'è') or (letter = 'é')
or (letter = 'ê') or (letter = 'ï') or (letter = 'ë') or (letter = 'ì') or (letter = 'í')
or (letter = 'î') or (letter = 'ð') or (letter = 'ò') or (letter = 'ó') or (letter = 'ô')
or (letter = 'õ') or (letter = 'ø') or (letter = 'ñ') or (letter = 'ö') or (letter = 'ü')
or (letter = 'ý') or (letter = 'þ') or (letter = 'ù') or (letter = 'ú') or (letter = 'û') then
string2 := char_concat(string);
sql_stmt := 'Update '||rtrim(tbl_name)||' set '||rtrim(fld_name)||' = '''||upper(string2)||
''' where rowid = '''||ltrim(ID_row)||'';
cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid,sql_stmt,dbms_sql.native);
result := DBMS_SQL.EXECUTE(cid);
end if;
pos := pos + 1;
End loop;
DBMS_SQL.CLOSE_CURSOR(cid);
End Loop;
CLOSE c_type;

EXCEPTION
WHEN no_data_found THEN
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
IF DBMS_SQL.IS_OPEN(cid) then
DBMS_SQL.CLOSE_CURSOR(cid);
END IF;

END srch_str;
/

**** the function
CREATE OR REPLACE FUNCTION char_concat(pVar IN VARCHAR2) RETURN VARCHAR2 IS
cReturnString varchar2(255);
letter varchar2(2);
pos integer;
len integer;
BEGIN
dbms_output.put_line('Begin the <<char_concat>> FUNCTION');
len := length(rtrim(pVar)); pos := 1;
While pos <= len Loop
letter := substr(pVar,pos,1);
If (rtrim(letter) = 'À') or (rtrim(letter) = 'Á') or (rtrim(letter) = 'Â') or (rtrim(letter) = 'Ã')
or (rtrim(letter) = 'Å') then letter := 'A';
elsif (rtrim(letter) = 'Ä') or (rtrim(letter) = 'Æ') then letter := 'AE';
elsif rtrim(letter) = 'Ç' then letter := 'C';
elsif (rtrim(letter) = 'É') or (rtrim(letter) = 'É') or (rtrim(letter) = 'Ê') then letter := 'E';
elsif rtrim(letter) = 'Ë' then letter := 'EE';
elsif (rtrim(letter) = 'Ì') or (rtrim(letter) = 'Í') or (rtrim(letter) = 'Î') then letter := 'I';
elsif rtrim(letter) = 'Ï' then letter := 'IE';
elsif rtrim(letter) = 'Ð' then letter := 'D';
elsif rtrim(letter) = 'Ñ' then letter := 'N';
elsif (rtrim(letter) = 'Ò') or (rtrim(letter) = 'Ó') or (rtrim(letter) = 'Ô') or (rtrim(letter) = 'Õ')
or (rtrim(letter) = 'Ø') then letter := 'O';
elsif rtrim(letter) = 'Ö' then letter := 'OE';
elsif (rtrim(letter) = 'Ù') or (rtrim(letter) = 'Ú') or (rtrim(letter) = 'Û') then letter := 'U';
elsif rtrim(letter) = 'Ü' then letter := 'UE';
elsif rtrim(letter) = 'Ý' then letter := 'Y';
elsif rtrim(letter) = 'ß' then letter := 'ss';
elsif (rtrim(letter) = 'à') or (rtrim(letter) = 'á') or (rtrim(letter) = 'â') or (rtrim(letter) = 'ã')
or (rtrim(letter) = 'å') then letter := 'a';
elsif (rtrim(letter) = 'ä') or (rtrim(letter) = 'æ') then letter := 'ae';
elsif rtrim(letter) = 'ç' then letter := 'c';
elsif (rtrim(letter) = 'è') or (rtrim(letter) = 'é') or (rtrim(letter) = 'ê') or (rtrim(letter) = 'ï') then letter := 'e';
elsif rtrim(letter) = 'ë' then letter := 'ee';
elsif (rtrim(letter) = 'ì') or (rtrim(letter) = 'í') or (rtrim(letter) = 'î') then letter := 'i';
elsif (rtrim(letter) = 'ð') or (rtrim(letter) = 'ò') or (rtrim(letter) = 'ó') or (rtrim(letter) = 'ô') or (rtrim(letter) = 'õ')
or (rtrim(letter) = 'ø') then letter := 'o';
elsif rtrim(letter) = 'ñ' then letter := 'n';
elsif rtrim(letter) = 'ö' then letter := 'oe';
elsif rtrim(letter) = 'ü' then letter := 'ue';
elsif rtrim(letter) = 'ý' then letter := 'y';
elsif rtrim(letter) = 'þ' then letter := 'p';
elsif (rtrim(letter) = 'ù') or (rtrim(letter) = 'ú') or (rtrim(letter) = 'û') then letter := 'u';
elsif (rtrim(letter) = chr(9)) or (rtrim(letter) = chr(10)) or (rtrim(letter) = chr(11)) or (rtrim(letter) = chr(12))
or (rtrim(letter) = chr(13)) then letter := ' ';
end if;
cReturnString := cReturnString||rtrim(letter);
pos := pos + 1;
End loop;
return (cReturnString);
END char_concat;
/

>>> DBMS OUTPUT <<<
Starting time: 01:38:20 (line #1)
ORA-01001: invalid cursor
ORA-01001: invalid cursor
......
ORA-01001: invalid cursor
Ending time: 01:38:21 (line #40)

June 15, 2004 - 5:37 pm UTC

cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cid,sql_stmt,dbms_sql.native);
result := DBMS_SQL.EXECUTE(cid);
end if;
pos := pos + 1;
End loop;
DBMS_SQL.CLOSE_CURSOR(cid);

you opened a cursor inside the loop.
you closed the LAST cursor opened (leaving all others "open")

move the open OUTSIDE of the loop.

and no bind variables to be seen!!!! arg.

why not just execute:

update t set field_name = translate( field_name, 'bad chars', 'good chars' )
where translate( field_name, 'bad chars', 'good chars' ) <> field_field;

on each table (instead of running a query and then row by row updates)

Many Thanks

DacHo, June 17, 2004 - 10:44 am UTC

This is my first time to write the codes on Oracle DB, in my previous experience was used the SQL server or Sysbase.
The thing that I cannot use the function "Translate", because if there was an "umlaut" it has to be translate to 2 characters (i.e. change from ËLGAR to OELGAR).
Also, I know in Oracle 8.0.6 does not allow to use the "execute immediate", so that's why I use DBMS_SQL to execute the update table for each time I converted the accent character of a string. Could you help me out.
Thanks again.

June 17, 2004 - 1:39 pm UTC

re-read the answer, you have the dbms_sql open call inside the loop. you are leaking cursors like crazy.

I'd rather

replace(
replace(
replace( string, 'E', 'OE' ),
'A', 'AE' ),
......

and still just do an update. no code, not needed.

Marvellous

DacHo, June 18, 2004 - 10:59 am UTC

TOM, Thanks a lot...

Help

Raju, August 20, 2004 - 12:34 am UTC

Dear Tom,
This procedure is not working properly.
Could you please fix it?

SQL> set serverout on

SQL> create or replace procedure pp(dno in emp.deptno%type default 10)
  2  authid current_user
  3  as
  4  c pls_integer default dbms_sql.open_cursor;
  5  r pls_integer;
  6  v_empno emp.empno%type;
  7  v_ename emp.ename%type;
  8  v_sal emp.sal%type;
  9  begin
 10  dbms_sql.parse(c,'select empno,ename,sal from emp where deptno = :deptno',dbms_sql.native);
 11  dbms_sql.bind_variable(c,':deptno',dno);
 12  dbms_sql.define_column(c,1,v_empno,10);
 13  dbms_sql.define_column(c,2,v_ename,30);
 14  dbms_sql.define_column(c,3,v_sal,6);
 15  r := dbms_sql.execute(c);
 16  loop
 17   if dbms_sql.fetch_rows(c) = 0 then
 18    exit;
 19   end if;
 20  dbms_sql.column_value(c,1,v_empno);
 21  dbms_sql.column_value_char(c,2,v_ename);
 22  dbms_sql.column_value(c,3,v_sal);
 23    dbms_output.put_line(v_empno||v_ename||v_sal);
 24  end loop;
 25  dbms_sql.close_cursor(c);
 26  exception
 27  when others then
 28   if dbms_sql.is_open(c) then
 29    dbms_sql.close_cursor(c);
 30   end if;
 31  end;
 32  /

Procedure created.

SQL> exec pp

PL/SQL procedure successfully completed.


SQL> exec pp(20)

PL/SQL procedure successfully completed.


The problem is "It is not printing out Column Values"

August 20, 2004 - 10:46 am UTC

"could you fix it" - programmer for hire...

whatever

SQL> set serveroutput on

you need to enable dbms_output.

OK

Raju, August 20, 2004 - 11:33 am UTC

Dear Tom,
It has been enabled and you could see that in the first
line as
"sql>set serverout on"
Any help you may provide

August 21, 2004 - 10:41 am UTC

set serveroutput on

sorry.

Tom is Too Kind

Avid Reader, August 20, 2004 - 11:49 am UTC

Raju - Please re-read Tom's reply and LOOK AT YOUR OWN CODE.
The correct statement is SET SERVEROUTPUT ON, not SET SERVEROUT ON as you typed it. While we are at it, would you like us to design your application for you as well?

Kapil, August 09, 2005 - 11:42 am UTC

Hi Tom,

As you said in some previous thread "Followup:
no, only explicit paths. no subdirectory traversal. "

Then what is the other way(Database version 8.1.7) if I want to give permission to a specific user on directory say /u03/rpt and all its subdirectories, actually some program creates sudirectories under one particular directories and I want to read/write from there ?

Will "create directory" in 9i help in this context ?

Thanks
kaps

August 09, 2005 - 12:00 pm UTC

No, only explict paths, no subdirectory traversal with utl_file_dir or directory objects.

Kapil, August 09, 2005 - 12:25 pm UTC

so there is NO solution for the problem other than setting up utl_file_dir= * ???? which is truely a very very bad idea..

Thanks
kaps

August 09, 2005 - 12:37 pm UTC

No, you can use directory objects.

Person needs access to "/foo/bar/new"

directory to "/foo/bar/new" does not exist.

person calls procedure owned by someone with the privelege to create directory.

that procedure takes the input "new" and creates a directory mapping for "/foo/bar/new"

Actually, you could (since you'll have to convert OS directories into directory names) use a single function:

function get_dir_name( p_str in varchar2 ) returns varchar2

that function would query the dictionry to translate p_str into a direct object. If no hit AND the leading edge of the OS directory is "safe" (the one you want), it would create a new name, create the directory and return that name.

There are hundreds of things you could do short of '*'

kapil, August 09, 2005 - 12:58 pm UTC

That is really good for directory objects in 9i.

For 8i any good solution like this?

Thanks
kaps

August 09, 2005 - 2:09 pm UTC

You can use a java stored procedure and dbms_java to grant access for the java stored procedure.

But, if you are doing new development (which you are), it should really be done with supported software. If you are planning some sort of application upgrade...

kapil, August 09, 2005 - 2:13 pm UTC

Thanks a lot.

Selecting variable no. of columns

VA, August 12, 2005 - 4:25 pm UTC

create table t
(c1 varchar2(4000),
c2 varchar2(4000),
..
c10 varchar2(4000));

Given a number N (between 1 and 10), how can I write a dynamic SQL to select columns c1..cN from the table above and store each row in a PL/SQL table which I can loop from 1..N and get values as l_tab(i)?

I tried using define_array and stuff but couldnt really figure out how to make it dynamic.

Thanks

August 13, 2005 - 9:49 am UTC

I don't get it.

are you saying "I would like to dynamically reference the N'th column?"

if so, use dbms_sql, the get column value API call lets you ask for the I'th column

anan, November 22, 2005 - 12:14 am UTC

I'd like to dynamically create a sql within a package function
and return the resultset to the caller.

Is there a way of getting a ref cursor from a dynamic sql statement
created with dbms_sql in Oracle 9i r2

November 22, 2005 - 8:19 am UTC

no, you do not use dbms_sql to create ref cursors, you use

open ref_cursor_variable for some_variable_containing_your_query;

to do that.

pass cursor address to a function

A reader, January 30, 2006 - 7:25 am UTC

Hi

I have a code like this

l_cursor integer default dbms_sql.open_cursor;
begin
dbms_sql.parse( l_cursor, l_query, dbms_sql.native );
proc_run(l_cursor);
end;

proc_run(l_cursor);
seems invalid because inside the procedure i run

dbms_sql.fetch_rows(p_cursor)

and it always says

ORA-01002: fetch out of sequence

Is my construct illegal?

January 30, 2006 - 9:02 am UTC

that would be fine, assuming proc_run is coded properly.


ops$tkyte@ORA10GR2> create or replace procedure dump2( l_theCursor in out number )
  2  is
  3      l_columnValue   varchar2(4000);
  4      l_status        integer;
  5      l_colCnt        number := 0;
  6      l_separator     varchar2(1);
  7      l_descTbl       dbms_sql.desc_tab;
  8  begin
  9      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 10      for i in 1 .. l_colCnt loop
 11          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 12      end loop;
 13      l_status := dbms_sql.execute(l_theCursor);
 14      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 15          l_separator := '';
 16          for i in 1 .. l_colCnt loop
 17              dbms_sql.column_value( l_theCursor, i, l_columnValue );
 18              dbms_output.put( l_separator || l_columnValue );
 19              l_separator := ',';
 20          end loop;
 21          dbms_output.new_line;
 22      end loop;
 23      dbms_sql.close_cursor(l_theCursor);
 24  end;
 25  /

Procedure created.

ops$tkyte@ORA10GR2> show err
No errors.
ops$tkyte@ORA10GR2> create or replace procedure dump1( p_tname in varchar2 )
  2  is
  3      l_theCursor     integer default dbms_sql.open_cursor;
  4      l_query         varchar2(1000)
  5                      default 'select * from ' || p_tname;
  6  begin
  7      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
  8          dump2( l_theCursor );
  9  end;
 10  /

Procedure created.

ops$tkyte@ORA10GR2> pause

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dump1( 'dual' );
X

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec dump1( 'scott.emp' );
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
7788,SCOTT,ANALYST,7566,09-DEC-82,20182.53,,20

PL/SQL procedure successfully completed.

I'm trying to run this procedure:

Robin, April 12, 2006 - 8:25 am UTC

create or replace function dump_csv( p_query in varchar2,
p_separator in varchar2 default ',',
p_dir in varchar2 ,
p_filename in varchar2 )

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

And I keep getting an invalid directory. I am assuming that p_dir is "directory" and I've tried passing c, c:, c:\

I know...duh....but its early and I have a deadline and I'm sorry for bugging you.

April 12, 2006 - 9:18 am UTC

database version is?
utl_file_dir is set to what?

I'm using 10g

Robin, April 12, 2006 - 3:07 pm UTC

and though I've tried to set UTL_FILE_DIR, but that doesn't appear to work.

April 12, 2006 - 7:37 pm UTC

REM don't want to write to c:\ that would be nasty...
create directory my_dir as 'c:\something\'
/

and then use the string 'MY_DIR' (in uppercase) as the directory name in the call to dump_csv

DBMS_SQL.PARSE version

Rachelle, May 24, 2006 - 10:36 pm UTC

Hi Tom,

Thanks for your very useful tips.

We have just upgraded from oracle v7 to oracle v817. Yes i know, we are totally out-dated but this is our step to migrate to higher oracle version & application version. Anyways, my question is: is there any issue if the code still refers to "dbms_sql.v7" in our dbms_sql.parse statement?
Example: dbms_sql.parse(iCursorId,sSQLText,dbms_sql.v7);

Would appreciate your response.
Thanks.

Rachelle

May 25, 2006 - 1:33 pm UTC

ouch, you upgrade from the really unsupported to the merely unsupported.

It will likely be OK, but I would encourage you to switch to "native" over time.

question on dbms_sql

chinns, July 24, 2006 - 5:55 am UTC

hai tom,

I'm using the DBMS_SQL package to transfer data from bill side to cath side database.
Table name :tb4000606@bill(monthly table)
Every month we will transfer data from bill side to cath side after billing is happened to customer.
To transfer we will user plsql script.

Table is not a hard coded one(only tr400 is hardcoded), it is dynamic and we will get current month and current year from the maintainance table.

Both selecting and inserting tables are dynamic.

I prepared the script but its performance is too slow.

Can you help me in this regard by giving any sample example with better performance?

Thanks in Advance....

July 24, 2006 - 10:17 am UTC

lots of red herring stuff about using dynamic sql.

and then "but it is slow".

Can we help? No, not really - not to solve your particular coding problem with your particular sql (dynamic or not - not really relevant).

You know about sql tracing and tkprof? It would be where you should start. Find the low hanging fruit in your process.

question on dbms_sql

chinnas, July 24, 2006 - 6:05 am UTC

Tb400yymm
table contains 250 columns
and 200000 records or it may contain more, records will come based on the transactions done by the credit cardholders.

handle no_data_found in dynamic sql

chandana, July 25, 2006 - 1:29 am UTC

hi tom,

i have written a sql as follows,
p_cursor_string := 'select ct.customer_id, ct.trx_number, ct.trx_date ' ||
'from custom_table ct ' ||
'where ct.matching_number = :b_current_matching_number ';

but the SQL must be such that, if it does not match with given matching number, it must return:
customer_id = -9999,
trx_number = null,
trx_date = null.
In case it matches to multiple customers, but the same trx numbers it must return customer_id = -7777. trx_number and trx_date will be ignored in this case.

The program which is calling this procedure does not expect it to return any errors. the procedure is as follows:

PROCEDURE MATCHING_RULE(p_matching_option IN VARCHAR2,
p_cursor_string OUT VARCHAR2) IS
BEGIN
p_cursor_string := 'select ct.customer_id, ct.trx_number, ct.trx_date ' ||
'from custom_table ct ' ||
'where ct.matching_number = :b_current_matching_number ';
RETURN;
END matching_rule;
Please help me out in this soon.

July 25, 2006 - 10:49 am UTC

why the heck are you using dynamic SQL????

do not use dynamic sql, use static sql.  

Anyway, you can use the fact that an aggregate without a group by always returns a single row.  We can count the 'hits' and return either the data we find or -9999/-7777 depending on the number of rows:

ops$tkyte%ORA10GR2> create table t
  2  ( customer_id number,
  3    trx_number  number,
  4    trx_date    date,
  5    matching_number number
  6  )
  7  /

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, 1, sysdate, 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 2, 1, sysdate, 2 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 3, 1, sysdate, 2 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x number
ops$tkyte%ORA10GR2> exec :x := 1;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select :x matching_number,
  2         decode( count(*), 1, max(customer_id), 0, -9999, -7777 ) customer_id,
  3         decode( count(*), 1, max(trx_number), null ) trx_number,
  4             decode( count(*), 1, max(trx_date) ) trx_date
  5    from t
  6   where matching_number = :x;

MATCHING_NUMBER CUSTOMER_ID TRX_NUMBER TRX_DATE
--------------- ----------- ---------- ---------
              1           1          1 25-JUL-06

ops$tkyte%ORA10GR2> exec :x := 2;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

MATCHING_NUMBER CUSTOMER_ID TRX_NUMBER TRX_DATE
--------------- ----------- ---------- ---------
              2       -7777

ops$tkyte%ORA10GR2> exec :x := 3;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

MATCHING_NUMBER CUSTOMER_ID TRX_NUMBER TRX_DATE
--------------- ----------- ---------- ---------
              3       -9999

Very Useful

chandana, July 26, 2006 - 12:17 am UTC

Hi Tom,

Thanks alot for your help.

We need dynamic sql beacuse, Matching_rule procedure will be called by another procedure and which in turn uses this cursor_string to fetch the values.

July 26, 2006 - 10:37 am UTC

since you are obviously in design/build mode - seriously (this is not a joke or sarcasm) reconsider your choices.

generic is "cool"
programmers love it

but - it can be nightmarish to implement, debug, maintain - especially the latter. You defeat many useful features like the dependency mechanism, reduce the efficiency of the database (you'll PARSE like mad), increase the complexity many orders of magnitude.

rarely does something truly good come from it.

reader

A reader, August 29, 2006 - 2:44 pm UTC

Is there a script or similar script in this web site that could show me how many blank rows for each column in a table

I like a script which when the table name is passed as a paramter shows for each column of this table, how many blank rows are there

August 29, 2006 - 4:44 pm UTC

define "blank row" first. I'm not familar with the concept.

reader

A reader, August 30, 2006 - 7:28 am UTC

Rows having null or blank as a value for a column

If the table has suppose 15 columns, and have 10,000 rows, I like to find if any column in the table has value null or ' ' in 10,000 rows (all blanks) or 8000 rows (very sparse). This will be used to redesign the schema

August 30, 2006 - 8:47 am UTC

wow, I failed to see how this will help in a design - seems to me you would KNOW for sure already if

a) a column permits nulls
b) or not

just because you know your data. The absence of a null today does not mean 'it is NOT NULL'. This seems "backwards" if you ask me.

reader

A reader, August 30, 2006 - 8:41 am UTC

continuation from previous posting .....

redesign schema and business rules

August 30, 2006 - 8:52 am UTC

does not compute...

You don't look at existing data to see what the rules are.

You state the rules, then you make data conform to rules.

reader

A reader, August 30, 2006 - 9:15 am UTC

There is an existing database schema which falls short of referential integrity and have duplicated rows. We are re-organizing the whole schema structure and re-modelling the schema using ERWin tool etc.

The existing schema has tables that have columns that are not used or very sparingly used which can be removed or replaced with another field which could encompass a broader domain of values

August 30, 2006 - 9:26 am UTC

and your knowledge of the data should be providing that.

blank rows does not in any way shape or form provide that.

You have to actually understand your data to model your data.

Your last paragraph scares me here, it almost sounds like you are proposing to hide many attributes in a single generic column. THAT would be something in need of a redesign. The goal is not "attribute minimization"

DBMS_SQL

Rome, August 20, 2007 - 8:48 am UTC

Hi Tom,

I went through the site but i am still not very clear about dbms_sql.

What i am trying to do is

i have dynamically generated query which returns some columns, e.g the query maybe 'select empdate from emp' or 'select emp_name, emp_date, sal from emp'.

Now i want to capture the values returned by these queries and possibly pass them to another procedure. Which will do some processing for the values.

I tried using dbms_sql but can't figure out how do i store the values, do i have to use define which will cast the value and store it in a variable or something ?

thanks

a) Do i hv to use define and set the value to integer or varchar,

August 22, 2007 - 10:30 am UTC

I am not sure how to answer this.

you have an API dbms_sql

it lets you describe the query (to find the number of columns)
it lets you access the i'th column of the query result set using this api - the column_value function

you use column_value to access the i'th column of the result set? Not sure what more can be said.

A reader, August 24, 2007 - 6:41 am UTC

where do you store the column_value ? in a variable or something, which means the variable or something needs to be declared, which means either we know the type of column (which is not the case) or it performs a conversion of some sort to store, date values in varchar etc...

August 24, 2007 - 3:08 pm UTC

you need to pass an OUT parameter, which is a variable of yours and we place the value in that variable - yes, sure.

since you don't know the type of the column, it pretty much seems plausible to fetch everything as a STRING doesn't it. You must be doing something very generic (like dumping a query to a file)

Re: question on dbms_sql

RG, August 24, 2007 - 5:01 pm UTC

You could join to user_tab_columns to determine the column type and then store the value in an appropriate variable.
In the example below I determine the primary key columns of a table (table name is passed to the stored procedure by <p_table_name>)

CREATE OR REPLACE procedure P_TEST
 (
   p_table_name varchar2
 , p_column_name varchar2
 )
authid definer is
 v_tx_script     CLOB   := null;
 v_sql_statement    CLOB  := null;
 v_sql_columns     varchar2(500):= null;
 v_column_name    CLOB   := null;
 v_return      LONG   := null;
 v_clob_values      CLOB        := null;
 v_clob_columns      CLOB        := null;
 v_comma      varchar2(2) := ', ';
begin
  for x in (
   select
    (
    decode(rn,1,'''','||' || '''' || ' AND ') || p_table_name || '.' || column_name
    ) as column_name_with_and
    ,column_name as column_name
    ,data_type as data_type
    ,rn as rn
    from
    (
     select
     ucc.column_name
     ,ucc.position
     ,utc.data_type
     ,row_number() over (partition by ucc.table_name order by ucc.position) as rn
     FROM
     user_cons_columns ucc
     ,user_constraints uc
     ,user_tab_columns utc
     WHERE
     ucc.table_name = p_table_name
     and ucc.table_name = uc.table_name
     and ucc.constraint_name = uc.constraint_name
     and uc.constraint_type = 'P'
     and ucc.table_name = utc.table_name
     and ucc.column_name = utc.column_name
     and uc.table_name = utc.table_name
    )
  )
  loop
   if (x.data_type = 'NUMBER') then
    v_sql_columns := v_sql_columns || x.column_name_with_and
      || ' = ' || ''''|| '||' || p_table_name || '.' ||x.column_name;

   elsif (x.data_type = 'DATE') then
    if (x.rn <> 1) then
     v_sql_columns := v_sql_columns || '||' || '''' || ' AND ';
    else
     v_sql_columns := v_sql_columns || '''';
    end if;
    v_sql_columns := v_sql_columns || p_table_name || '.' || x.column_name || ' = ' ||
       ' TO_DATE(\\' || '''' || '||' ||
       'TO_CHAR('||p_table_name || '.' ||x.column_name || ',''MM/DD/YYYY HH:MI:SS AM'')' ||
       '||' || ''''|| '\\, \\MM/DD/YYYY HH:MI:SS AM\\) '|| ''''
       ;

   else
    if (x.rn <> 1) then
     v_sql_columns := v_sql_columns || '||' || '''' || ' AND ';
    else
     v_sql_columns := v_sql_columns || '''';
    end if;
    v_sql_columns := v_sql_columns || p_table_name || '.' || x.column_name || ' = ' ||
       ' TO_CHAR(\\' || '''' || '||' ||
       p_table_name || '.' ||x.column_name || '||' || ''''|| '\\)TO_CHAR '|| ''''
       ;

   end if;
  end loop;


exception
when others then
 RAISE_APPLICATION_ERROR(-20009, ' SQL= ' || SUBSTR(SQLERRM,1, 100),TRUE);

end;
/

August 27, 2007 - 4:01 pm UTC

well, describe columns will do that to (in dbms_sql) but so what? you convert to......

a string

anyway. Unless you know that column 5 is a number at compile time, what advantage do you have by fetching column 5 into a number - since your (obviously) generic bit of code MUST CONVERT IT to a string ultimately anyway to do anything with it

DBMS_SQL question

Bob, July 17, 2009 - 9:33 pm UTC

Hi Tom,

Recently I have bene playing around with DBMS_SQL.PARSE and declaring types of VARCHAR2s. I discovered that you can compile code that's greater than 32k in bytes. Is it possible to permanently cache the parsed code in the database (i.e we know the code won't change) - we just need to execute it?

July 24, 2009 - 8:59 am UTC

shared sql is stored in the SGA, never on disk.

so no, you cannot persist a sql plan 'forever'

dbms_sql

anil, April 03, 2013 - 4:05 pm UTC

Hi tom, I'm getting following error while executing this routine. Oracle version 10G release2
ORA-01489: result of string concatenation is too long

declare
c number;
stmt CLOB;
id_var clob;
STATUS NUMBER;
SUBTYPE LONG IS VARCHAR2(32567);
STR2 varchar2(32000);
begin
c:=DBMS_SQL.OPEN_CURSOR;

FOR I IN 1..1500 LOOP
str2:=str2||I||'||'',''||';
END LOOP;
DBMS_OUTPUT.PUT_LINE('A'||length(str2));
STR2 :=STR2||'1';
stmt :='select '||STR2||' from dual where 1=:1';
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c,':1', 1);
DBMS_SQL.DEFINE_COLUMN(C, 1, id_var);
STATUS := DBMS_SQL.EXECUTE(C);
DBMS_OUTPUT.PUT_LINE('VAL IS '||ID_VAR || status);

IF DBMS_SQL.FETCH_ROWS(C)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(C, 1, id_var);
end if;
DBMS_OUTPUT.PUT_LINE('VAL IS '||DBMS_LOB.GETLENGTH(ID_VAR));
END;
/

April 22, 2013 - 1:55 pm UTC

you are building a string:

1 || ',' || 2 || ',' || 3 || ....... || 1500

that string will be longer than 4000 bytes.

the longest string sql supports in 11g and before is 4000 bytes - the maxlength of a varchar2.

what are you really trying to accomplish??

use of dbms_sql

Ranjan., July 05, 2013 - 7:28 am UTC

Hi Tom,

We know that we should use dbms_sql when we dont know the select list until run time.(which is not possible to fetch
in weak efcursor).

what I understood after seeing few examples on dbms_sql.
#########
By the help of dbms_sql we can access each field in the cursor by there position which is not posible (partial fetch or positional fetch in refcursor).

Please tell whether I am right or wrong.

########
I have written a procedure using dbms_sql . I have few doubts on it.
########

CREATE OR REPLACE PROCEDURE emp_dept AS
v_cursor integer;
field1 char(20);
field2 char(20);
field3 char(20);
field4 char(20);
field5 char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select * from emp,dept ', DBMS_SQL.NATIVE);

DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, field1, 20);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 2, field2, 20);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 3, field3, 20);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 4, field4, 20);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 10, field5, 20);

v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;

DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, field1);
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 2, field2);
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 3, field3);
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 4, field4);
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 10, field5);
DBMS_OUTPUT.PUT_LINE(field1||field2||field3||field4||field5);
DBMS_OUTPUT.PUT_LINE('#########################################');

end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/

output:
exec emp_dept;
#######################
369 SMITH CLERK 7902 ACCOUNTING
#########################################
7499 ALLEN SALESMAN 7698 ACCOUNTING
#########################################
7521 WARD SALESMAN 7698 ACCOUNTING
#########################################
7566 JONES MANAGER 7839 ACCOUNTING
#########################################
....
....

In above code what my doubt is the total number of fields in "select * from emp,dept " is eleven ,so how can i found it so that i would use for i in 1..that_count for defining and column_value for the cursor.

2. for i in 1..5 loop
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, i, field||i);
end loop;
Any trick for this to work (it failed as expected).
how to pass field||i as a variable.

Thanks as always.

July 16, 2013 - 1:54 pm UTC

yes, you can use dbms_sql to procedurally access the columns one by one at runtime without having to know how many there are, what their types are and so on at compile time.

look up the describe routines in dbms_sql, they tell you column numbers, names, and typed.

you cannot pass "field||i" as input - it is just wanting an variable so it can see the lengths and character sets.

here is an example using dbms_sql fully:

create or replace procedure dump_table_to_csv( p_tname in varchar2,
                                               p_dir   in varchar2,
                                               p_filename in varchar2 )
authid current_user  /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_query         varchar2(1000)
                    default 'select * from ' || p_tname;
    l_colCnt        number := 0;
    l_separator     varchar2(1);
    l_descTbl       dbms_sql.desc_tab;
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
    execute immediate
    'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        utl_file.put( l_output,
            l_separator || '"' || l_descTbl(i).col_name || '"' );
        dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
        l_separator := ',';
    end loop;
    utl_file.new_line( l_output );

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := ',';
        end loop;
        utl_file.new_line( l_output );
    end loop;
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );

    execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
    when others then
        execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
        raise;
end;
/
exec dump_table_to_csv( 'SCOTT.EMP', '/tmp', 'tkyte.emp' );
host cat /tmp/tkyte.emp

mind blowing explanation:) thank you

Ranjan, July 16, 2013 - 6:50 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

question on dbms_sql

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

dbms_sql

Timeliness

error executing dump_csv

error executing dump_csv

error executing dump_csv

Can you please guide me on how can I re-write this function using DBMS_SQL

THANKS

bravo!!!!!

Please guide me to enlighten this problem

Many Thanks

Marvellous

Help

OK

Tom is Too Kind

Selecting variable no. of columns

pass cursor address to a function

I'm trying to run this procedure:

I'm using 10g

DBMS_SQL.PARSE version

question on dbms_sql

question on dbms_sql

handle no_data_found in dynamic sql

Very Useful

reader

reader

reader

reader

DBMS_SQL

Re: question on dbms_sql

DBMS_SQL question

dbms_sql

use of dbms_sql

mind blowing explanation:) thank you

More to Explore

PL/SQL demos

PL/SQL docs