CODEWITHSUNDEEP
stored-proceduresnetezza
Venkat
Venkat

Reputation: 109

Netezza stored procedure error

I am my trying to convert an oracle procedure of something like below to Netezza and facing some issues when executed, no compilation errors. The procedure was executed successfully in Oracle. About the task, it is batch process without expecting to return or take input parameters and loop through the a table to prepare an SQL statement dynamically. Logic is built well as there is no issue in Oracle.

CREATE OR REPLACE PROCEDURE proc1 ()
   RETURNS CHARACTER VARYING (ANY) EXECUTE AS OWNER  LANGUAGE NZPLSQL AS 
BEGIN_PROC

DECLARE

lv_sql varchar(4000);
lv1 varchar(4000);
lv2 varchar(4000);
lv3 varchar(4000);
lv4 varchar(4000);
lv5 varchar(4000);
lv_value varchar(1000);
lv_str_cnt integer;
lv_ret_string VARCHAR(1000);

BEGIN
    FOR c1 IN (SELECT * from test)
    LOOP
            IF lv_str_cnt = 0 THEN
              lv_ret_string := '''' || c1.col1 || '''';
            ELSE IF  lv_str_cnt IS NULL THEN
              lv_ret_string := NULL;
            ELSE
                lv_ret_string := '''' || c1.col2 || '''';
            END IF;
            .............
            .............

        lv_sql := lv_sql || lv1 || lv2 || lv3 || lv4;

        EXECUTE IMMEDIATE lv_sql;

     END LOOP;  

END; 
END_PROC;

I get the following error when the procedure is executed "Syntax error, unexpected WORD at or near Varchar2"

Varchar2 is an Oracle datatype and I can confirm it is modified to Varchar of Netezza in my procedure in all the places. I searched on google to find good examples of creating a procedure but unfortunately I could not get desired help.

Also there is particular block code which I want to create as function and re-use it. Unfortunately stuck up with function syntax too.

Appreciate your help

Upvotes: 0

Views: 1628

Answers (2)

Venkat
Venkat

Reputation: 109

I felt it is time to return back with answer to my own question.

CREATE OR REPLACE PROCEDURE proc1 ()
   RETURNS CHARACTER VARYING (ANY) EXECUTE AS OWNER  LANGUAGE NZPLSQL AS 
BEGIN_PROC

DECLARE
c1      RECORD;
v_sql   text;

lv_sql varchar(4000);
lv1 varchar(4000);
lv2 varchar(4000);
lv3 varchar(4000);
lv4 varchar(4000);
lv5 varchar(4000);
lv_value varchar(1000);
lv_str_cnt integer;
lv_ret_string VARCHAR(1000);

BEGIN
    v_sql := 'SELECT * from test;';

    FOR c1 IN EXECUTE v_sql
    LOOP
            IF lv_str_cnt = 0 THEN
              lv_ret_string := '''' || c1.col1 || '''';
            ELSE IF  lv_str_cnt IS NULL THEN
              lv_ret_string := NULL;
            ELSE
                lv_ret_string := '''' || c1.col2 || '''';
            END IF;
            .............
            .............

        lv_sql := lv_sql || lv1 || lv2 || lv3 || lv4;

        EXECUTE IMMEDIATE lv_sql;

     END LOOP;  

END; 
END_PROC;

Upvotes: 0

Lars G Olsen
Lars G Olsen

Reputation: 1118

You are on the right track, but I can see at least one mentioning of varchar2(): the last one of your declarations:

lv_ret_string VARCHAR2(1000);

Upvotes: 0

Related Questions