How to create a temporary table within a function in oracle?

Asked

Viewed 14 times

0

I’m creating a time sheet in the system. The idea is that the user fill in a filter with the reference date and then Function prints every day of the month with the abbreviation of the day of the week(01-sec),02-b) so that the employees know when it will be Saturday and Sunday and so do not fill in. I’m still crawling on database so I’m sorry if the question is silly rs. I also accept suggestions regarding the logical way I am thinking. All knowledge is welcome. I thank you for your help.

When executing the function I come across the following error : PLS-00103: Encountered the Symbol "BEGIN" when expecting one of the following: := ; not null default Character The Symbol ";" was substituted for "BEGIN" to continue.

CREATE OR REPLACE FUNCTION SANKHYA.MV_FOLHA_PONTO
(
P_DTREF IN  DATE,
P_EMPRESA IN INT,
P_FUNCIONARIO IN INT)  

RETURN VARCHAR2
AS
P_CODEMP INT;
P_NOMEFANTASIA VARCHAR2(200);
P_CNPJ VARCHAR2(100);
P_CODFUNC INT;
P_NOMEFUNC VARCHAR2(200);
P_CARGO VARCHAR2(200);
P_ADMISSAO DATE;
P_CPS VARCHAR2(200);
P_DEPARTAMENTO VARCHAR2(200);
P_CARGAHORARIA VARCHAR2(200);
P_TABLE VARCHAR2(4000)
BEGIN 
     
 EXECUTE IMMEDIATE 'CREATE  GLOBAL TEMPORARY TABLE TMP_MV_PONTO 
                    ( CODIGO IDENTITY  INT,DATA_MES DATE ,DIA_SEMANA VARCHAR2(200))ON COMMIT DELETE ROWS';
    
        
        WHILE P_DTREF < LAST_DAY(P_DTREF)
       
            LOOP 
                   

        
                INSERT INTO TMP_MV_PONTO VALUES(    
                    DATA_MES +1
                    DIA_SEMANA);
                
                SELECT * FROM  TMP_MV_PONTO;
            
END LOOP;
        
RETURN MV_FOLHA_PONTO;
END;
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.