Compile it first and then execute it.
Try this
step 1
REPLACE PROCEDURE CCC.YYYYYC()
BEGIN
CREATE VOLATILE TABLE VT_TEXT1
(
Column1 DECIMAL(38,2) NULL,
Column2 varchar(8000) NULL
)
ON COMMIT PRESERVE ROWS
;
INSERT INTO VT_TEXT1 VALUES (1,'SGSH');
END;
/
step 2
call CCC.YYYYYC();
Hi Priya ,
Thanks for the reply !!! But when i tried , i m unable to create a stored procedure .!! :(
Failed [5526 : HY000] Stored Procedure
is not created/replaced due to error(s).{Nested Failure Msg [5526 : HY000] SPL1027:E(L11), Missing/Invalid SQL statement'E(3807):Object 'VT_TEXT1' does not exist.'.}
Elapsed
time = 00:00:00.016
SAP
Simply create the Volatile table first and then the SP.
Of course you should have some code within your SP which deals with an existing VT, otherwise you can't CALL it a 2nd time within the same session.
Dieter
So Dieter , DDL and DML cant be in same stored Procedure ?
SAP
Of course can DDL and DML be mixed in the same SP, you just have to follow the rule that a DDL must be commited.
Dieter
Got it !!!! Experimented it as well !!! Thanks !!
SAP
Hi,
I wanna create a procedure,
This procedure is Select - Insert , but i have a problem about time data type columns,
Error : "Invalid Operation for DateTime or Interval"
replaceProcedure Prefix.ProcedureName_1 (OUT VOUT byteint) BEGIN insert into Prefix.Table_X(Time_Column) select cast(starttime*100 as TIME) StartTime; END; replaceProcedure Prefix.ProcedureName_2 (OUT VOUT byteint) BEGIN insert into Prefix.Table_Y(TimeColumn) select cast(SUBSTR(cast(cast(DATE '1970-01-01' + (starttime_utc/86400) as timestamp(3)) + ((starttime_utc MOD 86400)* INTERVAL'00:00:01.000' HOUR TO SECOND as char(27)),12,15) as time(3)) UTCStartTime; END; Sample starttime values; --starttime is integer data type Source Target 0 00:00:00 130 01:30:00 30 00:30:00 1545 15:45:00 300 03:00:00 --------------------------------------------------------------- Sample starttime_utc value --starttime_utc is Unixtime Source Target 1413087300 04:15:00
Select script is successful;
But i can't create a procedure,
Regards,
SALIH TOPCU
salih.topcu@assistt.com.tr
Hi ,
i solve this problem;
replaceProcedure Prefix.ProcedureName_2 (OUT VOUT byteint)
BEGIN
insert into Prefix.Table_Y(TimeColumn)
select cast(cast(SUBSTR(cast(cast(DATE '1970-01-01' + (starttime_utc/86400) as timestamp(3)) + ((starttime_utc MOD 86400)* INTERVAL'00:00:01.000' HOUR TO SECOND as char(27)),12,15) as time(3))as varchar(24)) UTCStartTime;
END;
Hi All ,
Please help me on below :-
REPLACE PROCEDURE CCC.YYYYYC()
BEGIN
CREATE VOLATILE TABLE VT_TEXT1
(
Column1 DECIMAL(38,2) NULL,
Column2 varchar(8000) NULL
)
ON COMMIT PRESERVE ROWS
;
INSERT INTO VT_TEXT1 VALUES (1,'SGSH');
END;
I am unable to create it . but i can use the volatile table when i create it outside and execute it in same session . Is this the case for all kind of tables ?
Thanks in Advance !!