| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: DDL alter in execute immediate pl/sql procedure - dynamic sql
Hi
If I am reading this right you are building a PL/SQL block and trying to do and execute immediate on this, you can biuld the bloc as normal anf then create strings of the actual SQL you need to execute and then EXECUTE MMEDIATE thestring
Sometiung like this works
PROCEDURE P_CALENDAR_UPDATE
IS
--============================================================================= v_Average NUMBER; v_Count NUMBER; v_Dcount NUMBER; v_Min NUMBER; v_Max NUMBER; v_Variance NUMBER;
--=============================================================================BEGIN
-- part_count := 0; v_CreateString := 'CREATE GLOBAL TEMPORARY TABLE t_tmp_cal2 ( v_part NUMBER) ON COMMIT DELETE ROWS'; EXECUTE IMMEDIATE v_CreateString; EXCEPTIONReceived on Sun Dec 30 2001 - 17:44:18 CST
WHEN OTHERS THEN
IF SQLCODE != -955 THEN
RAISE;
ELSE
v_TruncString := 'TRUNCATE TABLE t_tmp_cal2';
EXECUTE IMMEDIATE v_TruncString;
END IF;
v_CreateString := 'INSERT INTO t_tmp_cal2 (SELECT DISTINCT v_part from t_tmp_cal1)' ; EXECUTE IMMEDIATE v_CreateString; v_SelectString := 'SELECT COUNT(v_part)from t_tmp_cal2' ; EXECUTE IMMEDIATE v_SelectString INTO part_count; <<statistics_loop>> FOR counter IN 1..part_count LOOP
v_SelectString := 'SELECT v_part FROM t_tmp_cal2 where dbms_rowid.rowid_row_number(rowid) = (:1 - 1)';
EXECUTE IMMEDIATE v_SelectString INTO p_part USING counter;
v_SelectString := 'SELECT STDDEV(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Stddev USING p_part;
v_SelectString := 'SELECT COUNT(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Count USING p_part;
v_SelectString := 'SELECT AVG(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Average USING p_part;
v_SelectString := 'SELECT MIN(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Min USING p_part;
v_SelectString := 'SELECT SUM(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Sum USING p_part;
v_SelectString := 'SELECT MAX(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Max USING p_part;
v_SelectString := 'SELECT VARIANCE(READINGS) FROM t_tmp_cal1 WHERE v_part = :1';
EXECUTE IMMEDIATE v_SelectString INTO v_Variance USING p_part;
v_SelectString := 'SELECT COUNT(READINGS) FROM t_tmp_cal1 WHERE v_part = :1 AND READINGS IS NOT NULL'; EXECUTE IMMEDIATE v_SelectString INTO v_Dcount USING p_part; HTH -- ================================================= Peter McLarty E-mail: Peter.Mclarty_at_mincom.com Technical Consultant WWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, Australia Mobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 ================================================= A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) ================================================= Mincom "The People, The Experience, The Vision" ================================================= "Hagedorn, Linda" <lindah_at_epocrates.com> Sent by: root_at_fatcity.com 29/12/2001 05:55 AM Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Fax to:
Subject: DDL alter in execute immediate pl/sql procedure - dynamic sql
If someone has a few minutes to read through this, I'd be most appreciative. I could use a second set of eyes looking this over. This procedure is designed to maintain a table/sequence map, executed after an import and increment any sequences which have a lower nextval that the max value in the column it's supposed to be matching. Often the sequences are out-of-sync after an import (even full=y and direct=y) and we have to manually adjust them. This is an effort to automate the process. These are the displays and error from the procedure, and the code follows. The problem is in the execute immediate which is doing DDL. It's Oracle 8.1.7 on Solaris so DDL in execute immediate alter is supposed to work. The execute immediate insert does work. Any suggestions or comments are welcome. Thanks, Linda top of loop, counter is:1 top of loop, counter is:2 top of loop, counter is:3 top of loop, counter is:4 top of loop, counter is:5 top of loop, counter is:6 top of loop, counter is:7 top of loop, counter is:8 top of loop, counter is:9 top of loop, counter is:10 top of loop, counter is:11 top of loop, counter is:12 Show l_sql_string 4 DECLARE L_SEQUENCE_OWNER VARCHAR2(30):= :1; L_SEQUENCE_NAME VARCHAR2(30):= :2; BEGIN insert into dbauser.table_sequence_map values (:1,:2, null,null,null,sysdate,sysdate); END; top of loop, counter is:13 top of loop, counter is:14 top of loop, counter is:15 top of loop, counter is:16 top of loop, counter is:17 top of loop, counter is:18 top of loop, counter is:19 Show l_sql_string 1 DECLARE L_SEQUENCE_OWNER VARCHAR2(30):= :1; L_SEQUENCE_NAME VARCHAR2(30):= :2; L_INCREMENT_VALUE NUMBER := :3; BEGIN alter sequence :1.:2 increment by :3; END; BEGIN table_sequence_mender ('01-DEC-2001','dev01') ; END; * ERROR at line 1: ORA-06550: line 1, column 129: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following: begin declare exit for goto if loop mod null pragma raise return select update while <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall <a single-quoted SQL string> ORA-06512: at "DBAUSER.TABLE_SEQUENCE_MENDER", line 148 ORA-06512: at line 1
create or replace procedure table_sequence_mender (date_in in date, ORACLE_SID in char ) is fileid0 UTL_FILE.FILE_TYPE; c_sequences_added number ; c_sequences_unmapped number ; c_sequences_ok number ; c_sequences_fixed number ; c_sequences_total number ; v_column_name varchar(30); v_create_dt date ; v_dt date ; v_increment number ;
v_max_column number ;
v_nextval number ; v_sequence_owner varchar(30); v_sequence_name varchar(30); v_sqlcode number ; v_table_owner varchar(30); v_table_name varchar(30); v_sql varchar(200) ; v_cursor_id integer; v_insert varchar(200) ; l_sql_string varchar(2000) ; -- -- The purpose of this procedure is to adjust the existing sequences after an import to match the dependent columns. -- Requirements: A table matching the sequences to the table/column names. This is built manually because there is -- no physical relationship between a sequence and a column. The table is populated by install_sequence_mender.ksh. -- The execution is running under userid dbauser. Table access in PL/SQL will not use roles, but individual grants; -- Therefore a grant of DBA to dbauser is insufficient to run this procedure. The install ksh grants the necessary -- authority to dbauser. -- cursor c is select sequence_owner, sequence_name from dba_sequences where sequence_owner not in ('SYS','SYSTEM') ; Begin c_sequences_added := 0 ; c_sequences_ok := 0 ; c_sequences_fixed := 0 ; c_sequences_unmapped := 0 ; c_sequences_total := 0 ;
if date_in is null
then v_dt := sysdate; else v_dt := date_in; end if; Begin -- Scan table_sequence_map and delete any rows for sequences that no longer exist Delete from table_sequence_map where sequence_owner||sequence_name not in (select sequence_owner||sequence_name from dba_sequences) ; End ; -- start processing the rows in dba_sequences begin for i_row in c loop c_sequences_total := c_sequences_total + 1 ; dbms_output.put_line ('top of loop, counter is:'||c_sequences_total ) ; begin -- For every row in dba_sequences, find a matching row in the table_sequence_map select table_owner, table_name, column_name, create_dt into v_table_owner, v_table_name, v_column_name, v_create_dt from dbauser.table_sequence_map a where i_row.sequence_owner = a.sequence_owner and i_row.sequence_name = a.sequence_name ; v_sqlcode := SQLCODE ; -- If a row exists for the sequence, and if the table information is populated, -- then check the max value in the data column and the nextval from the sequence. if v_sqlcode = 0 then if v_table_owner is not null and v_table_name is not null and v_column_name is not null then begin v_sql := 'select max('||v_column_name||') from '||v_table_owner||'.'||v_table_name ; execute immediate v_sql into v_max_column ; v_sql := 'select '||i_row.sequence_owner||'.'||i_row.sequence_name||'.nextval from dual ' ; execute immediate v_sql into v_nextval ; end ; -- If the max column number is > the nextval, it means the sequence is broken or out of sync and -- has to be incremented to higher than the max column. Do the math to find the difference, -- alter the sequence increment, call it to move the number up, and alter the sequence increment -- back to 1. if v_max_column > v_nextval then begin v_increment := v_max_column - v_nextval + 1 ;
l_sql_string:= 'DECLARE '||
'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '|| 'L_SEQUENCE_NAME VARCHAR2(30):= :2; '|| 'L_INCREMENT_VALUE NUMBER := :3; '|| 'BEGIN '; l_sql_string:= l_sql_string||'alter sequence :1.:2 increment by :3; END;'; dbms_output.put_line ('Show l_sql_string 1 '||l_sql_string) ; execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name, v_increment ;
commit;
l_sql_string:= 'DECLARE '|| 'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '|| 'L_SEQUENCE_NAME VARCHAR2(30):= :2; '|| 'L_INCREMENT_VALUE NUMBER := :3; '|| 'BEGIN '; l_sql_string:= l_sql_string||'select :1.:2 into v_nextval from dual; END;'; dbms_output.put_line ('Show l_sql_string 2 '||l_sql_string) ; execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ; commit; l_sql_string:= 'DECLARE '|| 'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '|| 'L_SEQUENCE_NAME VARCHAR2(30):= :2; '|| 'L_INCREMENT_VALUE NUMBER := :3; '|| 'BEGIN '; l_sql_string:= l_sql_string||'alter sequence :1.:2 increment by 1; END; ';
dbms_output.put_line ('Show l_sql_string 3 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ; commit;
c_sequences_fixed := c_sequences_fixed + 1 ;
end ; end if ; -- If the table_sequence_map contains an entry for the sequence, but the table information is blank, -- it means that the sequence was added to the database, and to the table_sequence_map by a previous -- execution of this job, and the table information was never added. Updating the table information -- has to be manual because there are no naming conventions to guarantee that a -- sequence and column are associated. elsif ( (v_table_owner is null) or (v_table_name is null) or (v_column_name is null) ) then dbms_output.put_line (i_row.sequence_owner||'.'||i_row.sequence_name||' is not mapped in table_sequence_map since '||v_create_dt ) ;
c_sequences_unmapped := c_sequences_unmapped + 1 ;
end if ; end if ; exception -- If there's no entry in the table_sequence_map for the sequence, it means a new sequence has been -- added to the database. This section will add a row for the sequence to the table_sequence_map. -- The table and column information has to be manually entered with an update statement as there's -- no naming convention to guarantee that a sequence and column are associated. when no_data_found then begin l_sql_string:= 'DECLARE '|| 'L_SEQUENCE_OWNER VARCHAR2(30):= :1; '|| 'L_SEQUENCE_NAME VARCHAR2(30):= :2; '|| 'BEGIN '; l_sql_string:= l_sql_string||'insert into dbauser.table_sequence_map values (:1,:2, null,null,null,sysdate,sysdate); END;';
dbms_output.put_line ('Show l_sql_string 4 '||l_sql_string) ;
execute immediate l_sql_string using i_row.sequence_owner, i_row.sequence_name ; commit; exception when no_data_found then null ; c_sequences_added := c_sequences_added + 1 ; dbms_output.put_line ('New row added to the map table '||i_row.sequence_owner||'.'||i_row.sequence_name ) ;
COMMIT ;
end ; when others then raise ; end ; end loop ; end ; end ; /
-- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.
 |
 |