Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Oracle PL/SQL: How to use a column conditionally as it may not exist in older versions of a table
mkaresh85
post Aug 30 2017, 03:24 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 16-November 13
Member No.: 49,129



I'm trying to write a simple anonymous procedure which tries a perform SELECT queries on a table. Problem is in an older version of this table, few columns did not exist and these new columns were introduced in a newer version of table structure.

** I am checking if this new column exists and then conditionally using SELECT query if the only columns exists. But seems due to static compilation it reports:- PL/SQL: ORA-00904: "ERRORTYPE": invalid identifier**

How do I handle this? Below is the sample procedure,

SET SERVEROUTPUT ON;
DECLARE
errorType_column_exists number := 0;
BEGIN
SELECT COUNT(*) INTO errorType_column_exists FROM ALL_TAB_COLS WHERE owner = 'DEV' AND table_name = 'Table1' AND column_name = 'ERRORTYPE';
IF (errorType_column_exists = 1) THEN
FOR r_row IN (SELECT ErrorMsg FROM DEV.Table1 WHERE (ErrorType != 'Missing' OR ErrorType IS NULL) AND ROWNUM <= 100) LOOP
DBMS_OUTPUT.PUT_LINE(r_row.ErrorMsg);
END LOOP;
END IF;
END;
/
Go to the top of the page
 
+Quote Post
mkaresh85
post Aug 30 2017, 05:51 AM
Post #2


Newbie
*

Group: Members
Posts: 4
Joined: 16-November 13
Member No.: 49,129



I had posted the same question here https://stackoverflow.com/questions/4595504...ist-in-older-ve
The recommended option is to use Dynamic SQL. Thanks.
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Aug 31 2017, 07:24 AM
Post #3


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Karesh,

I read through the stack overflow post and I also agree that dynamic SQL is the only way to solve your problem.

The below script is what I've framed (Similar to the one in stack overflow).

CODE
DECLARE
errorType_column_exists NUMBER := 0;
l_varr1 sys.odcivarchar2list;
BEGIN
SELECT COUNT(*)
INTO errorType_column_exists
FROM ALL_TAB_COLS
WHERE owner = 'DEV'
AND table_name = 'Table1'
AND column_name = 'ERRORTYPE';
IF (errorType_column_exists = 1) THEN
EXECUTE immediate 'SELECT ErrorMsg
FROM DEV.Table1
WHERE (ErrorType != ''Missing''
OR ErrorType IS NULL)
AND ROWNUM <= 100' bulk collect INTO l_varr1;
FOR i IN 1..l_varr1.count()
LOOP
DBMS_OUTPUT.PUT_LINE(l_varr1(i));
END LOOP i;
END IF;
END;
/


There is a misconception in stack overflow stating "Code has to be compiled before it can be run. The code inside your loop will only compile in a schema where TABLE1 has a column called error_type.

The only way to write an anonymous block which will compile against all versions of the schema is to use dynamic SQL.
". This is actually wrong!

The dynamic part of the code will be compiled only during the RUN time. In our case, only when IF (errorType_column_exists = 1) THEN is satisfied, the dynamic SQL will be compiled and executed.


Cheers,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd September 2017 - 10:11 PM