Stored Procedure Error

Database
Enthusiast

Stored Procedure Error

I am getting the below error when i  execute the below script

CREATE PROCEDURE DB_T_STAG_R1_DEV.SP_SAMPLE_SH (OUT STATUS INTEGER)

BEGIN

CREATE VOLATILE TABLE V_TABLE1

(

COL1 INTEGER


PRIMARY INDEX (COL1)

ON COMMIT PRESERVE ROWS;

INSERT INTO V_TABLE1

SEL DISTINCT DT_ID FROM DB_V_R1_DEV.DT_DIM;

INSERT INTO V_TABLE2 SEL DISTINCT COL1, COL1,'ABC',1 FROM V_TABLE1;

END;

SPL1027:E(L10), Missing/Invalid SQL statement'E(3807):Object 'V_TABLE1' does not exist.'.

SPL1027:E(L11), Missing/Invalid SQL statement'E(3807):Object 'V_TABLE1' does not exist.'.


Can someone help?

Thanks,

Sharat Balaji P R

Tags (1)
3 REPLIES
Enthusiast

Re: Stored Procedure Error

Hi Sharat,

Can you confirm if those are error or warning messages.

I believe these would be warning messages so still you can go ahead and execute your stored proc after compilation, it will work.

In your code, you have missed the V_TABLE2 creation code.

Thanks,

Priya

Enthusiast

Re: Stored Procedure Error

Priya,

These are errors and not warnings.

V_TABLE2 is a permanent table created outside of the stored procedure

Enthusiast

Re: Stored Procedure Error

Priya,

THis is resolved now. 

As suggested by other teradata forums, i created the volatile table V_TABLE1 outside of SP in the same session and then compile the above SP. Now the SP compiled and the errors are shown as warnings. I disconnected the session and the created a new session and then called the stored procedure. SP executed successfully.