Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL
Dynamic SQL [message #41061] Wed, 20 November 2002 14:40 Go to next message
Rizwan Qazi
Messages: 135
Registered: August 2002
Senior Member
If I generate a dynamic SQL string in a PL/SQL block is there any limit on the size of the string that can be read by execute immediate?

Seems like the string is built up but upon execution I get the error

ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at "DW.CROSSTAB_PKG", line 190

Report message to a moderator

Re: Dynamic SQL [message #41062 is a reply to message #41061] Wed, 20 November 2002 14:50 Go to previous messageGo to next message
Rizwan Qazi
Messages: 135
Registered: August 2002
Senior Member
Here is the code incase you want to look at it

CREATE OR REPLACE package crosstab_pkg
is
type rc is ref cursor;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return NUMBER;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return NUMBER;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_value_column3 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return NUMBER;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_value_column3 in user_tab_columns.column_name%type,
dw_value_column4 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return NUMBER;
end;
/
CREATE OR REPLACE package body crosstab_pkg
is
function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return number is
v_crosstab rc;
v_range varchar2(30);
v_column_list1 varchar2(8000);
v_string varchar2(13000);
v_string1 varchar2(1000);
v_string2 varchar2(1000);
begin
open v_crosstab for
'select distinct ' || dw_range_column ||
' from ' || dw_table;

loop
fetch v_crosstab into v_range;
exit when v_crosstab%notfound;
v_column_list1 := v_column_list1 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column1 || ', null)) ' || dw_value_column1 || '_' || v_range;
end loop;
close v_crosstab;

BEGIN
v_string1 := 'DROP TABLE ' || dw_param1;
execute immediate v_string1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Table Does Not Exist');
END;

v_string2 := 'CREATE INDEX ' || dw_param2 || ' on ' || dw_param1 || ' (' || dw_key_column || ') TABLESPACE DW_IND';

v_string := 'CREATE TABLE ' || dw_param1 || ' AS SELECT /*+ INDEX(' || dw_table || ') */' || dw_key_column || ',' ||
substr(v_column_list1, 3) ||
' from ' || dw_table ||
' group by ' || dw_key_column;

execute immediate v_string;

execute immediate v_string2;

return 0;
end;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return number is
v_crosstab rc;
v_range varchar2(30);
v_column_list1 varchar2(8000);
v_column_list2 varchar2(8000);
v_string varchar2(21000);
v_string1 varchar2(1000);
v_string2 varchar2(1000);
begin
open v_crosstab for
'select distinct ' || dw_range_column ||
' from ' || dw_table;
DBMS_OUTPUT.PUT_LINE('Start');

loop
fetch v_crosstab into v_range;
exit when v_crosstab%notfound;
v_column_list1 := v_column_list1 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column1 || ', null)) ' || dw_value_column1 || '_' || v_range;
v_column_list2 := v_column_list2 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column2 || ', null)) ' || dw_value_column2 || '_' || v_range;
end loop;
close v_crosstab;

BEGIN
v_string1 := 'DROP TABLE ' || dw_param1;
execute immediate v_string1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Table Does Not Exist');
END;

v_string2 := 'CREATE INDEX ' || dw_param2 || ' on ' || dw_param1 || ' (' || dw_key_column || ') TABLESPACE DW_IND';

v_string := 'CREATE TABLE ' || dw_param1 || ' AS SELECT /*+ INDEX(' || dw_table || ') */' || dw_key_column || ',' ||
substr(v_column_list1, 3) || ',' || substr(v_column_list2, 3) ||
' from ' || dw_table ||
' group by ' || dw_key_column;

execute immediate v_string;

execute immediate v_string2;
return 0;
end;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_value_column3 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return number is
v_crosstab rc;
v_range varchar2(30);
v_column_list1 varchar2(8000);
v_column_list2 varchar2(8000);
v_column_list3 varchar2(8000);
v_string varchar2(29000);
v_string1 varchar2(1000);
v_string2 varchar2(1000);
begin
open v_crosstab for
'select distinct ' || dw_range_column ||
' from ' || dw_table;

loop
fetch v_crosstab into v_range;
exit when v_crosstab%notfound;
v_column_list1 := v_column_list1 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column1 || ', null)) ' || dw_value_column1 || '_' || v_range;
v_column_list2 := v_column_list2 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column2 || ', null)) ' || dw_value_column2 || '_' || v_range;
v_column_list3 := v_column_list3 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column3 || ', null)) ' || dw_value_column3 || '_' || v_range;
end loop;
close v_crosstab;

BEGIN
v_string1 := 'DROP TABLE ' || dw_param1;
execute immediate v_string1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Table Does Not Exist');
END;

v_string2 := 'CREATE INDEX ' || dw_param2 || ' on ' || dw_param1 || ' (' || dw_key_column || ') TABLESPACE DW_IND';

v_string := 'CREATE TABLE ' || dw_param1 || ' AS SELECT /*+ INDEX(' || dw_table || ') */' || dw_key_column || ',' ||
substr(v_column_list1, 3) || ',' || substr(v_column_list2, 3) || ',' || substr(v_column_list3, 3) ||
' from ' || dw_table ||
' group by ' || dw_key_column;

execute immediate v_string;

execute immediate v_string2;

return 0;
end;

function qry( dw_table in user_tables.table_name%type,
dw_key_column in user_tab_columns.column_name%type,
dw_range_column in user_tab_columns.column_name%type,
dw_value_column1 in user_tab_columns.column_name%type,
dw_value_column2 in user_tab_columns.column_name%type,
dw_value_column3 in user_tab_columns.column_name%type,
dw_value_column4 in user_tab_columns.column_name%type,
dw_param1 in VARCHAR2,
dw_param2 in VARCHAR2)
return number is
v_crosstab rc;
v_range varchar2(30);
v_column_list1 varchar2(8000);
v_column_list2 varchar2(8000);
v_column_list3 varchar2(8000);
v_column_list4 varchar2(8000);
v_string varchar2(32500);
v_string1 varchar2(1000);
v_string2 varchar2(1000);
begin
open v_crosstab for
'select distinct ' || dw_range_column ||
' from ' || dw_table;

loop
fetch v_crosstab into v_range;
exit when v_crosstab%notfound;
v_column_list1 := v_column_list1 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column1 || ', null)) ' || dw_value_column1 || '_' || v_range;
v_column_list2 := v_column_list2 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column2 || ', null)) ' || dw_value_column2 || '_' || v_range;
v_column_list3 := v_column_list3 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column3 || ', null)) ' || dw_value_column3 || '_' || v_range;
v_column_list4 := v_column_list4 || ', max(decode(' || dw_range_column || ', ''' ||
v_range || ''', ' || dw_value_column4 || ', null)) ' || dw_value_column4 || '_' || v_range;
end loop;
close v_crosstab;

BEGIN
v_string1 := 'DROP TABLE ' || dw_param1;
execute immediate v_string1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Table Does Not Exist');
END;

DBMS_OUTPUT.PUT_LINE('1');
v_string2 := 'CREATE INDEX ' || dw_param2 || ' on ' || dw_param1 || ' (' || dw_key_column || ') TABLESPACE DW_IND';
DBMS_OUTPUT.PUT_LINE('2');
v_string := 'CREATE TABLE ' || dw_param1 || ' AS SELECT /*+ INDEX(' || dw_table || ') */' || dw_key_column || ',' ||
substr(v_column_list1, 3) || ',' || substr(v_column_list2, 3) ||
substr(v_column_list3, 3) || ',' || substr(v_column_list4, 3) ||
' from ' || dw_table ||
' group by ' || dw_key_column;
DBMS_OUTPUT.PUT_LINE('3');
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_string,100,350));
execute immediate v_string;
DBMS_OUTPUT.PUT_LINE('4');
execute immediate v_string2;
DBMS_OUTPUT.PUT_LINE('5');

return 0;
end;
end;
/

When I executed this with the following params

var x number
exec:x:=crosstab_pkg.qry('DW_ENX_UPDATE','DW_CONTRACT_NO','DW_LOAD_DATE','DW_BCN_SCORE','DW_NI_SCORE','DW_PNCL_SCORE','DW_SFC_CODE','AGG_ENX_UPDATE','ID_AGG_ENX_UPDATE');

I got the error described in previous post. However when I use three parameters everything works fine

Thanks

Rizwan

Report message to a moderator

Re: Dynamic SQL [message #41064 is a reply to message #41061] Wed, 20 November 2002 18:01 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
The limit is 32K, but that is not your problem here. One of the dynamic statements has a syntax problem (probably because a variable doesn't have the value you expect it to have).

Report message to a moderator

Re:Using native dynamic sql to write a PL/SQL block to calculate the number of records for each of t [message #41084 is a reply to message #41061] Thu, 21 November 2002 20:03 Go to previous message
Tom
Messages: 67
Registered: June 1998
Member
My name is Tom, I am new in PL/SQL program,please write this pl/sql block at the Subject Box and send it back to me at the e-mail address listed above, thank you all.

Report message to a moderator

Previous Topic: Re: Create View with selective concatenation
Next Topic: Re: Have anybody test for Oracle SQL&PL/SQL (1Z0-001)?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon May 06 14:44:18 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.