Solved

Stored procedures in Oracle

Posted on 2001-06-29
16
927 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
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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Join 9 53
Syntax Help on SP 4 52
How to update a dummy recordset with classic ASP 4 52
Update field in order 21 109
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now