Anyone have experience to relate on using synonyms of External SQL Script files in Oracle?

I'd like to know what features of Oracle's SQL dialect it supports, and which not.

For instance, does it understand --

• Subquery factoring ("WITH ..." preceding the main SELECT)
  
• Nonscalar functions
  

-- at least sufficiently to identify the answer-set columns, and generate proper queries based on the script?

This message has been edited. Last edited by: Kerry,

quote:

Subquery factoring

I would suggest trying with a simple example? For subquery factoring, I was able to run a FOCEXEC using this SQL:

ENGINE SQLORA SET DEFAULT_CONNECTION MYORA
SQL SQLORA PREPARE SQLOUT FOR
with generator as (
    select
        rownum id
    from dual
    connect by
        rownum <= 4000
)
select
    /*+ gather_plan_statistics */
    count(*)
from    (
    select
        /*+ no_merge */
        rownum id
    from
        generator,
        generator
    where
        rownum <= 1000
    )
;
END
TABLE FILE SQLOUT
PRINT 
     COUNT___
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

I do recall a error [(FOC1405) SQL PREPARE ERROR] being thrown using CASE statements that seemed to require them to be wrapped in parentheses as a work-around. For example:

Produces an Error:
CASE WHEN…. THEN…. END AS COLUMN

No Error:
(CASE WHEN…. THEN…. END) AS COLUMN

Hope this helps...

Haven't run into anything that is a problem using ENGINE SQLORA SET DEFAULT_CONNECTION MYORA
SQL SQLORA PREPARE SQLOUT FOR
, other than these few querks;

- Nested CASE statements, might have to put those in line.

- For comments Use -* not -- but /* */ works

- May need to cast some vars

- Don't allow functions to create their own field names, they can quickly exceed the available witdh

There are a few off the top of my head.

That's useful info, with regard to SQL Pass-Thru.

But my question was about

quote:

using synonyms of External SQL Script files

-- i.e., you store a sql script on the WF server, create a synonym thereof, and use it in TABLE (or GRAPH or MATCH) FILE.

• If the first word of the script is WITH (rather than SELECT), will the synonym-generator reject it out-of-hand?
  
  
• The feature description prohibits "comments", does that preclude Hint comments?
  

Unfortunately, at the moment I am not in position to test this.

When you create a synonym for an external SQL script the requirement documented in Managing ORACLE Metdata is

quote:

A Synonym candidate can be any file that contains one (and only one) valid SQL Query and does not contain end-of-statement delimiters (";" or "/") and comments.

A WITH clause preceding the SQL is OK. For example a synonym can be created for this simple query and then used for retrieval:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   scott.emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   scott.emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno

This simple query works; there is just one SELECT statement and it's last.

This functionality has been enhanced in Release 7.7.05 to support more complex queries.