Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Oracle Metadata
 Login/Join
 
Gold member
posted
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?


Dev Studio /7.6.11/7.7.02M
MVS/USS
AIX/SOLARIS
Windows WF Client 7.6.8/7.6.11
 
Posts: 64 | Location: Eastern and middle NC | Registered: March 13, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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


Personal: Windows 7.7.02
Clients: Variety
 
Posts: 20 | Location: Cincinnati, OH | Registered: February 17, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
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?


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Reply With QuoteReport This Post
Master
posted Hide Post
Piipster. That is interesting. I assume if you use Dynamic Columns, then using the GUI is out. Everything must be manually coded, correct?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
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


Dev Studio /7.6.11/7.7.02M
MVS/USS
AIX/SOLARIS
Windows WF Client 7.6.8/7.6.11
 
Posts: 64 | Location: Eastern and middle NC | Registered: March 13, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
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.


George D. Brown
Loyola University Chicago
gbrown2@luc.edu
 
Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.