Javascript, ODBC, and Oracle functions returning cursors

R

Roger Redford

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!
 
A

Ana C. Dent

(e-mail address removed) (Roger Redford) wrote in
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!
Click to expand...

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****************************
 
G

Gregory Toomey

Roger said:
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?
Click to expand...

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
 
J

Jeff North

On 20 Aug 2004 16:32:37 -0700, in comp.lang.javascript
| 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.
Click to expand...

[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();
%>
 
R

Roger Redford

Thanks for responding. To answer the question, these newsgroups
were the ones that I found at groups.google.com as groups that had
discussions on the keywords that I searched on.
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.
Click to expand...

Right. The question was more, what is possible, and the exact
syntax to do it.

Thanks for the code.
 
R

Roger Redford

Had you read the detailed post properly, you would have noticed:

1) that I had -already- included an example of how to return
rows from a cursor

2) the question is how to integrate this function (returning
a cursor) with javascript.


You don't seem illiterate. You -are- illiterate.
 
S

Sympatico Member

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 said:
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!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DBD::Oracle, tracing, and stored procedures... 2
Draw a real-time flot chart by fetching data from database and store it into an array using JavaScri 0
Final chapter of "Learn PHP, MySQL and JavaScript" 3
ANN: eGenix mxODBC 3.2.0 - Python ODBC Database Interface 0
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. 7
Can I pass BLOB to stored function using DBD::Oracle? 0
ANN: eGenix mxODBC Connect 2.1.0 - Python ODBC Database Interface 0
Oracle - java - get xmlType col into resultset 7

Members online

No members online now.
Total: 1,995 (members: 1, guests: 1,994)
Robots: 196

Forum statistics

Threads
474,164
Messages
2,570,901
Members
47,439
Latest member
elif2sghost

Latest Threads

Top