| Using Native Dynamic Sql [message #392781] |
Thu, 19 March 2009 05:01  |
kbeeharry
Messages: 4
Registered: March 2009
|
Junior Member |
|
|
All Users,
Below is a code listing whereby Native Dynamic Sql has been used. If static Sql is used, the forms procedure is successfully compiled (commented part). Else when using Native Dynamic Sql (OPEN c_CodeRec FOR Str_Query USING sStrSrch; ),
it gives an error message like :
This feature is not supported in client-side programs.
Any idea about why oracle is giving this compilation error !.
The code list :
DECLARE
TYPE Cur_Type IS REF CURSOR;
c_CodeRec Cur_Type;
Str_Query VARCHAR2(200);
choice NUMBER;
sCode VARCHAR2(30);
sStrSrch VARCHAR2(30);
nCntRow NUMBER;
BEGIN
:CODE_DESC := NULL;
IF :PARAM.CODE_SET1 = 'REGIMES' THEN
IF :CODE IS NOT NULL THEN
Str_Query := 'SELECT REG_CODE, REG_DESC ';
Str_Query := Str_Query||'FROM REGIMES ';
Str_Query := Str_Query||'WHERE REG_CODE LIKE :j AND ';
Str_Query := Str_Query||' TRUNC(SYSDATE) BETWEEN TRUNC(REG_DT_FROM) AND TRUNC(REG_DT_TO)';
sStrSrch := TRIM(:CODE)||'%';
nCntrow := 0;
BEGIN
-- OPEN c_CodeRec FOR SELECT REG_CODE, REG_DESC
-- FROM REGIMES
-- WHERE REG_CODE LIKE TRIM(:CODE)||'%' AND
-- TRUNC(SYSDATE) BETWEEN TRUNC(REG_DT_FROM) AND TRUNC(REG_DT_TO);
OPEN c_CodeRec FOR Str_Query USING sStrSrch;
LOOP
FETCH c_CodeRec INTO sCode, :CODE_DESC;
EXIT WHEN c_CodeRec%NOTFOUND;
nCntRow := nCntRow + 1;
IF nCntRow > 1 THEN
:CODE_DESC := NULL;
EXIT;
END IF;
END LOOP;
CLOSE c_CodeRec;
IF nCntRow = 0 THEN
RAISE NO_DATA_FOUND;
ELSIF nCntRow = 1 THEN
:CODE := sCode;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Alerts.Stop_Alert('Error: Invalid Regime Code', choice);
RAISE FORM_TRIGGER_FAILURE;
WHEN INVALID_CURSOR THEN
Alerts.Stop_Alert('Error : '||TO_CHAR(SQLCODE)||' '||SQLERRM, choice);
RAISE FORM_TRIGGER_FAILURE;
WHEN OTHERS THEN
IF c_CodeRec%ISOPEN THEN
CLOSE c_CodeRec;
END IF;
Alerts.Stop_Alert('Error : '||TO_CHAR(SQLCODE)||' '||SQLERRM, choice);
RAISE FORM_TRIGGER_FAILURE;
END;
END IF;
END IF;
END;
|
|
|
|
|
|
| Re: Using Native Dynamic Sql [message #392803 is a reply to message #392781] |
Thu, 19 March 2009 05:36  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
| kbeeharry wrote on Thu, 19 March 2009 10:21 |
It gives a compilation error : This feature is not supported in client-side programs.
|
Seems fairly self explanatory. Oracle forms doesn't support dynamic ref cursors.
You can use dynamic ref cursors in database procedures but not in forms.
|
|
|
|
|
|
| Re: Use of Native Dynamic Sql [message #394998 is a reply to message #394915] |
Tue, 31 March 2009 00:18 |
kbeeharry
Messages: 4
Registered: March 2009
|
Junior Member |
|
|
Yah,
I'm new to oracle forms but I know what exactly I want.
It's solved now. Here is the package body. There is a tricky way to call either the procedure or function. Find out the other half of the soluton.
PACKAGE BODY ProcSqlStmt IS
PROCEDURE pr_OpenCursor(p_SqlStmt IN VARCHAR2, p_SqlCursor IN OUT SYS_REFCURSOR) IS
sBlk VARCHAR2(1000);
BEGIN
sBlk := 'BEGIN '||
' OPEN :c_RefCur FOR '||p_SqlStmt||';'||
'END;';
EXECUTE IMMEDIATE sBlk USING IN OUT p_SqlCursor;
END pr_OpenCursor;
--
FUNCTION fn_OpenCursor(p_SqlStmt IN VARCHAR2, p_SqlCursor IN SYS_REFCURSOR) RETURN SYS_REFCURSOR IS
c_RefCur SYS_REFCURSOR;
sBlk VARCHAR2(1000);
BEGIN
c_RefCur := p_SqlCursor;
sBlk := 'BEGIN '||
' OPEN :c_RefCur FOR '||p_SqlStmt||';'||
'END;';
EXECUTE IMMEDIATE sBlk USING IN OUT c_RefCur;
RETURN (c_RefCur);
END fn_OpenCursor;
END ProcSqlStmt;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
