Home » Developer & Programmer » Forms » Using Native Dynamic Sql
Using Native Dynamic Sql [message #392781] Thu, 19 March 2009 05:01 Go to next message
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: Use of Native Dynamic Sql [message #392801 is a reply to message #392781] Thu, 19 March 2009 05:35 Go to previous messageGo to next message
ramyazhary
Messages: 7
Registered: March 2009
Junior Member
i have the same problem

any ideas anyone ??
Re: Using Native Dynamic Sql [message #392803 is a reply to message #392781] Thu, 19 March 2009 05:36 Go to previous messageGo to next message
cookiemonster
Messages: 12415
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 #394915 is a reply to message #392801] Mon, 30 March 2009 17:56 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

Is this your first form? What are you trying to do? Please place your code between code tags.

David
Re: Use of Native Dynamic Sql [message #394998 is a reply to message #394915] Tue, 31 March 2009 00:18 Go to previous message
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;
Previous Topic: ora-06503 when trying to save a file using Win_API_Dialog.Save_File
Next Topic: Form to Ms Access database
Goto Forum:
  


Current Time: Wed Dec 07 16:23:36 CST 2016

Total time taken to generate the page: 0.06425 seconds