PL/SQL assinging values to a variable using cursor columns

Question

I am trying to take some statistics against the Emp table, the create table & the rows inserted are given below. I am trying to develop a store procedure which will get all the columns for a particular table from oracle ALL_TAB_COLUMNS & I will generate the statistics.

The PL/SQL block of code given below is compiling but not returning any records when I run it. Can anyone please let me know where I might be getting wrong -

`is "distinct_cnt := 'SELECT COUNT(DISTINCT (' || table_rec.COLUMN_NAME || ')) FROM' || table_rec.TABLE_NAME;"`

a correct way of assigning the result to a variable.

create table emp( empno  number(4,0),
ename    varchar2(10),
job      varchar2(9),
mgr      number(4,0), 
hiredate date, 
sal      number(7,2),
comm     number(7,2), 
deptno   number(2,0)
)

insert into emp values( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
insert into emp values( 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values( 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20);
insert into emp values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
insert into emp values( 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);

 Commit;


create or replace
procedure p_profiling (V_tablename IN varchar2)
IS

cursor c1 is
select TABLE_NAME,
COLUMN_NAME
from
ALL_TAB_COLUMNS
where TABLE_NAME='V_tablename';


REC_CNT NUMBER;
distinct_cnt NUMBER;
is_valid NUMBER;
not_null NUMBER;

BEGIN

FOR table_rec in c1
LOOP

REC_CNT := 'SELECT COUNT(*) FROM' ||  table_rec.TABLE_NAME;

distinct_cnt := 'SELECT COUNT(DISTINCT (' || table_rec.COLUMN_NAME || ')) FROM' || table_rec.TABLE_NAME;

is_valid := 'SELECT COUNT(*) FROM '||table_rec.TABLE_NAME ||'WHERE'|| table_rec.COLUMN_NAME ||' IS NOT NULL
            AND LENGTH('||table_rec.COLUMN_NAME||') = LENGTH(LTRIM(RTRIM('||table_rec.COLUMN_NAME||')))';

not_null := 'SELECT COUNT(*)  FROM'|| table_rec.TABLE_NAME ||'WHERE '|| table_rec.COLUMN_NAME ||'IS NOT NULL';

DBMS_OUTPUT.PUT_LINE ('REC_CNT:'||REC_CNT||' '||'distinct_cnt:'||distinct_cnt||' '||'is_valid:'||is_valid ||'  '||'TABLE_NAME'||table_rec.TABLE_NAME||' '||'COLUMN_NAME'||table_rec.COLUMN_NAME);

END LOOP;

END;

It seems highly unlikely that this is running without error. You're declaring a bunch of numeric variables and then assigning them various string values that are sometimes valid SQL statements. You should be getting all sorts of runtime errors that you should be mentioning... You'd need to use dynamic SQL to execute the SQL statements you are dynamically assembling. — Justin Cave, Jun 10 '15 at 17:41

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

You need to use EXECUTE IMMEDIATE in order to execute these dynamics queries an also to obtain the data you are looking for.

Review this post: dynamic SELECT INTO clause in PL/SQL

Hope this help.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 10 '15 at 17:41

Leonardo Heis

109
6

score 0 · Accepted Answer · answered Jun 12 '15 at 07:22

For executing the SQL statements created dynamically, you need to use EXECUTE IMMEDIATE:

create or replace
procedure p_profiling (V_tablename IN varchar2)
IS

cursor c1 is 
select TABLE_NAME,
COLUMN_NAME
from
ALL_TAB_COLUMNS
where TABLE_NAME='V_tablename';


REC_CNT NUMBER;
distinct_cnt NUMBER;
is_valid NUMBER;
not_null NUMBER;


BEGIN

FOR table_rec in c1

LOOP

IF c1%ROWCOUNT = 1 THEN 

EXECUTE IMMEDIATE  'SELECT COUNT(*) FROM' ||  
table_rec.TABLE_NAME INTO  REC_CNT;

END IF;

EXECUTE IMMEDIATE  'SELECT COUNT(DISTINCT (' || 
table_rec.COLUMN_NAME || ')) FROM' || 
table_rec.TABLE_NAME INTO distinct_cnt;

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||
table_rec.TABLE_NAME ||'WHERE'|| table_rec.COLUMN_NAME ||' 
IS NOT NULL
AND LENGTH('||table_rec.COLUMN_NAME||') = 
LENGTH(LTRIM(RTRIM  ('||table_rec.COLUMN_NAME||')))' INTO is_valid;

EXECUTE IMMEDIATE  'SELECT COUNT(*)  FROM'|| 
table_rec.TABLE_NAME ||'WHERE '|| table_rec.COLUMN_NAME 
||'IS NOT NULL' INTO not_null;


DBMS_OUTPUT.PUT_LINE('REC_CNT:'||REC_CNT||' '||'distinct_cnt:'||
distinct_cnt||' '
||'is_valid:'||is_valid   ||'  '||'TABLE_NAME'||table_rec.TABLE_NAME||' '
||'COLUMN_NAME'||table_rec.COLUMN_NAME);

END LOOP;

END;

PL/SQL assinging values to a variable using cursor columns

2 Answers2