Здравствуйте, OldRick, Вы писали:

OR>Как получить результат табличной функции ??

OR>как выполнить запрос для ОЛАПа (Oracle 9i r2)

OR>execute DBMS_AW.execute('AW ATTACH xademo')
OR>execute DBMS_AW.PRINTLOG(DBMS_AW.INTERPCLOB('REPORT geography'))
OR>execute DBMS_AW.execute('AW DETACH xademo')

OR>или, что одно и тоже

OR>execute DBMS_AW.execute('AW ATTACH xademo;REPORT geography;AW DETACH xademo')

OR>и получить результат ?
OR>у меня Delphi говорит ошибку ORA-00900 Invalid SQL statement

OR>использую dbExpress или ODAC
2 Moderators Извините, много получилось.
2 OldRick Из вьюхи во втором примере данные выбрать сможешь я надеюсь ... А вообще RTFM.

Methods of Accessing Multidimensional Data From SQL
There are several ways that SQL can access the multidimensional data of an analytic workspace. An abstract data type and the table functions underlie all of them. The method that you choose depends on how you want to use the data.
Use the CWM2_OLAP_AW_ACCESS PL/SQL package to define a star schema of dimension views and fact views, which represent the measures, dimensions, hierarchies, and attributes in the analytic workspace. You can then query these views using standard SQL SELECT statements. (This package is part of CWM2 because it is also used to make workspace objects accessible to the OLAP catalog metadata.)
If you are using the CNV_CWM.TO.ECM OLAP DML program to create an analytic workspace, you can also use it to generate relational views of your workspace data. The CNV_CWM.TO.ECM program employs the CWM2_OLAP_AW_ACCESS package for this stage.

Write object type definitions and then make relational queries and views of the analytic workspace data by using the OLAP_TABLE function in SQL SELECT statements. This method is more complex than using the CWM2_OLAP_AW_ACCESS package (which is a wrapper to this technology), but it provides more flexibility and power in an application than using predefined views.
See Also: Chapter 9, "Creating an Analytic Workspace From Relational Tables"
Chapter 10, "DBMS_AW"
Chapter 15, "CWM2_OLAP_AW_ACCESS"

Example: Script for the Product View
This PL/SQL command uses the /users/oracle/mapfiles/product.txt input file shown in "Product Dimension View" to generate a script named /users/oracle/scripts/product.sql. The resulting view will be named electro_product_view.

CALL CWM2_OLAP_AW_ACCESS.CREATEAWACCESSSTRUCTURES_FR(
'/users/oracle/scripts/', 'product.sql', 'electro_product_',
'scott.electronics', '/users/oracle/mapfiles/', 'product.txt');
Before executing the script, you may edit it.

--product.sql
--Generated on: 15-FEB-2002 09:16:42am
SET ECHO ON
SET LINESIZE 200
SET PAGESIZE 50
SET SERVEROUT ON
DROP TYPE electro_product_TBL;
DROP TYPE electro_product_OBJ;
CREATE TYPE electro_product_OBJ AS OBJECT (
PRODUCT VARCHAR2(16),
PROD_PARENT VARCHAR2(16),
PROD_GID NUMBER(10),
PRODP_GID NUMBER(10),
EQUIPMENT VARCHAR2(16),
COMPONENT VARCHAR2(16),
DIVISION VARCHAR2(16),
TOTALPROD VARCHAR2(16),
PROD_LONG VARCHAR(32),
PROD_SHORT VARCHAR(16));
/
CREATE TYPE electro_product_TBL AS TABLE OF electro_product_OBJ;
/
CREATE OR REPLACE FUNCTION electro_product_LMAP RETURN VARCHAR2 IS
--This function will return the following Limit Map:
--DIMENSION PRODUCT FROM PRODUCT
-- WITH HIERARCHY PROD_PARENT FROM PRODUCT.PARENTREL
-- GID PROD_GID FROM PRODUCT.GID
-- PARENTGID PRODP_GID FROM PRODPARENT.GID
-- LEVELREL EQUIPMENT, COMPONENT, DIVISION, TOTALPROD FROM
PRODUCT.HIERHEIGHT USING PRODUCT.LVLDIM
-- ATTRIBUTE PROD_LONG FROM PRODUCT.LONGLABEL
-- ATTRIBUTE PROD_SHORT FROM PRODUCT.SHORTLABEL
vRetVal VARCHAR2(443) := '';
BEGIN
vRetVal := vRetVal || 'DIMENSION PRODUCT FROM PRODUCT ';
vRetVal := vRetVal || 'WITH HIERARCHY PROD_PARENT FROM PRODUCT.PARENTREL ';
vRetVal := vRetVal || 'GID PROD_GID FROM PRODUCT.GID ';
vRetVal := vRetVal || 'PARENTGID PRODP_GID FROM PRODPARENT.GID ';
vRetVal := vRetVal || 'LEVELREL EQUIPMENT, COMPONENT, DIVISION, TOTALPROD
FROM PRODUCT.HIERHEIGHT USING PRODUCT.LVLDIM ';
vRetVal := vRetVal || 'ATTRIBUTE PROD_LONG FROM PRODUCT.LONGLABEL ';
vRetVal := vRetVal || 'ATTRIBUTE PROD_SHORT FROM PRODUCT.SHORTLABEL';
RETURN vRetVal;
END electro_product_LMAP;
/
SHOW ERRORS;
CREATE OR REPLACE VIEW electro_product_VIEW AS SELECT * FROM
TABLE(CAST(OLAP_TABLE('scott.electronics DURATION QUERY', 'electro_product_TBL',
'', electro_product_LMAP())AS electro_product_TBL));
--The command below should be modified to provide appropriate security to
Analytic Workspace data.
--GRANT SELECT ON electro_product_VIEW TO PUBLIC;
--End of file: product.sql
Example: Product View
The script shown in "Example: Script for the Product View" creates a view named ELECTRO_PRODUCT_VIEW, which has the following definition:

SELECT "PRODUCT", "PROD_PARENT", "PROD_GID", "PRODP_GID" "EQUIPMENT",
"COMPONENT","DIVISION, "TOTALPROD", "PROD_LONG", "PROD_SHORT"
FROM TABLE(CAST (OLAP_TABLE('scott.electronics DURATION QUERY',
'electro_product_TBL', '', electro_product_LMAP()) AS electro_product_TBL))
Use a command like the following to access data about products from the electronics analytic workspace:

select product, prod_long, prod_short from electro_product_view
where prod_gid=0;
PRODUCT PROD_LONG PROD_SHORT
---------------- -------------------------------- ----------------
PORTCD Portable CD Player Port CD
PORTST Portable Stereo Port Stereo
PORTCAS Portable Cassette Port Cassette
TUNER Tuner Tuner
.
.
.
METALCAS Metal Cassette Mtl Cassette
STNDCAS Standard Cassette Std Cassette
STNDVHSVIDEO Standard VHS Video VHS Video
8MMVIDEO 8MM Video 8MM Video
HI8VIDEO Hi 8 Video Hi8 Video
22 rows selected.