Converting only a few lines 1

[Sahubba]

Hello,

I am creating a Procudure to move data from a current table to a new table.
Right now I am test and I was wondering is there a way I can move just a few lines to test instead of the whole table?

Thanks,

 

[taupirho]

Yes, there is. make use of the rownum pseudo cilumn.

e.g insert into t2 select * from t1 where rownum <= 5

Will copy only 5 records from t1 to t2


In order to understand recursion, you must first understand recursion.

 

[Sahubba]

Thank you for responding!

I am going to ask you a question and I hope its not dumb.
I have no clue where to add that. Here is the code.... I only want no more than 100 records migrated

CREATE OR REPLACE PROCEDURE CR_TDL_CONV (
POST_DT IN DATE
)
IS
/* variable declarations based on their associated column counterparts*/
KCOL_NAME USER_IND_COLUMNS.COLUMN_NAME%TYPE;
COL_NAME USER_TAB_COLUMNS.COLUMN_NAME%TYPE;
DATA_TYPE USER_TAB_COLUMNS.DATA_TYPE%TYPE;
DATA_LEN USER_TAB_COLUMNS.DATA_LENGTH%TYPE;
DATA_PREC USER_TAB_COLUMNS.DATA_PRECISION%TYPE;
DATA_SCAL USER_TAB_COLUMNS.DATA_SCALE%TYPE;
NULLS USER_TAB_COLUMNS.NULLABLE%TYPE;
TBLSPC_NAME ALL_TABLES.TABLESPACE_NAME%TYPE;
PART_TYPE ALL_PART_TABLES.PARTITIONING_TYPE%TYPE;
SUBPART_TYPE ALL_PART_TABLES.SUBPARTITIONING_TYPE%TYPE;
PART_CNT ALL_PART_TABLES.PARTITION_COUNT%TYPE;
SUBPART_CNT ALL_PART_TABLES.DEF_SUBPARTITION_COUNT%TYPE;
HIGH_VAL ALL_TAB_PARTITIONS.HIGH_VALUE%TYPE;
PART_NAME ALL_TAB_PARTITIONS.PARTITION_NAME%TYPE;
PRTTBLSPC_NAME ALL_TAB_PARTITIONS.TABLESPACE_NAME%TYPE;
PARTCOL_NAME ALL_PART_KEY_COLUMNS.COLUMN_NAME%TYPE;
SUBCOL_NAME ALL_SUBPART_KEY_COLUMNS.COLUMN_NAME%TYPE;
SUBPART_NAME ALL_TAB_SUBPARTITIONS.SUBPARTITION_NAME%TYPE;
SPTBLSPC_NAME ALL_TAB_SUBPARTITIONS.TABLESPACE_NAME%TYPE;

/* This cursor is used for grabbing out all the names of the columns
that are primary keys for TBL_TDL */
CURSOR KCOLS
IS
SELECT COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE INDEX_NAME = (SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'TBL_TDL'
AND UNIQUENESS = 'UNIQUE')
AND TABLE_NAME = 'TBL_TDL';


