Re: Javascript, ODBC, and Oracle functions returning cursors

dba_222_at_yahoo.com (Roger Redford) wrote in news:a8c29269.0408201532.76f8cab1_at_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;
>
> 1
>
>
> -----------------------
> 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
>
> -----------------------
> 2)
>
> 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()}
>
> 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
>
> -----------------------
>
> 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}
>
> 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
>
>
> -----------------------
>
> 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!
>

Why in the world are you spamming every language newgroup under the SUN? comp.databases.oracle.server,comp.lang.javascript,comp.lang.java.api,comp.l ang.basic.visual.database,comp.lang.perl.misc

Either your ODBC or JS code is FUBAR. PL/SQL does know know or care about the language of the code which calls it. PL/SQL is too dumb to know or care about the calling language.

--****************************Start PL/SQL****************************

CREATE OR REPLACE PACKAGE Calc AS

Function Add5 (num in number) RETURN NUMBER;

END Calc;

/

CREATE OR REPLACE PACKAGE BODY Calc AS

Function Add5 (num in number) RETURN NUMBER IS

Begin

Return (num + 5);

End Add5;

END Calc;

/

--****************************End PL/SQL****************************

'****************************Start VB Code****************************

Dim con As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim InputParam As New ADODB.Parameter

Dim ReturnParam As New ADODB.Parameter

con.ConnectionString = "DSN=ODBC8; UID=scott; PWD=tiger;"

con.Open

cmd.ActiveConnection = con

cmd.CommandText = "Calc.Add5"

cmd.CommandType = adCmdStoredProc

Set InputParam = cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30)

'Using adParamOutPut instead of adParamReturnValue will result in the following error:

'ORA-24334 - no descriptor for this position

'Set Prm2 = cmd.CreateParameter("Prm2", adSmallInt, adParamOutput)

Set ReturnParam = cmd.CreateParameter("Prm2", adSmallInt, adParamReturnValue)

'You will also get the ORA-24334 error if you don't Append the parameters

'in the correct order. Make sure to bind the Returning parameter first.

cmd.Parameters.Append ReturnParam

cmd.Parameters.Append InputParam

cmd.Execute

MsgBox "Input Value = " & cmd.Parameters(1)

MsgBox "Return Value = " & cmd.Parameters(0)

'****************************End VB Code****************************