Thread: Function error

Function error

From
Sachin Srivastava
Date:

Dear Team,

I am getting the below error for function, please see the bold line in "Function code", please suggest what I will do to correct this code.

 -----------------------

ERROR:  syntax error at or near "*"
LINE 35: SELECT * from logError(msg text) is
                ^
CONTEXT:  invalid type name "* from logError(msg text) is

BEGIN
    insert into SMERROR_LOG
    (error_message,  error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
       values(msg,  nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"
********** Error **********

ERROR: syntax error at or near "*"
SQL state: 42601
Character: 1276
Context: invalid type name "* from logError(msg text) is

BEGIN
    insert into SMERROR_LOG
    (error_message,  error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
       values(msg,  nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"

--------------


Function Code as below:


----------------------------------------------------------------------------------------------------------------------------------------------------------


-- Function: cp_property_room_count_trans(bigint, bigint, text)

-- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);

CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
    subscriberid bigint,
    incompanyid bigint,
    loginname text)
  RETURNS void AS
$BODY$
DECLARE

 
  CRS_1 CURSOR FOR SELECT distinct company_id from CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag is null or process_flag=0)  order by company_id;

  --Version:
  -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
  --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
  -- 06/08/07 (Bea) insert value for CP_ROOM_TYPE_COUNT.room_budget_home_amt
  --          If phaseID is new, validate that these as required fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
  --           Will set process_flag=2 if fail the validation.
  --          CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated per business rule 15327    
  --
 
  RW_1 RECORD; --CRS_1 %ROWTYPE;
  err_msg_1 varchar(100);
  v_errorMessage_1 varchar(4000);
  sucessfulRecCount bigint :=0;
  failedRecCount bigint :=0;
 
  --************************************************************
  SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, companyID bigint, supplierSku text, loginName text) is
 
