?
Solved

Stored procedures in Oracle

Posted on 2001-06-29
16
Medium Priority
?
939 Views
Last Modified: 2012-05-04
I'm trying to execute a stored procedure in Oracle.  I've seen the similar questions but can't seem to get either the ASP or stored procedure syntax correct.  Here's my code:
Dim adCmdStoredProc,adInteger,adVarChar,adParamInput,adParamReturnValue,adParamOutput
adCmdStoredProc = 4
adInteger = 3
adVarChar = 200
adParamInput = 1
adParamReturnValue = 4
adParamOutput = 2

Set objCmd = Server.CreateObject("ADODB.Command")
Set objParm = Server.CreateObject("ADODB.Parameter")

' Set teh command object properties
SetConnection()   'create and set the connection to oracle
Set objCmd.ActiveConnection = oConn
objCmd.CommandText = "get_cfgname"
objCmd.CommandType = adCmdStoredProc

' Set up the parameter to receive the return value
Set objParm = objCmd.CreateParameter("cname",adVarChar,adParamReturnValue,20,"NoCFG")
objCmd.Parameters.Append objParm

objCmd.Execute
' check for errors
if oConn.Errors.Count > 0 then
     Response.Write "Error execting SP"
end if
' Display the results
Response.Write "SP returned: " &objCmd.Parameters.Item("cname").Value &"<BR>"
' Close and dereference objects
Set objParm = Nothing
Set objCmd = Nothing
oConn.Close
Set oConn = Nothing


Here's my simple SP for testing:
create or replace procedure get_cfgname (cname out varchar2) is
     begin
          select name into cname from config_param where config_id = '2';
     end;

I get "ORA-06550: line 1, column 17: PLS-00306: wrong number or types of arguments in call to 'GET_CFGNAME' ORA-06550: line 1, column 7: PL/SQL: Statement ignored "

as if it wants to see cname in the cmdtext input but I can't get the syntax correct.  Also, do I need '@' in my oracle code to get the values passed, or is this SQL server syntax.  I've seen a lot of different syntaxes for this like {call proc(arg1,arg2) }  too, but cant' get this stuff to play. Basically I need some clues for syntax on parameter passing for Oracle stored procedures.
Thanks

 
0
Comment
Question by:sieglej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 20

Expert Comment

by:jitganguly
ID: 6238753
SP is wrong. Correct it first

Here is the corrected one

create or replace procedure get_cfgname (cname out varchar2) as
    begin
         select name as cname from config_param where config_id = '2'
    end;

Is config_id a charatcer field, then its ok to put quotes around

First create it SQLPlus and run it like start get_cfgname.

0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6238769
Make sure this SP has permission to execute. In other words what login Id you use when you connect from ASP to oracle.
You may want to provide some Grant statement like

GRANT ALL ON  get_cfgname TO youruserid ;
from sqlplus
0
 

Author Comment

by:sieglej
ID: 6238851
I tried the new syntax for the sp but sqlplus didn't like it.  I assume the key was "name as cname" but it grouses about wanting an "into" statement.  It also needs the ';' at the end of the query.  Also, I'm creating the sp in sqlplus with the same login as I'm running the ASP with.  
The sp seems to execute ok in sqlplus when I do:
declare cname varchar2(20);
begin
  get_cfgname(cname);
end;
/
btw, how do I check the contents of cname in sqlplus at that point?
Still no luck here,
THanks,
jeff
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sieglej
ID: 6238854
I tried the new syntax for the sp but sqlplus didn't like it.  I assume the key was "name as cname" but it grouses about wanting an "into" statement.  It also needs the ';' at the end of the query.  Also, I'm creating the sp in sqlplus with the same login as I'm running the ASP with.  
The sp seems to execute ok in sqlplus when I do:
declare cname varchar2(20);
begin
  get_cfgname(cname);
end;
/
btw, how do I check the contents of cname in sqlplus at that point?
Still no luck here,
THanks,
jeff
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6238881
Didn't understand. Works fine for me here with Oracle 8.16. To see this try exec get_cfgname
0
 

Author Comment

by:sieglej
ID: 6238971
I'm running database ver 8.1.6 too.  Exec get_cfgname grouses about not having an argument for 'get_cfgname'.  What did you mean by "To see this try exec get_cfgname"?
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6238978
Let me see your latest sp first
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6238988
Do a
SELECT TEXT FROM USER_SOURCE WHERE NAME='GET_CFGNAME'

from sqlplus and cut and paste for me pls.
0
 

Author Comment

by:sieglej
ID: 6239002
TEXT
----------------------------------------------------------------------------------------------------
procedure get_cfgname (cname out varchar2) as
 begin
  select name into cname from config_param where config_id = '2';
 end;


Thanks for your help!
0
 
LVL 20

Expert Comment

by:jitganguly
ID: 6239007
and what happens when you do

exec GET_CFGNAME from sqlplus
0
 

Author Comment

by:sieglej
ID: 6239020
SQL> exec get_cfgname
BEGIN get_cfgname; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_CFGNAME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
0
 
LVL 1

Expert Comment

by:choroque
ID: 6243614
try this to exec your procedure from your asp:

<%
sql = "EXEC GET_CFGNAME('myparameter1','myparameter2'); END;"
dbconn.execute(sql)
%>
0
 

Author Comment

by:sieglej
ID: 6256347
The problem seems to be that I'm trying to return a value from my procedure which, in Oracle is not a procedure but a function.  I'm not sure the nomenclature people refer to in ASP for "stored procedures" but the only way I got Oracle script to run was as a function (i.e. "create or replace function....").  The only way I found to run it from ASP was as "select func(parm) from dual" not "exec func(parm)".  Unfortunately, it still doesn't fill in the return parameter.  Here's the Oracle function:

create or replace test1 (id in varchar2) return varchar2 as
begin
declare cname varcher2(20);
   begin
      select name into cname from config_param where config_id=id;
      return cname;
   end;
end test1;

The relevant ASP changes are:
objCmd.CommandText = "select test1(2) from dual";
objCmd.CommantType = adCmdText

Set objParm = objCmd.CreateParameter("cname",adVarChar,adParmamReturnValue,20,"NoCFG")
objCmd.Parameters.Append objParm

This runs with no errors, but cname doesn't get filled in. Does anybody ever return values from Oracle stored procedures ("functions")? I've only seen examples for SQL Server. Or do I have to save the results in a table and read them out with a query?
Thanks,
Jeff
0
 

Author Comment

by:sieglej
ID: 6257365
I finally got it to work this way.  Here's the SP (note you can skip the local cname declare and the return if you list cname as an output in the call header - use adParamOutput in ASP - then you can have multiple return values!...):
create or replace function get_cfgname(id in varchar2) return varchar2 as
begin
declare cname varchar2(20);
     begin
          select name into cname from config_param where config_id=id;
          return cname;
     end;
end get_cfgname;

and the ASP code:

Dim adCmdStoredProc,adInteger,adVarChar,adParamInput,adParamReturnValue,adParamOutput
adCmdStoredProc = 4
adInteger = 3
adVarChar = 200
adParamInput = 1
adParamReturnValue = 4
adParamOutput = 2

Set objCmd = Server.CreateObject("ADODB.Command")
Set objParm = Server.CreateObject("ADODB.Parameter")
Set rs = server.CreateObject("ADODB.Recordset")     ' if you want to return a recordset from a SP

' Set teh command object properties
SetConnection()
Set objCmd.ActiveConnection = oConn
objCmd.CommandText = "get_cfgname"
objCmd.CommandType = adCmdStoredProc

' Set up the parameter to receive the return value
' Use this method if you use a "Return cname;" in the stored function
Set objParm = objCmd.CreateParameter("cname",adVarChar,adParamReturnValue,20,"NoCFG")
' Use this  method if you use the (..... cname out varchar) as ..."   method
'Set objParm = objCmd.CreateParameter("cname",adVarChar,adParamOutput,20,"NoCFG")
objCmd.Parameters.Append objParm

Set objParm = objCmd.CreateParameter("id",adVarChar,adParamInput,20,"2")
objCmd.Parameters.Append objParm
objCmd.Parameters.Item("id").Value = 8

set rs = objCmd.Execute
' check for errors
if oConn.Errors.Count > 0 then
     Response.Write "Error execting SP"
end if
' Display the results
Response.Write "No error reported <BR>"
'Response.Write "No errors reported: " &rs("name") &"<BR>"        ' for use when returning a rs
Response.Write "SP returned: " &objCmd.Parameters.Item("cname").Value &"<BR>"
' Close and dereference objects
Set objParm = Nothing
Set objCmd     = Nothing
set rs          = nothing
oConn.Close
Set oConn = Nothing
%>

Thanks,
Jeff
0
 

Author Comment

by:sieglej
ID: 6257752
Slight clarification.  Oracle can do procedures that return a value but they must be done using parameters, not with the "Return" statement.  My main problem was using adParamReturnValue instead of adParamOutput for the return parameter. A function must be used if "Return <value>" is used in Oracle.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 6257880
Points reduced to 0 and placed in paq per the questioner's request.

Thank you
Computer101
Community Support Moderator
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question