OraFAQ Forum: SQL & PL/SQL » PLS-00414: no column ... in table

Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00414: no column ... in table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PLS-00414: no column ... in table [message #2753]

Mon, 12 August 2002 02:11

Chris
Messages: 128
Registered: November 1998

Senior Member

Hello,

I have a problem with PL/SQL.

I want to develop a script that queries a table of an application in several databases.
Depending on what application is found in the database (there is only one per database), the table has a different structure (different column names).

So in my pl/sql I first find out the application and according to that I want to execute a specific query.
So I have a structure like this:

If application is this
do this query
elsif application is that
do another query
...
end if

But the problem is that I get an error with the message that a specific column in the table does not exist. And this is correct: in one pl/sql program I have all queries for all applications, the table name is the same but the columns are different for the different applications.
Although the pl/sql program will not execute a wrong query (because I check first which application is present and then I make sure I execute the correct query) it still gives an error if there is a wrong column name found somewhere in the pl/sql program.

Any idea how I can bypass this problem?

A different pl/sql program for each application is not an option. I want that the pl/sql program to determine the correct application and execute the correct query.

Thanks in advance.

Report message to a moderator

Re: PLS-00414: no column ... in table [message #2765 is a reply to message #2753]

Mon, 12 August 2002 09:32

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

You will need to use dynamic SQL for this so that the procedure can compile in all of the schemas. If you are on 8i or up, you can use execute immediate, otherwise you will use DBMS_SQL.

if v_appname = 'APP1' then
  execute immediate 'select count(*) from t where cola = :x and colb = :y' into v_count using v_x, v_y;
elsif v_appname = 'APP2' then
  execute immediate 'select count(*) from t where colc = :x and cold = :y' into v_count using v_x, v_y;
end if;

Report message to a moderator

Previous Topic:	dbms_utility
Next Topic:	help!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:57:52 CDT 2024