Executing a Procedure

[Sahubba]

Hello I just created a procedure but its not working.
I was wondering if I had to execute it?

here is the code

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 || '''';
INSERTSQL := INSERTSQL || ' AND ROWNUM <= 100';

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;

 

[SantaMufasa]

Sahubba,

Whoever owns the procedure has all rights on a database object. If an Oracle user does not own an object, then that user needs to have privileges that GRANT the necessary access.

Could you please comment on the procedure as it relates to my assertions, above?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[LKBrwnDBA]

How did you create it?
Did you execute the statement you posted? (It's missing a slash '/' at the end).
Do you get an error?
How do you know it's not working?




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

Sahubba,

Its generally a 2 stage process.

1) You "introduce" your procedure to the database

$ sqlplus mydb/myuser @the_name_of_the_file_containg_your_prrocedure_code_goes_here.sql


2) After the database has gotten to know your procedure, it can take it out for "a spin"


$ sqlplus mydb/myuser
execute your_procedure_name_goes_here_along_with_any_parameters_it_might_need;


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