PL/SQL assinging values to a variable using cursor columns

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;

--------------Solutions-------------

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;

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.

Category:oracle Time:2018-11-16 Views:0
Tags: oracle plsql

Related post

  • How can I assign the value of a variable using eval in python? 2011-04-08

    Okay. So my question is simple: How can I assign the value of a variable using eval in Python? I tried eval('x = 1') but that won't work. It returns a SyntaxError. Why won't this work? --------------Solutions------------- Because x=1 is a statement,

  • How I set the 'top' css value as a variable using $(this) 2011-07-06

    How I set the 'top' css value as a variable using $(this) ? Thus far I have not been able to do so with this code: $("#thumb").click(function () { var TopV = $(this).css('top'); alert(TopV) }); --------------Solutions------------- That code snippet i

  • AS3 - Can I detect change of value of a variable using addEventListener? 2008-11-20

    Is it possible to use EventListener to Listen to a variable and detect when the value of that variable changes? Thanks. --------------Solutions------------- This is quite easy to do if you wrap it all into a class. We will be using getter/setter meth

  • How to change the value of a variable using back button in iphone 2010-06-21

    I need to change the value of a variable of the previous view when the default back button is selected. How can I develop this using viewControllers in an iphone application? I tried to do it by creating a custom back button and by set method. I call

  • How to count no of lines in text file and store the value into a variable using batch script? 2011-04-14

    I want to count the no of lines in a text file and then the value has to be stored into a environment variable. The command to count the no of lines is findstr /R /N "^" file.txt | find /C ":" I refered the question How to store the result of a comma

  • Is there a way to determine the potential value of a variable using Roslyn? 2012-04-12

    I am using the Roslyn CTP and I am trying to determine if the value of a variable in a class has a value. Lets say I am trying to detect when someone is using a BinaryExpressionSyntax to determine if a string is equal to nothing "". For example: priv

  • how to check the value of database entry using cursor? 2011-10-21

    i want to check the value of the database content using a cursor. I am able to get the content of the database but i can't correctly check if the data i want to add already exists on the database. Here is a snippet of my code. for(int i=1;i<=curso

  • Trying to initialize 2 values in 2 variables using 1 query, is it possible to do this.? 2009-12-09

    SET @Password = ( SELECT UserPassword,IsLocked FROM [Authentication].[tblLogin] WHERE [email protected]) i m trying to get both values userpassword and islocked in two variables to be used in same SP in next query. Is it possible or do i have to wri

  • Query Oracle for running sql and value of bind variables 2011-02-03

    If I run the SQL in Fig. 1 below, it may return something like this: Select fname, lname from name_tbl where nam_key = :key Without using some fancy DBA trace utility, how can I query an Oracle system table to find the value of the bind variable “:ke

  • Firebird SQL: add value to same variable inside select-into 2011-09-10

    In firebird stored procedure I can use this: select...into :variable1 For knowledge sake only I wonder is there a way when using additional select sql I can "ADD" the returned value (both are numeric) to same variable "variable1" within the select st

  • How to change the value of ${user} variable used in Eclipse templates 2008-10-29

    Instead of hardcoding the default @author template I would like Eclipse to use user's real name taken from account information (in Linux - but Windows solution is also welcome). Entering it somewhere into Eclipse configuration would be acceptable, to

  • Print out value of XSL variable using 2009-04-13

    I'm trying to output a variable's literal string value, after it is being set depending on whether a node exists or not. I think the condition check logic is correct. But it is not outputing the values... <xsl:variable name="subexists"/> <xs

  • Assign table values to multiple variables using a single SELECT statement and CASE? 2010-03-26

    I'm trying to assign values contained in a lookup table to multiple variables by using a single SELECT having multiple CASE statements. The table is a lookup table with two columns like so: [GreekAlphabetastic] SystemID Descriptor -------- ----------

  • Assign value to session variable using javascript 2012-03-22

    I am developing an asp.net web application. I need to use java script to assign variables to the session variable. How can i use javascript to assign a value to a session variable ? --------------Solutions------------- you cannot set session variable

  • How to assign value to LESS variable using selector 2012-03-30

    Since LESS is a pre-compile stylesheet language, this may not achieve what I'm after, but the idea is: #sidebar { width: 40%; } @sidebar_width : @('#sidebar:width'); .some_other_elements { width: @sidebar_width; } So there are three questions I have:

  • Error retrieving bash value into PHP variable using cron 2012-04-07

    I have used cron to run PHP: * * * * * /var/www/html/new12345/testing.sh This bash contains: #!/usr/bin/php -q /var/www/html/new12345/shell_call.php and my shell_call.php has a variable: $a= shell_exec("./main.sh $l"); My main.sh is suppose to return

  • PL/SQL Separate Values in a Variable 2014-10-13

    I have a variable that has a value like the following: v_test := 'REP, MAK' I would like to take the value and return it to the variable like this v_test := 'REP','MAK' Can anyone give me any suggestions on how to accomplish this? I have tried using

  • SQL - Multiple Values comma separated when using GROUP BY 2008-10-24

    This question already has an answer here: How can I combine multiple rows into a comma-delimited list in Oracle? [duplicate] 11 answers I have data that looks like CUSTOMER, CUSTOMER_ID, PRODUCT ABC INC 1 XYX ABC INC 1 ZZZ DEF CO 2 XYX DEF CO 2 ZZZ D

  • Value of a variable using WinDbg 2009-04-16

    Question: How to display the value of a C++ iterator using WinDbg, illustrated below: for (vector<string>::iterator i = args.begin(); i != args.end(); i++) //omitted //for instance: } else if (*i == "-i") {//attempting to display the value of *

  • sql set values for some variables with a select 2009-12-16

    I have something like this declare @foo bigint; declare @bar nvarchar(20); set @foo = select foo from theTable where id = 37; set @bar = select bar from theTable whre id = 37; is it possible to do this with a single select ? --------------Solutions--

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.981 (s). 13 q(s)