By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Javascript, ODBC, and Oracle functions returning cursors

P: n/a
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!
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
db*****@yahoo.com (Roger Redford) wrote in
news:a8**************************@posting.google.c om:
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****************************

Jul 23 '05 #2

P: n/a
Roger Redford wrote:
Oracle is my specialty, the back end mainly, .... 2)
Does anyone have experience getting a number of rows back
with an Oracle cursor? Is this possible?


For somebody who specialises in Oracle, but does not know about getting rows
from a cursor, your come across as a prize idiot.

Please dont bother us in comp.lang.perl.misc.

gtoomey
Jul 23 '05 #3

P: n/a
On 20 Aug 2004 16:32:37 -0700, in comp.lang.javascript
db*****@yahoo.com (Roger Redford) wrote:
| 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.


[snip 2 end]

Javascrit comes in 2 flavours: client-side (that appears within the
webpage). This can only manipulate items on the webpage. It can not
open files or databases.

Server-side: also known as JScript. This allows you to do all the file
/ database manipulation stuff. Check with your web people as to what
web server they are running. If IIS then ASP/JScript, ASP/VBScript or
PHP will do. If any other server then check what server-side language
they support.

Sample listed below:
-----------------------------------------------
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/ELVASS.asp" -->
<%
//--- get data from the webpage form
var tpid = Request.QueryString("tpid");
var qid = Request.QueryString("qid");
if( isNaN(tpid) || isNaN(qid) ) Response.Redirect("../menu.asp");

//--- global variables
var fname="", fExt="", TPName="", QName="", Ver="", AppDt="",
mainID=0;
var fso, f2;
var src="", dst = "", wrd="";

function WriteFormData( mainid, cid, type, fname )
{
//--- use this syntax for insert, update and delete queries
var w = Server.CreateObject('ADODB.Command');
w.ActiveConnection = MM_ELVASS_STRING;
w.CommandText = "INSERT INTO forms (fk_mainID, fk_cid,
type,filename) Values (" + mainid + "," + cid + ",\"" + type + "\",\""
+ fname + "\")";
w.Execute();
w.ActiveConnection.Close();
}

//--- get general information
//--- use this syntax for select queries
var db = Server.CreateObject("ADODB.Recordset");
db.ActiveConnection = MM_ELVASS_STRING;
db.Source = "SELECT main.id form main WHERE fk_TrainingPackage=" +
tpid + " AND fkQualID=" + qid;
db.CursorType = 0;
db.CursorLocation = 2;
db.LockType = 1;
db.Open();
while( !db.EOF )
{
//--- do something
db.moveNext();
}
db.Close();
%>
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #4

P: n/a
Use JDeveloper free from the Oracle.com site, don't need code, it's like a
gian wizard writing the code for you.

"Roger Redford" <db*****@yahoo.com> wrote in message
news:a8**************************@posting.google.c om...
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!

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.