Is there a way to point directly to an Oracle table without having a fully defined MFD. That is, something like the MFD or ACX file getting fieldnames directly from Oracle?

Instead of using the TABLE facility which uses the MAS/ACX metadata, you can do SQL passthru directly to the Oracle tables. After that, you reference a temporary table called SQLOUT which has metadata automatically generated based on your SQL answer set.

Here is an example of accessing Oracle Portal tables:

ENGINE SQLORA SET DEFAULT_CONNECTION ORATEST
SQL SQLORA PREPARE SQLOUT FOR
SELECT long_to_varchar2('select sql_statement from portal30.wwv_usr_dynamic_lov$ where lov_name = '||''''||LOV_NAME||'''', 4000) SQL_STATEMENT,
LOV_NAME
FROM PORTAL30.WWV_USR_DYNAMIC_LOV$
WHERE LOV_OWNER = 'CUSTOMER_APP'
;
END
TABLE FILE SQLOUT
PRINT SQL_STATEMENT
BY LOV_NAME
END
-RUN

Is this what you are looking for? Let me know if I can be of assistance.

Sincerely,

Doug L.
DLautzenheiser@PartnerPS.com

You do have an option when generating the synonyms:

Dynamic columns
To specify that the Master File created for the synonym
should not contain column information, select the Dynamic
columns check box.
If this option is selected, column data is retrieved
dynamically from the data source at the time of the request.

I guess the other question is how do you want to make use of this?

Piipster. That is interesting. I assume if you use Dynamic Columns, then using the GUI is out. Everything must be manually coded, correct?

I have used SQL passthru and created temp metadata but in this case I think the [NOCOLS] is what I'm lloking for. It does work with the GUI tools but it does not seem to recognize DBA Security rules. Still testing.

Thanks to all who replied.

Steve - UNC Chapel Hill

quote:

Originally posted by jgelona:
Piipster. That is interesting. I assume if you use Dynamic Columns, then using the GUI is out. Everything must be manually coded, correct?

It depends on what you'd like to do with the gui... if you wanted to simply report off of tables that are defined with dynamic columns there is no issue. The type of things you would not be able to do would be to add a define to the master file since it is pulled dynamically.

You should also be able to do all normal joins and such with dynamic columns.