All Forums General
SAP 73 posts Joined 08/14
19 Sep 2014
Stored Procedure

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 !!
 

SAP
Priya01 10 posts Joined 08/14
19 Sep 2014

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();
 

SAP 73 posts Joined 08/14
21 Sep 2014

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

dnoeth 4628 posts Joined 11/04
21 Sep 2014

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

SAP 73 posts Joined 08/14
27 Sep 2014

So Dieter , DDL and DML cant be in same stored Procedure ?

SAP

dnoeth 4628 posts Joined 11/04
27 Sep 2014

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

SAP 73 posts Joined 08/14
28 Sep 2014

Got it !!!! Experimented it as well !!! Thanks !!

SAP

salihtopcu 2 posts Joined 10/14
14 Oct 2014

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

salihtopcu 2 posts Joined 10/14
14 Oct 2014

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;

 

 

You must sign in to leave a comment.