2

I have to debug stored procedure which contains a few SQL queries. One of them contains an error. So, I need to execute this SQL query with parameters in other window. I found next query that would help me:

select v.SQL_TEXT
from v$sql v

Unfortunately, this field restricted by 1Kb. In my case I have quite big SQL query and Oracle truncates it. How to log the executed query? I use PL/SQL Developer 10 and Oracle 9i

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Nolesh
  • 6,848
  • 12
  • 75
  • 112
  • what do you mean by logging sql query ? :s – Ravi Jul 07 '15 at 01:27
  • @jWeaver, something like exporting SQL query with parameters from stored procedure to .txt. For example, `select * from employee e where e.id=vID` to **select * from employee e where e.id=15** – Nolesh Jul 07 '15 at 01:34
  • 3
    enable tracing with bind parameters and run tkprof on the output http://stackoverflow.com/questions/18928230/how-to-get-tracing-info-for-binding-variables-passed-through-oracleparameter-in – OldProgrammer Jul 07 '15 at 01:49

1 Answers1

3

Unfortunately, this field restricted by 1Kb

If you need the full SQL, then use the SQL_FULLTEXT which is a CLOB datatype instead of SQL_TEXT whcih is limited to first 1000 characters.

From documentation,

Column          Datatype        Description
------          --------------  ---------------------------------------

SQL_TEXT        VARCHAR2(1000)  First thousand characters of the SQL 
                                text for the current cursor

SQL_FULLTEXT    CLOB            Full text for the SQL statement exposed 
                                as a CLOB column. The full text of a SQL
                                statement can be retrieved using this 
                                column instead of joining with the 
                                V$SQL_TEXT dynamic performance view.

So, use:

SELECT SQL_FULLTEXT FROM v$sql;

By the way, seems like you are actually looking for tracing your session to get the complete details of the procedure and the SQL statements involved. I would suggest to trace the session with level 4 i.e. with the addition of bind variable values.

See How to generate trace file – SQL Trace and TKPROF in Oracle

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Unfortunately, I don't have permission to server to see trace file. So, I can use only one way which is **v$sql** table, and it's much easily in my opinion. I'm going to try your solution tomorrow. If it works, I'll check your answer. Thanks anyway! – Nolesh Jul 07 '15 at 12:03
  • In Oracle 9i which I use there is no **SQL_FULLTEXT** field. I need to find another way! – Nolesh Jul 07 '15 at 23:02
  • Did you try `v$sql_text`? – Lalit Kumar B Jul 08 '15 at 03:14
  • Did you mean `v$sqltext`? Yes, there is **SQL_TEXT** field which is restricted by 64 bytes. I begin hating Oracle 9i – Nolesh Jul 09 '15 at 05:25
  • Sorry, I don't have a 9i instance to check. By the way Oracle 9i is desupported. – Lalit Kumar B Jul 09 '15 at 05:37