Javascript, ODBC, and Oracle functions returning cursors

From: Roger Redford <dba_222_at_yahoo.com>
Date: 20 Aug 2004 16:32:37 -0700
Message-ID: <a8c29269.0408201532.76f8cab1@posting.google.com>

Dear Experts,

I'm attempting to marry a system to an Oracle 817 datbase. Oracle is my specialty, the back end mainly, so I don't know much about java or javascript.

The system uses javascript to make ODBC calls to the db.

The particular system I'm working with, will not work with an Oracle stored procedure I'm told. However, it will work with a stored function.

I made a simple function in Oracle to return a single integer. It works in sqlplus, but not via javascript.

Create or replace function fnc_rtn_integer Return integer
As
Ln_temp integer := 0;
Begin
  Select count(*)
  Into ln_temp
  From dual;
Return LN_TEMP;

End;

Sqlplus:

declare

ln_temp integer := -1;
begin

ln_temp := fnc_rtn_integer;
dbms_output.put_line (ln_temp);
end;

javascript:

ESC.tb.pop_addr=1;{call fnc_rtn_integer}

Netscape Privledge Manager exception
netscape.security.ForbiddenTargetException: access to target denied davox.host.AnswerSoftDB:Using local character set: 0 : davox.host.AnswerSoftDB:? [State: S1000] [Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

davox.host.AnswerSoftDB:-2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

davox.host.HostConn:get ESC.tb.pop_addr Host -2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

The developers need to get a number of rows back. I've made an Oracle function, that will return a number of rows.

CREATE OR REPLACE FUNCTION fnc_rtn_emp_info RETURN types.ref_cursor
AS
emp_cursor types.ref_cursor;

BEGIN OPEN emp_cursor FOR

SELECT 	empno,
	ENAME,
	JOB
From 	EMP;

RETURN emp_cursor;

END; CREATE OR REPLACE function fnc_dept_rpt
Return types.DeptCurTyp
AS

dept_cv types.DeptCurTyp ;

BEGIN
OPEN dept_cv FOR

	SELECT DEPTNO,
		DNAME,
		LOC 
	FROM DEPT;

	Return dept_cv;

END;

ESC.tb.pop_addr=1;{call FNC_DEPT_RPT()}

davox.host.AnswerSoftDB:-2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

davox.host.HostConn:get ESC.tb.pop_addr Host -2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

ESC.tb.pop_addr=1;{call FNC_DEPT_RPT() AND ESC.tb.pop_addr=1;{call FNC_DEPT_RPT davox.host.AnswerSoftDB:Using local character set: 0 : davox.host.AnswerSoftDB:? [State: S1000] [Oracle][ODBC][Ora]ORA-00911: invalid character

davox.host.AnswerSoftDB:-2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-00911: invalid character

davox.host.HostConn:get ESC.tb.pop_addr Host -2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-00911: invalid character

ESC.tb.pop_addr=1;{call FNC_DEPT_RPT}

davox.host.AnswerSoftDB:-2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

davox.host.HostConn:get ESC.tb.pop_addr Host -2 ? [State: S1000]
[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

A few questions.

1)
I'm sure that this could be just a syntax issue. Any ideas? Please send the exact sytax that it should be.

2)
Does anyone have experience getting a number of rows back with an Oracle cursor? Is this possible? Am I doing it in the right way?

Thanks a lot! Received on Fri Aug 20 2004 - 18:32:37 CDT