MarkJ
World Chat Champion
Joined: 11 Apr 2006
Karma :  
|
 Posted: 11:40 - 28 Feb 2011  Post subject: Retrieving SQL error variable from ASP |
 |
|
Morning all,
We're making an ASP website at work which connects to an Oracle SQL database. I've managed to get it to display the data from the table we're using, and input data into the table, however there is no error trapping. My boss has made a SQL package that when run in SQL inserts into the table. When the package is run, the output variable is either an integer if the insert succeded, or a string (saying "error, blah blah blah") if there was an error. This variable is written to dbms_output.
The procedure is as such (I've stripped a few lines out as it's quite long)
| Code: | | procedure create_gr(p_surname varchar2, p_forename VARCHAR2, p_birth_year INTEGER, .... p_result out VARCHAR2); |
This is the actual code executed in the ASP page;
| Code: | 'Create Connection
SET myConn=SERVER.createobject("adodb.connection")
myConn.Open "DSN=ODBCCONN;"
'Create string with SQL command to run
SQLStr="begin declare v_res varchar2(200); begin gr_package.create_gr('" & strforename & "','" & strsurname & "'," & stryob & .... ,v_res);dbms_output.put_line('the inserted id is '||v_res);end;end;"
Response.Write("SQLStr is " & SQLStr)
Response.Write("<P>New Line Added</P>")
SET result=myConn.execute(SQLStr) |
Now if all the code is fine then it inserts fine, but I don't get anything back as it's being written to dbms_output.
It looks to be as though SET is running the SQL command, but is there a way of getting back the variable 'v_res'?
I am an absolute noob when it comes to SQL and ASP. I was pretty good at programming Java when I was at Uni but that was a few years ago. |
|
huskie69
Spanner Monkey
Joined: 10 Nov 2010
Karma : 
|
| Posted: 15:36 - 28 Feb 2011 Post subject: |
|
|
Not sure if this is any help... I'm an Oracle DBA but have zero experience when it comes to ASP. However, Looking at your code, I would change the dbms_output.put_line calls to utl_file.put_line calls and set up utl_file and open a file first. You'll then need to cat/tail (or whatever Windows alternative you have on the host) the file in another session. Depending on you Oracle version (9.2+), you can use the autoflush option to write to the buffer and immediately flush the contents to the file.
____________________
'10 Skyjet SJ125-23 - Crashed  > '09 YBR 125 > '53 Suzuki SV650 - Died > 2010 Suzuki VanVan 125 > '87 Yamaha FZ750 Sold > VTR 1000 FireStorm - Sold > Honda CB1000R |
|
supZ
World Chat Champion
Joined: 03 Feb 2009
Karma : 
|
|
Help & FAQs
Search BCF
Members
Groups
Events Calendar
rating
rating