/* This cursor is used for selecting all the names of the columns for
TBL_TDL */
CURSOR COLS
IS
SELECT COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION
, DATA_SCALE
, NULLABLE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_TDL'
AND COLUMN_NAME IN (SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_TDL')
ORDER BY COLUMN_ID;

/* This cursor is used for determining if the table in
CLARITY_TDL is partitioned or not and the tablespace name*/
CURSOR TABPART
IS
SELECT PARTITIONED
, TABLESPACE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME = 'TBL_TDL';

/* This cursor is used for determining the primary key index
tablespace for TBL_TDL */
CURSOR PKTABSPACE
IS
SELECT AI.TABLESPACE_NAME
FROM ALL_INDEXES AI
WHERE AI.TABLE_NAME = 'TBL_TDL'
AND AI.INDEX_NAME IN (SELECT AC.INDEX_NAME
FROM ALL_CONSTRAINTS AC
WHERE AC.TABLE_NAME = 'TBL_TDL'
AND AC.CONSTRAINT_TYPE='P');

/* This cursor is used for determining the partition info
for TBL_TDL if it is partitioned*/
CURSOR PART
IS
SELECT PARTITIONING_TYPE
, SUBPARTITIONING_TYPE
, PARTITION_COUNT
, DEF_SUBPARTITION_COUNT
FROM ALL_PART_TABLES
WHERE TABLE_NAME = 'TBL_TDL';

/* This cursor is used for getting the high_value if the partition
for CLARITY_TDL if it is partitioned*/
CURSOR PART_HIGH
IS
SELECT HIGH_VALUE
, PARTITION_NAME
, TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = 'TBL_TDL'
ORDER BY PARTITION_POSITION;

/* This cursor is used for getting the column name for partitions
for TBL_TDL*/
CURSOR PARTCOLNAM
IS
SELECT COLUMN_NAME
FROM ALL_PART_KEY_COLUMNS
WHERE NAME = 'TBL_TDL';

/* This cursor is used for getting the column name for sub-partitions
for TBL_TDL*/
CURSOR SUBCOLNAM
IS
SELECT COLUMN_NAME
FROM ALL_SUBPART_KEY_COLUMNS
WHERE NAME = 'TBL_TDL';

/* This cursor is used for getting the sub-partition names
for all the sub-partitions for TBL_TDL*/
CURSOR SUBPARTNAM
IS
SELECT SUBPARTITION_NAME
, TABLESPACE_NAME
FROM ALL_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'TBL_TDL'
AND PARTITION_NAME = PART_NAME;


/* The next variables are rows in the previously defined cursors */
KCOLSROW KCOLS%ROWTYPE;
COLSROW COLS%ROWTYPE;
TABPARTROW TABPART%ROWTYPE;
PKTABSPACEROW PKTABSPACE%ROWTYPE;
PARTROW PART%ROWTYPE;
PARTHIGHROW PART_HIGH%ROWTYPE;
PARTCOLNAMROW PARTCOLNAM%ROWTYPE;
SUBCOLNAMROW SUBCOLNAM%ROWTYPE;
SUBPARTNAMROW SUBPARTNAM%ROWTYPE;

/* The next five variables are all strings used for building the queries.*/
COLS_C_SEP VARCHAR (8000);
TBLSPC_DEF VARCHAR (8000);
COL_DEF VARCHAR (254);

/* Messaging string to hold text */
MSGSTRING VARCHAR (255);

/* Create to hold various PL/SQL Blocks */
INSERTSQLSTRING DBMS_SQL.VARCHAR2S;
-- DELETESQLSTRING DBMS_SQL.VARCHAR2S;
CREATESQLSTRING DBMS_SQL.VARCHAR2S;
PROBESQLSTRING DBMS_SQL.VARCHAR2S;

/* The next variable holds the entire insert,delete and create operations which
must be broken apart and fed to the DBMS_SQL dynamic SQL engine. */
INSERTSQL VARCHAR (20000);
-- DELETESQL VARCHAR (20000);
CREATESQL VARCHAR (20000);

/* These next two integers are the integers used for creating the cursors
that must be passed to the dynamic SQL engine. */
-- DELCURS PLS_INTEGER;
INSCURS PLS_INTEGER;
CRECURS PLS_INTEGER;

/* The lower and upper bounds for the various queries. */
INS_LB PLS_INTEGER;
INS_UB PLS_INTEGER;
-- DEL_LB PLS_INTEGER;
DEL_UB PLS_INTEGER;
CRE_LB PLS_INTEGER;
CRE_UB PLS_INTEGER;

/* Temp. variables for breaking the queries apart into smaller strings */
ICOUNT PLS_INTEGER;
IVAL PLS_INTEGER;

/* Argument to the DBMS_SQL.parse command telling it not to add new lines
into the concatenated string */
NEW_LINE_FLAG BOOLEAN:=FALSE;

/* Current number of rows deleted for individual SQL delete statement*/
DROWS PLS_INTEGER;

/* Current number of rows inserted for individual insert statement */
IROWS PLS_INTEGER;

/* The name of the schema and owner that the user is logged in as*/
SCHEMA_NAME VARCHAR (8000);
OWNER_IN VARCHAR (8000);

/* Variables for determining the tablespace information */
IS_PART BOOLEAN:=FALSE;
PKTAB VARCHAR (30);
MANIP VARCHAR (100);
YR VARCHAR (4);
MNTH VARCHAR (2);
DY VARCHAR (2);
TMP VARCHAR(8000);
TMP2 VARCHAR(8000);

/*other variables*/
RENAME_CD VARCHAR(254);
POST_DATE DATE;
AGE_CNT PLS_INTEGER;

BEGIN

/*Assign defaults to some variables*/
SCHEMA_NAME := USER;
OWNER_IN := USER;
POST_DATE := POST_DT;

IF NOT CLARITY_UTILITIES.DOES_TABLE_EXIST(OWNER_IN,'TBL_TDL')
THEN
DBMS_OUTPUT.PUT_LINE('TBL_TDL DOES NOT EXIST IN THE SCHEMA: ' || SCHEMA_NAME);
ELSE
IF NOT CLARITY_UTILITIES.DOES_TABLE_EXIST(OWNER_IN,'TBL_TDL_AGE')
THEN
FOR COLSROW IN COLS
LOOP
/*Set the variables based on the values in the cursor*/
COL_NAME := COLSROW.COLUMN_NAME;
DATA_TYPE := COLSROW.DATA_TYPE;
DATA_LEN := COLSROW.DATA_LENGTH;
DATA_PREC := COLSROW.DATA_PRECISION;
DATA_SCAL := COLSROW.DATA_SCALE;
NULLS := COLSROW.NULLABLE;

COL_DEF := COL_NAME || ' ';

/*Determine how the datatype definition should be structured.*/
CASE
WHEN UPPER(DATA_TYPE) LIKE '%CHAR%' THEN
COL_DEF := COL_DEF || DATA_TYPE || '(' || TO_CHAR(DATA_LEN) || ') ';
WHEN UPPER(DATA_TYPE)='NUMBER' THEN
IF DATA_PREC IS NULL THEN
DATA_PREC:=38;
END IF;
COL_DEF := COL_DEF || DATA_TYPE || '(' || TO_CHAR(DATA_PREC) || ',' || TO_CHAR(DATA_SCAL) || ') ';
ELSE
COL_DEF := COL_DEF || DATA_TYPE || ' ';
END CASE;

/*Check to add the NULL/NOT NULL definitions */
IF UPPER(NULLS)='N' THEN
COL_DEF := COL_DEF || 'NOT NULL, ';
ELSE
COL_DEF := COL_DEF || 'NULL, ';
END IF;

/*DBMS_OUTPUT.PUT_LINE(COL_DEF);*/
/*Concatenate the column definitions together*/
COLS_C_SEP := COLS_C_SEP || COL_DEF;
END LOOP;

/*get the primary key name*/
OPEN KCOLS;
FETCH KCOLS INTO KCOLSROW;
CLOSE KCOLS;
KCOL_NAME := KCOLSROW.COLUMN_NAME;

/* get the primarykey tablespace name */
OPEN PKTABSPACE;
FETCH PKTABSPACE INTO PKTABSPACEROW;
CLOSE PKTABSPACE;
PKTAB:=PKTABSPACEROW.TABLESPACE_NAME;

/* determine if the table is partitioned */
OPEN TABPART;
FETCH TABPART INTO TABPARTROW;
CLOSE TABPART;
IF TABPARTROW.PARTITIONED IS NOT NULL AND UPPER(TABPARTROW.PARTITIONED) LIKE 'YE%'
THEN
IS_PART:=TRUE;
END IF;


/*Begin create table statment based on the information we have so far*/
CREATESQL:='CREATE TABLE ' || SCHEMA_NAME || '.TBL_TDL_AGE (' || COLS_C_SEP;
CREATESQL:=CREATESQL || ' CONSTRAINT PK_TBL_TDL_AGE PRIMARY KEY (';
CREATESQL:=CREATESQL || KCOL_NAME || ') USING INDEX TABLESPACE ' || PKTAB || ')';


/*do work if the table is partitioned*/
IF IS_PART
THEN

/* open the partitioning cursor get get the data needed*/
OPEN PART;
FETCH PART INTO PARTROW;
CLOSE PART;

PART_TYPE := PARTROW.PARTITIONING_TYPE;
SUBPART_TYPE := PARTROW.SUBPARTITIONING_TYPE;
PART_CNT := PARTROW.PARTITION_COUNT;
SUBPART_CNT := PARTROW.DEF_SUBPARTITION_COUNT;

/*Create tablespace definitions for range or list pertitioning*/
IF UPPER(PART_TYPE)='RANGE' OR UPPER(PART_TYPE)='LIST'
THEN
OPEN PARTCOLNAM;
FETCH PARTCOLNAM INTO PARTCOLNAMROW;
CLOSE PARTCOLNAM;
PARTCOL_NAME:=PARTCOLNAMROW.COLUMN_NAME;
TBLSPC_DEF:=' PARTITION BY ' || UPPER(PART_TYPE) || '(' || PARTCOL_NAME || ')' || CHR(13)||CHR(10);

/*if there are subpartitions, create those as well*/
IF UPPER(SUBPART_CNT)>0
THEN
OPEN SUBCOLNAM;
FETCH SUBCOLNAM INTO SUBCOLNAMROW;
CLOSE SUBCOLNAM;
SUBCOL_NAME := SUBCOLNAMROW.COLUMN_NAME;

TBLSPC_DEF:=TBLSPC_DEF || ' SUBPARTITION BY HASH (' || SUBCOL_NAME || ') SUBPARTITIONS ' || TO_CHAR(SUBPART_CNT) || CHR(13)||CHR(10) || '(';
ELSE
TBLSPC_DEF:=TBLSPC_DEF || '(';
END IF;

/*Loop for creating partitions*/
FOR PARTHIGHROW IN PART_HIGH
LOOP
PART_NAME := PARTHIGHROW.PARTITION_NAME;
TMP:=' PARTITION ' || UPPER(PART_NAME) || ' VALUES LESS THAN (';
HIGH_VAL := PARTHIGHROW.HIGH_VALUE;
IF UPPER(HIGH_VAL)<>'MAXVALUE'
THEN
MANIP := SUBSTR(HIGH_VAL,(INSTR(HIGH_VAL,'''')+1),11);
YR := SUBSTR(MANIP,2,4);
MNTH := SUBSTR(MANIP,7,2);
DY := SUBSTR(MANIP,10,2);
TMP:=TMP || 'TO_DATE(''' || MNTH || '/' || DY || '/' || YR || ''',''MM/DD/YYYY''))';
IF SUBPART_CNT=0
THEN
PRTTBLSPC_NAME := PARTHIGHROW.TABLESPACE_NAME;
TMP:=TMP || ' TABLESPACE ' || PRTTBLSPC_NAME || ',' || CHR(13)||CHR(10);
ELSE
TMP2:=CHR(13)||CHR(10) || '(' || CHR(13)||CHR(10);

/*Loop to create subpartitions*/
FOR SUBPARTNAMROW IN SUBPARTNAM
LOOP
SUBPART_NAME := SUBPARTNAMROW.SUBPARTITION_NAME;
SPTBLSPC_NAME := SUBPARTNAMROW.TABLESPACE_NAME;
TMP2 := TMP2 || ' SUBPARTITION ' || SUBPART_NAME || ' TABLESPACE ' || SPTBLSPC_NAME || ',' || CHR(13)||CHR(10);
END LOOP;
TMP2 := SUBSTR(TMP2, 1, LENGTH (TMP2) - 3) || CHR(13)||CHR(10) || '),';
END IF;
TMP := TMP || TMP2;
ELSE
/*Create the partition/subparitions for maxvalue*/
IF SUBPART_CNT=0
THEN
PRTTBLSPC_NAME := PARTHIGHROW.TABLESPACE_NAME;
TMP:=TMP || 'MAXVALUE) TABLESPACE ' || PRTTBLSPC_NAME || CHR(13)||CHR(10);
ELSE
TMP:=TMP || 'MAXVALUE)';
TMP2:=CHR(13)||CHR(10) || '(' || CHR(13)||CHR(10);
FOR SUBPARTNAMROW IN SUBPARTNAM
LOOP
SUBPART_NAME := SUBPARTNAMROW.SUBPARTITION_NAME;
SPTBLSPC_NAME := SUBPARTNAMROW.TABLESPACE_NAME;
TMP2 := TMP2 || ' SUBPARTITION ' || SUBPART_NAME || ' TABLESPACE ' || SPTBLSPC_NAME || ',' || CHR(13)||CHR(10);
END LOOP;
TMP2 := SUBSTR(TMP2, 1, LENGTH (TMP2) - 3) || CHR(13)||CHR(10) || ')';
END IF;
TMP := TMP || TMP2;
END IF;
TBLSPC_DEF:=TBLSPC_DEF || TMP;
END LOOP;
TBLSPC_DEF:=TBLSPC_DEF || CHR(13)||CHR(10) || ')' || CHR(13)||CHR(10) || 'ENABLE ROW MOVEMENT';
END IF;
/*add partitioned tablespace info to Create SQL statement*/
CREATESQL:=CREATESQL || TBLSPC_DEF;
ELSE
/*add tablespace info to Create SQL statement for non-partitioned table*/
CREATESQL:=CREATESQL || ' PCTFREE 10 PCTUSED 40 INITRANS 8 MAXTRANS 255 TABLESPACE ' || UPPER(TABPARTROW.TABLESPACE_NAME);
END IF;

/* ICOUNT := 0;
IVAL := 1;
Create the array of varchar2 values for the deletion
WHILE IVAL < LENGTH(CREATESQL) LOOP
ICOUNT := ICOUNT + 1;
CREATESQLSTRING(ICOUNT) := SUBSTR(CREATESQL,IVAL,IVAL+199);
CREATESQL := SUBSTR(CREATESQL,201,LENGTH(CREATESQL));
END LOOP;
CRE_LB := 1;
CRE_UB := ICOUNT;

ELSE
/* Insert statement to move records to the new table*/

SAVEPOINT TABLE_MIGRATE;
DROWS := 0;
IROWS := 0;
INSERTSQL := 'insert /*+ APPEND */ into TBL_TDL_AGE';
INSERTSQL := INSERTSQL || ' SELECT * FROM TBL_TDL';
INSERTSQL := INSERTSQL || ' WHERE DETAIL_TYPE>59 AND POST_DATE <= ''' || POST_DATE || '''';

ICOUNT := 0;
IVAL := 1;
/* Create the array of varchar2 values for the insertion */
WHILE IVAL < LENGTH(INSERTSQL) LOOP
ICOUNT := ICOUNT + 1;
INSERTSQLSTRING(ICOUNT) := SUBSTR(INSERTSQL,IVAL,IVAL+199);
INSERTSQL := SUBSTR(INSERTSQL,201,LENGTH(INSERTSQL));
END LOOP;
INS_LB := 1;
INS_UB := ICOUNT;

/* Perform the actual insert operation */
INSCURS := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(INSCURS,INSERTSQLSTRING,INS_LB,INS_UB,NEW_LINE_FLAG,DBMS_SQL.NATIVE);
IROWS := DBMS_SQL.EXECUTE(INSCURS);
DBMS_SQL.CLOSE_CURSOR(INSCURS);
MSGSTRING := CAST(IROWS AS VARCHAR2) || ' ROWS WERE INSERTED INTO TBL_TDL_AGE';
DBMS_OUTPUT.PUT_LINE(MSGSTRING);



ICOUNT := 0;
IVAL := 1;
Create the array of varchar2 values for the deletion
WHILE IVAL < LENGTH(DELETESQL) LOOP
ICOUNT := ICOUNT + 1;
DELETESQLSTRING(ICOUNT) := SUBSTR(DELETESQL,IVAL,IVAL+199);
DELETESQL := SUBSTR(DELETESQL,201,LENGTH(DELETESQL));
END LOOP;
DEL_LB := 1;
DEL_UB := ICOUNT; */

/*Perform the actual deletion operation
DELCURS := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(DELCURS,DELETESQLSTRING,DEL_LB,DEL_UB,NEW_LINE_FLAG,DBMS_SQL.NATIVE);
DROWS := DBMS_SQL.EXECUTE(DELCURS);
DBMS_SQL.CLOSE_CURSOR(DELCURS);*/

MSGSTRING := CAST(DROWS AS VARCHAR2) || ' ROWS WERE DELETED FROM TBL_TDL';
DBMS_OUTPUT.PUT_LINE(MSGSTRING);

IF (DROWS=IROWS)
THEN
IF DROWS=0
THEN
MSGSTRING :='THERE WERE NO RECORDS TO MIGRATE FROM TBL_TDL';
ELSE
MSGSTRING := CAST(IROWS AS VARCHAR2) || ' RECORDS SUCCESSFULLY MIGRATED TO TBL_TDL_AGE';
END IF;
DBMS_OUTPUT.PUT_LINE(MSGSTRING);

SELECT COUNT(TDL_ID) INTO AGE_CNT FROM TBL_TDL WHERE DETAIL_TYPE>59;
MSGSTRING := CAST(AGE_CNT AS VARCHAR2) || ' AGING RECORDS ARE LEFT TO MIGRATE TO TBL_TDL_AGE';
DBMS_OUTPUT.PUT_LINE(MSGSTRING);

IF AGE_CNT=0
THEN
RENAME_CD := 'RENAME TBL_TDL TO CLARITY_TDL_TRAN';
EXECUTE IMMEDIATE RENAME_CD;
DBMS_OUTPUT.PUT_LINE('TBL_TDLRENAMED TO TBL_TDL_TRAN');
END IF;
COMMIT;
ELSE
MSGSTRING := 'RECORDS DELETED DID NOT EQUAL RECORDS INSERTED. ROLLING BACK TBL_TDL_AGE MIGRATION';
DBMS_OUTPUT.PUT_LINE(MSGSTRING);
ROLLBACK TO TBL_TDL_MIGRATE;
END IF;
END IF;
END IF;
END;

 

[taupirho]

After this line

INSERTSQL := INSERTSQL || ' WHERE DETAIL_TYPE>59 AND POST_DATE <= ''' || POST_DATE || '''';

You need to add

INSERTSQL := INSERTSQL || ' AND ROWNUM <= 100';




In order to understand recursion, you must first understand recursion.

 

[Sahubba]

Thats Great!!!! I thought it was there but since I wasnt 100% I wasnt sure!
Thanks alot!