Stored Procedure

General
SAP
N/A

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

8 REPLIES

Re: Stored Procedure

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
N/A

Re: Stored Procedure

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

N/A

Re: Stored Procedure

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.

SAP
N/A

Re: Stored Procedure

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

N/A

Re: Stored Procedure

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.

SAP
N/A

Re: Stored Procedure

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

Re: Stored Procedure

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

Re: Stored Procedure

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;