Make sure you start the listener control on the server. This could be one of the problems. Some times it happens that the name of the server host in the tnsnames.ora file on the client side is not recognized, substitute this with the IP address of the server. Make sure you have the correct service name of the database in the tnsnames.ora file on the server as well as the client.
Make sure you are trying to connect to the databse this way.
I hope this helps you.
Thanks for the replies. The code is actually within this thread further up the web page. I felt this was an ASP problem because one of the db guys over here said that the "OraOLEDB error '80004005'" related to something at the ASP level. He said to check the ASP code first although my gut instinct tells me that Oracle may be playing up?
Thanks for replying Tony. The procedure is just too long in this case and I have removed the bulk of the code. I have also ommited the exceptions code for compactness.
I would stress though that under both pi_transaction_type 1 and 2 there are no calls being made to a REF CURSOR and are merely data manipulation code. Such a call to the REF CURSOR is only made when pi_transaction_type is neither 1 nor 2 as can be seen at the bottom of the IF-ELSE statement.
PROCEDURE new_contributor ( pi_usr_id IN contrib.cont_usr_id%TYPE,
pi_email_freq IN contrib.cont_email_freq%TYPE,
pi_con_start_date IN VARCHAR2,
pi_con_finish_date IN VARCHAR2,
pi_status IN contrib.cont_status%TYPE,
pi_faculty_spc_id IN contrib.cont_faculty_spc_id%TYPE,
pi_section_spc_id IN contrib.cont_section_spc_id%TYPE,
pi_sab_start_date IN VARCHAR2,
pi_sab_finish_date IN VARCHAR2,
pi_notes IN contrib.cont_notes%TYPE,
pi_email_volume IN contrib.cont_email_volume%TYPE,
pi_transaction_type IN NUMBER,
po_contr_record OUT ref_contributor_details,
po_status_code OUT NUMBER )
/* Variable declaration */
IF( pi_transaction_type = 1 ) THEN
/* do something */
ELSIF( pi_transaction_type = 2 ) THEN
/* do something else*/
/* Get The Contributors Details And Pass Back As A Ref Cursor To Calling Environment */
OPEN po_contr_record FOR SELECT * FROM contrib WHERE cont_usr_id = pi_usr_id;
In the case of my ASP page pi_transaction_type is 2 so no REF CURSOR is returned I believe. Should settings in the Oracle 9i client need changing to get it working? What I can say is that our previous 8i client had no problems with this procedure. Thanks in advance.
OK, so if you call this procedure with pi_transaction_type set to 1 or 2 then the REF CURSOR is not opened. If your ASP then tries to fetch a record from the REF CURSOR you will get the ORA-24338 error - looks like that's exactly what is happening.
Here is a simple example run in SQL Plus:
create or replace package pkg is
type t_rc is ref cursor;
procedure get( rc out t_rc );
create or replace package body pkg is
procedure get( rc out t_rc )
null; /* Does not open the cursor */
SQL> REM Declare a ref cursor variable
SQL> var rc refcursor
SQL> REM Run the procedure
SQL> exec pkg.get(:rc)
PL/SQL procedure successfully completed.
SQL> REM Try to access the ref cursor
SQL> print :rc
ORA-24338: statement handle not executed