DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Catching oracle procedure exception in a .NET C# application

634402Apr 16 2008 — edited Jan 27 2011

In sql server 2000, I would catch sql error codes and throw a new message to the users? I was wondering (in oracle) what the error codes are and if anyone has a custom error message class for oracle 9i?

This is an example of the approach in SQL Server:

virtual public DataTable ExecuteDataTable() {
DataTable dt = null;
try {
Prepare ("ExecuteDataTable");

SqlDataAdapter a = new SqlDataAdapter(this.Command);
dt = new DataTable();
a .Fill(dt);

TraceResult ("# Rows in DataTable = " + dt.Rows.Count);
} catch (SqlException e) {
throw SomeExceptionFunction(e);
} finally {
CloseOpenedConnection ();
}

return dt;
}

protected Exception SomeExceptionFunction (SqlException ex) {
....... (code )

...... (code)

if (dalException == null) {
// uses SQLServer 2000 ErrorCodes
switch (ex.Number) {
case 17:
// SQL Server does not exist or access denied.
case 4060:
// Invalid Database
case 18456:
// Login Failed
dalException = new DalLoginException(ex.Message, ex);
break;
case 547:
// ForeignKey Violation
dalException = new DalForeignKeyException(ex.Message, ex);
break;
case 1205:
// DeadLock Victim
dalException = new DalDeadLockException(ex.Message, ex);
break;
case 2627:
case 2601:
// Unique Index/Constriant Violation
dalException = new DalUniqueConstraintException(ex.Message, ex);
break;
default:
// throw a general DAL Exception
dalException = new DalException(ex.Message, ex);
break;
}
}

// return the error
return dalException;
}

aroumeli

Hi,
We require the same: returning raw json from pl/sql procedure. Did you find a solution?

Aaron L.

Hope an amateur like me can help......
To POST json to a clob column we do the following:

INSERT INTO js_table
 (js_column)
VALUES
 (blob_to_clob(:body));

oracle-base.com has a version of the blob_to_clob sql
If you just want to return the json clob, the SELECT-statement User_Z87BQ works for us.
We have pl/sql that build the json by using something like the following:

BEGIN
  APEX_JSON.OPEN_OBJECT;
  APEX_JSON.WRITE('result','success');
  APEX_JSON.WRITE('message','hello world');
  APEX_JSON.CLOSE_OBJECT;
END;

thatJeffSmith-Oracle

just print it with htp.p

aroumeli

That's brilliant Jeff, thank you!
I'm using:
OWA_UTIL.mime_header('application/json', TRUE);
in order to set the correct content-type. Is this the correct way to go?

User_2JVWU

For anyone having the same issue: you could simply return the column which holds JSON as a plain JSON type by using a column name like "{}columnname", see:

SELECT id,
       json "{}json"
  FROM table_with_json

ref: https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

User_2JVWU

For anyone having the same issue: you could simply return the column which holds JSON as a plain JSON type by using a column name like "{}columnname", see:

SELECT id,
       json "{}json"
  FROM table_with_json

ref: https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

Olafur T

Create GET handler, type PL/SQL
Some examples of creating output:

begin
  owa_util.status_line(201, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"status": "Item created successfully"}');
end;

begin
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"items":[');
  for i in (select rownum as rn, object_Type, object_name, status from all_objects where owner = 'ORDS_METADATA' fetch next 10 rows only) loop
    if i.rn > 1 then
      htp.prn(',');
    end if;
    htp.prn('{"type": "' || i.object_type || '"');
    htp.prn(',"name": "' || i.object_name || '"');
    htp.prn(',"status": "' || i.status || '"}');
  end loop;
  htp.prn(']}');
end;

declare
  l_arr json_array_t := json_array_t();
begin
  for i in (select object_Type, object_name, status from all_objects where owner = 'ORDS_METADATA' fetch next 10 rows only) loop
    declare
      l_obj json_object_t := json_object_t();
    begin
      l_obj.put('type', i.object_type);
      l_obj.put('name', i.object_name);
      l_obj.put('status', i.status);
      l_arr.append(l_obj);
    end;
  end loop;
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  apex_util.prn(l_arr.to_clob);
end;

Regards
Oli

Olafur T

And for posting your CLOB, Jeff has already shown how to do a GET media resource.
This is an alternative way to do POST in PL/SQL, I'm assuming it's an array, change as needed:

 begin
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"items":');
  for i in (select jsonclob from mytable where id = :id) loop
    apex_util.prn(i.jsonclob);
  end loop; 
end;

of course under production, this would be a select into with a no_data_found handler

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 24 2011

Added on Apr 16 2008

#net-stored-procedures, #windows-and-net

1 comment

7,072 views

DevOps, CI/CD and Automation

Catching oracle procedure exception in a .NET C# application

Comments

Post Details