Stored procedures in Oracle

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.

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

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

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

Didn't understand. Works fine for me here with Oracle 8.16. To see this try exec get_cfgname

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"?

Let me see your latest sp first

Do a
SELECT TEXT FROM USER_SOURCE WHERE NAME='GET_CFGNAME'

from sqlplus and cut and paste for me pls.

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!

and what happens when you do

exec GET_CFGNAME from sqlplus

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

try this to exec your procedure from your asp:

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

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

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

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.