3706 Error Executing a Procedure

Database
Enthusiast

3706 Error Executing a Procedure

REPLACE  PROCEDURE Dev_DB.CollectStats_New( IN in_db_name VARCHAR(64), IN in_table_name VARCHAR(255), OUT Ret_Code  VARCHAR(200))

CollectStats:

BEGIN

DECLARE  Actvty_Flg  INTEGER;

DECLARE  CS_SQL_Text, abc VARCHAR(3000);

DECLARE  cs_count INTEGER;

DECLARE EXIT HANDLER

FOR SQLEXCEPTION

BEGIN

SET Ret_Code = SQLSTATE;

END; 

SET Actvty_Flg=1;

SEL COUNT(*) INTO :cs_count FROM DBC.Indices

WHERE DatabaseName = :in_db_name 

AND TRIM(tablename) = TRIM(:in_table_name) 

AND indextype IN ('P','S','Q','K','V','I');

IF cs_count = 0  THEN

SET Ret_Code = -1;

  LEAVE CollectStats;

END IF;

FOR Loop1 AS Tables_for_CS CURSOR FOR

SEL databasename cs_dname, tablename cs_tname, indexnumber cs_inbr, indextype cs_indextype, indexname cs_indexname

FROM DBC.Indices

WHERE databasename = :in_db_name

AND TRIM(tablename) = TRIM(:in_table_name)

AND indextype IN ('P','S','Q','K','V','I')

GROUP BY 1,2,3,4,5

ORDER BY 1,2,3,4,5

DO

IF Loop1.cs_indextype = 'I' THEN

SET CS_SQL_Text = '''' || 'COLLECT STATISTICS ON ' || TRIM(Loop1.cs_dname) || '.' || TRIM(Loop1.cs_tname) || ' COLUMN ';

ELSE

SET CS_SQL_Text =  '''' || 'COLLECT STATISTICS ON ' || TRIM(Loop1.cs_dname) || '.' ||  TRIM(Loop1.cs_tname) || ' INDEX ';

END IF;

-- If we have an indexname, then simply CS on the name.  Otherwise, we read the columns for the index and build the index specification.

IF Loop1.cs_indexname IS NOT NULL THEN

SET CS_SQL_Text =  '''' || 'COLLECT STATISTICS ON ' || TRIM(Loop1.cs_dname) || '.' || TRIM(Loop1.cs_tname) || ' INDEX ' || TRIM(Loop1.cs_indexname) || ';';

CALL DBC.SysExecSQL (:CS_SQL_Text);

ELSE

-- Loop2 is the inner loop.  Columns_for_CS is the cursor that reads dbc.indices for the column names.

-- This cursor needs to be refreshed for each iteration based on the indexnumber.

-- Logic is needed to determine if it is the first column to control the use of commas.

FOR Loop2 AS Columns_for_CS CURSOR FOR

SEL columnname cs_cname

FROM DBC.Indices

WHERE databasename = :in_db_name

AND indextype IN ('P','S','Q','K','V','I')

AND databasename = :Loop1.cs_dname

AND tablename = :Loop1.cs_tname

AND indexnumber = :Loop1.cs_inbr

AND indextype = :Loop1.cs_indextype

ORDER BY 1

DO

IF Actvty_Flg = 1 THEN 

  SET Actvty_Flg = 2; 

  SET CS_SQL_Text = CS_SQL_Text || '(' ||  TRIM(Loop2.cs_cname);

ELSE

  SET CS_SQL_Text = CS_SQL_Text || ',' ||  TRIM(Loop2.cs_cname);

END IF;

END FOR;

SET Actvty_Flg=1; 

SET CS_SQL_Text = CS_SQL_Text || ');' || '''';

CALL DBC.SysExecSQL (:CS_SQL_Text); 

END IF; 

END FOR;

END;

When i Execute the same with CALL Dev_DB.CollectStats_New('Dev_DB', 'MyTable', RetCode);

I get 't3706' as ReturnCode.

The Procedure is in ANSI Mode. Can you Please Help me Get over this Error?

Thanks

1 REPLY
Enthusiast

Re: 3706 Error Executing a Procedure

Can anyone Help me get this resolved Please.