BEGIN
     insert into SMERROR_LOG
     (error_message, method_name, system_message, error_log_id, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
     values(v_errorMesg, procedureName, supplierSku, nextval('smerror_log_sequence'), subscriberID, companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin'); 
  end;

  --************************************************************
 
  FUNCTION Invoke_InsertPropertyRCProcess(subID in number, compID in number, loginName in varchar2) is
    
      CRS CURSOR FOR
             SELECT st.*, st.rowid, ph.project_code from CP_ROOM_COUNT_STAGING st, CP_PROJECT_PHASE ph 
             where st.SUBSCRIBER_ID =subID and st.company_id=compID and (st.process_flag is null or st.process_flag=0)
             and ph.SUBSCRIBER_ID(+)= st.SUBSCRIBER_ID AND ph.PHASE_ID(+)= st.CORP_PHASE_ID order by st.subscriber_id, st.company_id, st.phase_id;
      
     RW CRS%ROWTYPE;
     err_msg varchar2(100);
     v_errorMessage varchar2(4000);
     v_errorCountExp number :=0;
     MAX_ERR_WRITTEN constant number :=10;
     recCount number := 0;
  
     checkCount number;
     startRowCounter number :=0;
     isValidated boolean :=true;
    

     begin
       logError('Begin Invoke_InsertPropertyRCProcess subID:'|| subID || ' and compID:'||compID,'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);    
      
       -- the row_number counter starting number
       select max(Row_number) into startRowCounter from CP_ROOM_TYPE_COUNT 
       where subscriber_id=subID and company_id=compID;
      
       if(startRowCounter is null) then
           startRowCounter :=0;
       end if;
            
       open CRS;
       loop
          begin -- the begin inside the loop
             fetch CRS into RW;
             IF NOT FOUND THEN EXIT; END IF; -- apply on CRS
               recCount := recCount+1;
               startRowCounter := startRowCounter+1;
              
               --check to if phase_id already existed in CP_PROPERTY_PHASE  if not insert
               select  count(1)  into checkCount from CP_PROPERTY_PHASE
               where subscriber_id=subID and company_id=compID and PHASE_ID=RW.PHASE_ID;
              
               isValidated := true;
                --check to see if pass validation.
               if(checkCount =0 and (RW.phase_start_date is null or RW.phase_description is null or RW.corp_phase_id is null)) then
                    isValidated:=false;
                    --update processing flag to 2 as fail.
                   update CP_ROOM_COUNT_STAGING set process_flag=2 where rowid=RW.rowid;
                   failedRecCount := failedRecCount+1;
               end if;
                
               if(isValidated = true) then    
                 if(checkCount =0 ) then -- if phase_id not yet existed in the cp_property_phase table
                   insert into cp_property_phase
                   (subscriber_id, company_id, phase_id, phase_seq, phase_description,
                   corp_phase_id, phase_start_date, duration_type, phase_status, po_created_flag,
                   project_code, phase_level_path, is_deleted, phase_1_code,
                   creation_date, creation_user)
                   values
                   (subID, compID, RW.phase_id, RW.phase_id, RW.phase_description,
                   RW.corp_phase_id, RW.phase_start_date, 'W', 0, 0,
                   RW.project_code,  RW.phase_id, 0,
                   lpad(to_char(RW.phase_id),5,'0')||' '||upper(substring(RW.phase_description from 1 for 40)),
                   LOCALTIMESTAMP, loginName);
                  end if;--if(checkCount =0 )
            
                  --insert into CP_ROOM_TYPE_COUNT  table
                  insert into cp_room_type_count
                  (subscriber_id, company_id, phase_id,  room_type_code,
                  room_count, room_measure_unit, room_length, room_width,
                  room_height, bath_length, bath_width, row_number, room_budget_home_amt,
                  creation_date, creation_user)
                  values
                  (subID, compID, RW.phase_id, RW.room_type_code,
                   RW.room_count, RW.room_measure_unit, RW.room_length, RW.room_width,
                   RW.room_height, RW.bath_length, RW.bath_width, startRowCounter, RW.room_budget_home_amt,
                   LOCALTIMESTAMP, loginName);
              
                   --update processing flag to 1 as successful.
                   update CP_ROOM_COUNT_STAGING set process_flag=1 where rowid=RW.rowid;
                   sucessfulRecCount := sucessfulRecCount +1;
                  
                   if(mod(recCount, 500) = 0 ) then
                        commit;
                   end if;
               end if; -- if(isValidated = true)
             exception
             when others then
                if(v_errorCountExp < MAX_ERR_WRITTEN) then  
                    err_msg := substring(SQLERRM from 1 for 100);
                    v_errorMessage:=  'Run into Exception in Invoke_InsertPropertyRCProcess: ' || err_msg;
                    logError(v_errorMessage, 'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);  
                 end if;
                 v_errorCountExp := v_errorCountExp+1;
                 commit;       
            end;   --for inside begin
          end loop;                                                              
      close CRS;
      
      if( failedRecCount > 0) then
          logError('Error: set cp_room_count_staging.process_flag=2 ( Failed to insert new records dued to one of these required fields are empty: Corp Phase ID, Phase Desc or Phase Start Date)','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
      end if;
     
      logError('Ending Invoke_InsertPropertyRCProcess Total records: '||recCount|| '.  There are ' || sucessfulRecCount || ' sucessfull and ' || failedRecCount ||' failed records.','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);  
      commit;
  end Invoke_InsertPropertyRCProcess;
 
  --************************************************************

begin

   logError('Begin CP_Property_Room_Count_Trans ','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);    
 
   open CRS_1;
       loop
          begin -- the begin inside the loop
             fetch CRS_1 into RW_1;
             IF NOT FOUND THEN EXIT; END IF; -- apply on CRS_1
            
             sucessfulRecCount := 0;
             failedRecCount := 0;
             Invoke_InsertPropertyRCProcess(subscriberID, RW_1.company_ID, loginName);
             
             exception
             when others then
                    err_msg_1 := substring(SQLERRM from 1 for 100);
                    v_errorMessage_1:=  'Run into Exception in CP_Property_Room_Count_Trans: ' || err_msg_1;
                    logError(v_errorMessage_1, 'CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);  
                
                 commit;       
          end;   --for inside begin
       end loop;                                                              
   close CRS_1;
     
   logError('Ending CP_Property_Room_Count_Trans.','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);    
   commit;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION cp_property_room_count_trans(bigint, bigint, text)
  OWNER TO postgres;

----------------------------------------------------------------------------------------------------------------------------


 

 

Re: Function error

From
Albe Laurenz
Date:
Sachin Srivastava wrote:
> I am getting the below error for function, please see the bold line in "Function code", please suggest
> what I will do to correct this code.
> 
>  -----------------------
> 
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
>                 ^
> CONTEXT:  invalid type name "* from logError(msg text) is

That error message does not look like it could come from the
function definition below, but the function definition is clearly
syntactically incorrect:

> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
>     subscriberid bigint,
>     incompanyid bigint,
>     loginname text)
>   RETURNS void AS
> $BODY$
> DECLARE

[...]

>   SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, companyID bigint,
> supplierSku text, loginName text) is
> 
> BEGIN
>      insert into SMERROR_LOG
>      (error_message, method_name, system_message, error_log_id, subscriber_id, company_id,
> creation_date, creation_user, update_date, update_user)
>      values(v_errorMesg, procedureName, supplierSku, nextval('smerror_log_sequence'), subscriberID,
> companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>   end;

You cannot declare a function inside the DECLARE section of
another function.  Besides, what is "SELECT logError" supposed to mean?

You will have to declare each of the helper functions with ist own
CREATE FUNCTION statement.

Yours,
Laurenz Albe

Re: Function error

From
Adrian Klaver
Date:
On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
>
> Dear Team,
>
> I am getting the below error for function, please see the bold line in
> "Function code", please suggest what I will do to correct this code.
>
>   -----------------------
>
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
>                  ^
> CONTEXT:  invalid type name "* from logError(msg text) is
>

>
> *_Function Code as below:_*
>
>
>
----------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -- Function: cp_property_room_count_trans(bigint, bigint, text)
>
> -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
>
> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
>      subscriberid bigint,
>      incompanyid bigint,
>      loginname text)
>    RETURNS void AS
> $BODY$
> DECLARE
>
>
>    CRS_1 CURSOR FOR SELECT distinct company_id from
> CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag
> is null or process_flag=0)  order by company_id;
>
>    --Version:
>    -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
>    --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
>    -- 06/08/07 (Bea) insert value for
> CP_ROOM_TYPE_COUNT.room_budget_home_amt
>    --          If phaseID is new, validate that these as required
> fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
>    --           Will set process_flag=2 if fail the validation.
>    --          CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated
> per business rule 15327
>    --
>
>    RW_1 RECORD; --CRS_1 %ROWTYPE;
>    err_msg_1 varchar(100);
>    v_errorMessage_1 varchar(4000);
>    sucessfulRecCount bigint :=0;
>    failedRecCount bigint :=0;
>
> *  --************************************************************
>    SELECT logError(v_errorMesg text, procedureName text, subscriberID
> bigint, companyID bigint, supplierSku text, loginName text) is

I have not even tried to go through all of this function, but the above is a problem in and of itself.

If you want to do something that returns no result then you need to use PERFORM:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

That stills the question of what ..'loginName text) is' is supposed to be doing?

If you are trying to SELECT the result of logError into a  variable then you need to use INTO:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

If you are trying to do something else, then as Albe said, you need to let us know what that is?


>
> BEGIN
>       insert into SMERROR_LOG
>       (error_message, method_name, system_message, error_log_id,
> subscriber_id, company_id, creation_date, creation_user, update_date,
> update_user)
>       values(v_errorMesg, procedureName, supplierSku,
> nextval('smerror_log_sequence'), subscriberID, companyID,
> LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>    end;*



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Function error

From
Sachin Srivastava
Date:
Dear Albe,

Thanks for your support !!!

In my function the problem is that global variables defined inside the function. These variables are visible to functions defined inside a function. If we move these inner functions to outside of the main function, they will lose the visibility of the global variables. So I think that's why we have to correct this functions rather then creating it as individual.

We have migrated our data from Oracle to Postgres through ORA2PG Tool after that we are getting this error for this function.

Dear Adrian,

I have also used the PERFORM option but still it's not running.

Regards,
Sachin

 


On Thu, Jan 14, 2016 at 12:37 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 01/13/2016 01:18 AM, Sachin Srivastava wrote:
>
> Dear Team,
>
> I am getting the below error for function, please see the bold line in
> "Function code", please suggest what I will do to correct this code.
>
>   -----------------------
>
> ERROR:  syntax error at or near "*"
> LINE 35: SELECT * from logError(msg text) is
>                  ^
> CONTEXT:  invalid type name "* from logError(msg text) is
>

>
> *_Function Code as below:_*
>
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
> -- Function: cp_property_room_count_trans(bigint, bigint, text)
>
> -- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);
>
> CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
>      subscriberid bigint,
>      incompanyid bigint,
>      loginname text)
>    RETURNS void AS
> $BODY$
> DECLARE
>
>
>    CRS_1 CURSOR FOR SELECT distinct company_id from
> CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag
> is null or process_flag=0)  order by company_id;
>
>    --Version:
>    -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
>    --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
>    -- 06/08/07 (Bea) insert value for
> CP_ROOM_TYPE_COUNT.room_budget_home_amt
>    --          If phaseID is new, validate that these as required
> fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
>    --           Will set process_flag=2 if fail the validation.
>    --          CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated
> per business rule 15327
>    --
>
>    RW_1 RECORD; --CRS_1 %ROWTYPE;
>    err_msg_1 varchar(100);
>    v_errorMessage_1 varchar(4000);
>    sucessfulRecCount bigint :=0;
>    failedRecCount bigint :=0;
>
> *  --************************************************************
>    SELECT logError(v_errorMesg text, procedureName text, subscriberID
> bigint, companyID bigint, supplierSku text, loginName text) is

I have not even tried to go through all of this function, but the above is a problem in and of itself.

If you want to do something that returns no result then you need to use PERFORM:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT

That stills the question of what ..'loginName text) is' is supposed to be doing?

If you are trying to SELECT the result of logError into a  variable then you need to use INTO:

http://www.postgresql.org/docs/9.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

If you are trying to do something else, then as Albe said, you need to let us know what that is?


>
> BEGIN
>       insert into SMERROR_LOG
>       (error_message, method_name, system_message, error_log_id,
> subscriber_id, company_id, creation_date, creation_user, update_date,
> update_user)
>       values(v_errorMesg, procedureName, supplierSku,
> nextval('smerror_log_sequence'), subscriberID, companyID,
> LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin');
>    end;*



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Function error

From
"David G. Johnston"
Date:
On Wed, Jan 13, 2016 at 10:06 PM, Sachin Srivastava <ssr.teleatlas@gmail.com> wrote:
Dear Albe,

Thanks for your support !!!

In my function the problem is that global variables defined inside the function. These variables are visible to functions defined inside a function.

​Holy Crap...PostgreSQL functions support closures and global variables?  Who knew?  Well, aside from that fact that relations are global, anyway...

The problem you seem to have is trying to do something in PostgreSQL exactly like you'd do it in Oracle.  While I'm sure many things can be ported over you seem to have stumbled upon one of those things that likely will need to be written using PostgreSQL idioms.

​David J.​

Re: Function error

From
Albe Laurenz
Date:
Sachin Srivastava wrote:
> In my function the problem is that global variables defined inside the function.
> These variables are visible to functions defined inside a function.
> If we move these inner functions to outside of the main function,
> they will lose the visibility of the global variables.
> So I think that's why we have to correct this functions rather then creating it as individual.

I see.
You'll have to rewrite the functions then, probably by passing the shared
values as parameters instead of having them in global variables.

> We have migrated our data from Oracle to Postgres through ORA2PG Tool
> after that we are getting this error for this function.

ora2pg cannot translate all PL/SQL code, you have to be prepared to rewrite
most nontrivial functions.

Yours,
Laurenz Albe