Re: Insert using an Oracle stored procedure

From: Turkbear (noone_at_nowhere.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 08:54:51 -0600

I sent another poster some info about how we code SP calls in asp ( you need more than just the Sql you have, I
believe):
Here is that info.
Anyway, this is how we populate parameters (from a user entry form) and call the SP ( in this case called
:SNOW_AND_ICE_REPORT_BUILD)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rid=Request.Form("rnbr")
Begdate=Request.Form("dc1")
EndDate=Request.Form("dc2")
Jnbr=Request.Form("jnum")
Onbr=Request.Form("orgnum")
jrsp=Request.Form("jrcd")
act=Request.Form("actv")
jt=Request.Form("torj")
wonbr=Request.Form("won")
SqlStr = "DECLARE RetVal NUMBER;BEGIN RetVal := SNOW_AND_ICE_REPORT_BUILD ("
SqlStr = SqlStr & rptNbr & ", '"
SqlStr = SqlStr & Jnbr & "', '" & jrsp & "','" & act & "','"
SqlStr = SqlStr & Onbr & "','" & jt & "', 'Y', 'N', 'N'"
If wonbr <> "" then
SqlStr = SqlStr & "," & Cstr(wonbr) & ",'" & BegDate & "','" & EndDate & "')"
Else
SqlStr = SqlStr & ", NULL,'" & BegDate & "','" & EndDate & "')"
End If
SqlStr = SqlStr & ";COMMIT;END; "
  conn.Execute(SqlStr)

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
It may give some ideas on coding it..
hth

On 19 Mar 2004 04:57:53 -0800, andyza@webmail.co.za (Luis) wrote:

>I'm using the following code to insert a new record into an Oracle
>8.1.7 database using the stored procedure below:
>
>set conn = Server.CreateObject("ADODB.Connection")
>conn.open connStr
>SQLString = "TABLENAME_INSERT_SP(COLID => TABLENAME_SEQ.NEXTVAL,
>COLNAME => '" & tmpName & "', COLSNAME => '" & tmpSname & "',
>COLADDRESS => '" & tmpAddress & "', COLINSTITUTION => '" &
>tmpInstitution & "', COLEDUCATION => '" & tmpEducation & "',
>COLCOMPLETED => '" & tmpCompleted & "', COLLEVEL => '" & tmpLevel &
>"', COLCREDITS => '" & tmpCredits & "')"
>conn.execute(SQLString)
>
>The stored procedure is similar to:
>
>PROCEDURE TABLENAME_INSERT IS
>
> COLID VARCHAR(50);
> COLNAME VARCHAR(50);
> COLSNAME VARCHAR(20);
> COLADDRESS VARCHAR(50);
> COLINSTITUTION VARCHAR(50);
> COLEDUCATION VARCHAR(20);
> COLCOMPLETED VARCHAR(3);
> COLLEVEL NUMBER(4);
> COLCREDITS NUMBER(4);
>
>BEGIN
> INSERT INTO TABLENAME
> (COLID,COLNAME,COLSNAME,COLADDRESS,COLINSTITUTION,COLEDUCATION,COLCOMPLETED,COLLEVEL,COLCREDITS)
> VALUES
> (COLID,COLNAME,COLSNAME,COLADDRESS,COLINSTITUTION,COLEDUCATION,COLCOMPLETED,COLLEVEL,COLCREDITS);
> COMMIT;
>END;
>
>
>When I run the asp code I get the following error:
>
>"Microsoft OLE DB Provider for Oracle (0x80040E14)
>ORA-00900: invalid SQL statement"
>
>What is wrong with my SQL statement?
>
>Could it be a problem with my connection string?

Relevant Pages

  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... It appears that it was hitting the stored procedure, ... Is there any tool in the SQL Profiler that analyze each Trace? ... The maximum amount of time for a script to execute was exceeded. ... do it at ASP. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... other improvements I could do at the ASP page? ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: ASP, looping, and stored procedures.... error 800a0bb9 ...
    ... I'm just going to include the .asp page here. ... > If your bottleneck turns out to be occurring in the ASP code itself rather ... > stored procedure, then you may want to think about utilizing GetRows ... >> Oh, and by the way, the test box has SQL server and IIS on it. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
Loading