This forum is currently locked. You can't register or post questions at this time. (read more)

What is the last SQL executed in PL/SQL block

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

matspring
Member
Posts: 1
Joined: Thu Mar 27, 2014 2:04 pm

What is the last SQL executed in PL/SQL block

Postby matspring » Thu Mar 27, 2014 2:10 pm

Hi

I have PL/SQL that inserts into a table but error because of a TO_CHAR on a value that is already a CHAR. The exception is handled by WHEN OTHERS and an entry is made into a trace table. I'd like to however include in the trace the INSERT statement that has failed. Is this possible without writing some code that SELECTs from v_$sql table. I was hoping for something akin to SQL%ROWCOUNT etc

Any help appreciated

Mat

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: What is the last SQL executed in PL/SQL block

Postby Tim... » Thu Mar 27, 2014 4:49 pm

Hi.

Unfortunately not. There is no simple solution for this...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ranitb
Member
Posts: 5
Joined: Tue Apr 08, 2014 11:04 am

Re: What is the last SQL executed in PL/SQL block

Postby ranitb » Tue Apr 08, 2014 1:49 pm

Hi Mat,

I am not sure about understanding your requirement completely, but do you need something like "DML Error Logging" technique.
Read here - http://www.oracle-base.com/articles/10g ... -10gr2.php

Using that, your entire transaction won't fail and at the same time the error log table will hold the value which raised the error.

Thanks and Regards,
-- Ranit

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: What is the last SQL executed in PL/SQL block

Postby Tim... » Tue Apr 08, 2014 3:35 pm

Hi.

No. DML Error loggin will not help. He wants to capture the SQL that caused the exception in his PL/SQL, not just the fact the exception has been raised. DML error logging has nothing to do with this.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

ranitb
Member
Posts: 5
Joined: Tue Apr 08, 2014 11:04 am

Re: What is the last SQL executed in PL/SQL block

Postby ranitb » Wed Apr 09, 2014 7:41 am

Thanks Tim.

My bad, now I got his concern.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: What is the last SQL executed in PL/SQL block

Postby Tim... » Wed Apr 09, 2014 8:56 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron