Stored procedures in Oracle

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

 
sieglejAsked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
Points reduced to 0 and placed in paq per the questioner's request.

Thank you
Computer101
Community Support Moderator
0
 
jitgangulyCommented:
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
 
jitgangulyCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sieglejAuthor Commented:
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
 
sieglejAuthor Commented:
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
 
jitgangulyCommented:
Didn't understand. Works fine for me here with Oracle 8.16. To see this try exec get_cfgname
0
 
sieglejAuthor Commented:
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
 
jitgangulyCommented:
Let me see your latest sp first
0
 
jitgangulyCommented:
Do a
SELECT TEXT FROM USER_SOURCE WHERE NAME='GET_CFGNAME'

from sqlplus and cut and paste for me pls.
0
 
sieglejAuthor Commented:
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
 
jitgangulyCommented:
and what happens when you do

exec GET_CFGNAME from sqlplus
0
 
sieglejAuthor Commented:
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
 
choroqueCommented:
try this to exec your procedure from your asp:

<%
sql = "EXEC GET_CFGNAME('myparameter1','myparameter2'); END;"
dbconn.execute(sql)
%>
0
 
sieglejAuthor Commented:
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
 
sieglejAuthor Commented:
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
 
sieglejAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.