Raghuraman_New
New User
Joined: 03 Oct 2008
Posts: 43
Location: Chennai,India.
|
|
|
|
Hi,
I am trying to write a new Native SQL stored Procedure. I would like to know whether there is any option for declaring the Fixed-length tables or variable length tables like we do in Cobol.
| Code: |
DECLARE PV_SQL_CODE INTEGER DEFAULT 0;
DECLARE PV_SQL_STATE CHAR(05) DEFAULT '00000';
DECLARE PV_MSG_TX VARCHAR(200) DEFAULT 'NO MSG';
|
To use the array in my Native SQL, i have used the alternate solution like below,
| Code: |
IF IN_CNTC_AFFL_CNT_1 > 1 THEN
MOVE_LOOP: LOOP
IF IN_CNTC_AFFL_INFO_1 > SPACES THEN
SET IN_ACTION_CD =
SUBSTR(IN_CNTC_AFFL_INFO_1,1,1);
SET IN_STRUC_ID =
SUBSTR(IN_CNTC_AFFL_INFO_1,2,12);
SET IN_NODE_ID =
SUBSTR(IN_CNTC_AFFL_INFO_1,14,20);
SET IN_NODE_VAL_IND =
SUBSTR(IN_CNTC_AFFL_INFO_1,34,01);
SET IN_NODE_VAL_TEXT =
SUBSTR(IN_CNTC_AFFL_INFO_1,35,30);
SET IN_DELIMTR =
SUBSTR(IN_CNTC_AFFL_INFO_1,65,1);
ELSE
SET PV_MSG_TX = 'INVALID ADDRESS';
SIGNAL SQLSTATE '75001';
END IF;
SET PV_INT_CNT = PV_INT_CNT + 1;
IF IN_CNTC_AFFL_CNT_1 = PV_INT_CNT THEN
LEAVE MOVE_LOOP;
END IF;
END LOOP MOVE_LOOP;
ELSE
SET IN_ACTION_CD = SUBSTR(IN_CNTC_AFFL_INFO_1,1,1);
SET IN_STRUC_ID = SUBSTR(IN_CNTC_AFFL_INFO_1,2,12);
SET IN_NODE_ID = SUBSTR(IN_CNTC_AFFL_INFO_1,14,20);
SET IN_NODE_VAL_IND =
SUBSTR(IN_CNTC_AFFL_INFO_1,34,01);
SET IN_NODE_VAL_TEXT =
SUBSTR(IN_CNTC_AFFL_INFO_1,35,30);
SET IN_DELIMTR = SUBSTR(IN_CNTC_AFFL_INFO_1,65,1);
END IF; |
But I need to know whether in the Native SQL procedure, can i declare like fixed length tables or variable length tables. do we have a option to do like that.. ?? Please advise |
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
