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;

-------------Problems Reply------------

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 Views:3 Time:2018-11-07
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--

  • Set Hidden Field value to Javascript Variable using Jquery or Javascript 2010-01-14

    Very Brief Background: I am using Jquery Autocomplete to lookup the the value of an item from a database. That value is then somehow given to a hidden field within the same form and then inserted to the database. What complicates this slightly is tha

  • Update a Table by Passing Table Name, ColumnName as a Variable Using Cursor 2011-09-21

    I want to Update a Table. I am getting Table Name and Column Name as a XML DataSet from the Front End. I have written One Cursor for that. But it throws error. Below is my Cursor set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[P

  • creating a new variable using two columns when they satisfy certain conditions using R 2010-09-03

    I am providing this example data to get my question across. aid=c(1,2,3,4,5,6,7,8,9,10) foson=c(0,1,2,0,6,9,0,0,3,0) fosof=c(0,0,2,3,0,0,0,5,0,0) data=data.frame(aid,foson,fosof) Now, I need to create a new variable (column) named data$hist with the

  • SQL Concat Values from second table into one column 2012-02-29

    I have two SQL tables Entries(entryid,name,date) Entry Files(fileid,entryid,filename,dateadded) Im trying to write a stored procedure to return everything from Entries and for each row a concatenated list of the files for that entry. from reading thr

  • sql, selecting the lastest completed couse using max (column name) not working 2010-07-21

    Every year employee takes mandatory courses. I need to get a list of employees and their latest completed course, for example FS (fire safety). Below is my query. The problem is it shows two or three completed courses for some employees. Trying to tr

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

processed in 0.094 (s). 11 q(s)