I am trying to use oracle hint /*+ USE_HASH(Q1,SHRTCKN) ORDERED */ in a query using passthru. when I run the procedure, I keep getting a number of script errors and then the page shows only a reset button.

When I remove the hint, the sql passthru works fine.

Please advice.

This message has been edited. Last edited by: <Kathryn Henning>,

Did you try executing same SQL along with HINT in oracle client?

Are you able to execute without any issues there?

Thanks,
Ram

Hi Ritu,

Welcome to the forum.

We're using Oracle hint in SQLPASSTHRU and works perfect.

ENGINE SQLORA SET DEFAULT_CONNECTION CONNECTION_NAME
SQL SQLORA PREPARE SQLOUT FOR
SELECT /*+ parallel(a,4) */ DISTINCT LTRIM(COLUMN_NAME) AS SAMPLE FROM TABLENAME
;
END
TABLE FILE SQLOUT 
PRINT *
END

Is that running fine in Oracle Client as Ram questioned?

Thanks,
Rifaz

The query runs fine in SQL Developer and Toad. Also when I remove the hint the query runs fine in pass thru.

Following is the query
SELECT SFRSTCR_PIDM,
SSBSECT_SUBJ_CODE,
SSBSECT_CRSE_NUMB,
SSBSECT_SEQ_NUMB,
SSBSECT_SUBJ_CODE || ' ' || SSBSECT_CRSE_NUMB || SSBSECT_SEQ_NUMB AS COURSE,
MIN(SFRSTCR_TERM_CODE),
SHRTCKG_GRDE_CODE_FINAL
FROM (SELECT /*+ USE_HASH(Q1,SHRTCKN) ORDERED */ DISTINCT SFRSTCR_PIDM,
SFRSTCR_TERM_CODE,
SSBSECT_SUBJ_CODE,
SSBSECT_CRSE_NUMB,
CASE WHEN SSBSECT_SEQ_NUMB LIKE '%CC' THEN ' CC'
WHEN SSBSECT_SEQ_NUMB LIKE '%E' THEN ' E'
WHEN SSBSECT_SEQ_NUMB LIKE '%I' THEN ' I'
WHEN SSBSECT_SEQ_NUMB LIKE '%S' THEN ' S'
ELSE '' END AS SSBSECT_SEQ_NUMB,
SHRTCKG_GRDE_CODE_FINAL
FROM SFRSTCR
JOIN SSBSECT
ON SFRSTCR_CRN = SSBSECT_CRN
AND SFRSTCR_TERM_CODE = SSBSECT_TERM_CODE
LEFT OUTER JOIN SHRTCKN
ON SSBSECT_CRN = SHRTCKN_CRN
AND SSBSECT_TERM_CODE = SHRTCKN_TERM_CODE
AND SSBSECT_SUBJ_CODE IN ('ENG', 'MAT', 'INT', 'CAR', 'REA')
AND SHRTCKN_PIDM = SFRSTCR_PIDM
LEFT OUTER JOIN (SELECT SHRTCKG_PIDM,
SHRTCKG_TCKN_SEQ_NO,
SHRTCKG_TERM_CODE,
SHRTCKG_GRDE_CODE_FINAL
FROM SHRTCKG M
WHERE SHRTCKG_SEQ_NO IN (SELECT MAX(SHRTCKG_SEQ_NO)
FROM SHRTCKG N
WHERE M.SHRTCKG_PIDM = N.SHRTCKG_PIDM
AND M.SHRTCKG_TCKN_SEQ_NO = N.SHRTCKG_TCKN_SEQ_NO
AND M.SHRTCKG_TERM_CODE = N.SHRTCKG_TERM_CODE
AND SHRTCKG_TERM_CODE >= 200909)) Q1
ON Q1.SHRTCKG_TCKN_SEQ_NO = SHRTCKN.SHRTCKN_SEQ_NO
AND Q1.SHRTCKG_PIDM = SHRTCKN.SHRTCKN_PIDM
AND Q1.SHRTCKG_TERM_CODE = SHRTCKN.SHRTCKN_TERM_CODE
AND NVL (SHRTCKN.SHRTCKN_REPEAT_COURSE_IND, '!') <> 'E'
WHERE SFRSTCR_RSTS_CODE IN (SELECT STVRSTS_CODE
FROM STVRSTS
WHERE STVRSTS_INCL_SECT_ENRL = 'Y')
AND SFRSTCR_TERM_CODE >= '200909')
WHERE SSBSECT_SUBJ_CODE || ' ' || SSBSECT_CRSE_NUMB || SSBSECT_SEQ_NUMB = '&S_COURSE'
GROUP BY SFRSTCR_PIDM, SSBSECT_SUBJ_CODE, SSBSECT_CRSE_NUMB, SSBSECT_SEQ_NUMB, SHRTCKG_GRDE_CODE_FINAL

Ritu,

Put your hints on a separate line and prefix them with -*

I have used oracle hints. It works perfect in 8005. Can you share the error message that you get while using hints.

Thanks,
Ram

I still get the errors when I put -* before the hint an put in a different line as follows:
..
SHRTCKG_GRDE_CODE_FINAL
FROM (SELECT
-*/*+ USE_HASH(Q1,SHRTCKN) ORDERED */
DISTINCT SFRSTCR_PIDM,
..

-----------------------------
Following are the errors I get:

1.
Line: 118
Char 442
Errors: Syntax error
Code: 0
URL:https://dev-focus-02.dccc.edu/ibi_apps/WFServlet.ibfs

2.
Line: 10
Char 21
Errors: 'jsData' is undefined
Code: 0
URL:https://dev-focus-02.dccc.edu/ibi_apps/ibi_html/S59_13781483881F/javaassist/ibi/html/describe/APHTMLGen.js

3.
Line: 381
Char 18
Errors: 'jsData' is undefined
Code: 0
URL:https://dev-focus-02.dccc.edu/ibi_apps/ibi_html/S59_13781483881F/javaassist/ibi/html/describe/APAutoprompt.js

4.
Line: 1172
Char 13
Errors: 'jsData' is undefined
Code: 0
URL:https://dev-focus-02.dccc.edu/ibi_apps/WFServlet.ibfs

5.
Line: 1145
Char 30
Errors: THe calue of the property 'onLoadDescribeForm' is null or undefined, not a Function Object
Code: 0
URL:https://dev-focus-02.dccc.edu/ibi_apps/WFServlet.ibfs


Thanks,
Ritu