Debug a procedure in Oracle which feeds into crystal report

[Member 14474607]

Hi, I have debugged a stored proc in Sql Server, which was fairly easy to do. I am trying the same with debugging Oracle procedures, being fairly new having some difficulties. I have researched how to output a cursor value, with no luck. Also, the sql is constructed, and then used in cursor to output results to crystal report. My final sql looks like this, which is a whole bunch of variables:

ssql := s_SEL||s_FROM1||s_WHERE||s_WH_PER||s_WH_CC||s_WH_VEN
||s_UNION||
s_SEL||s_FROM2||s_WHERE||s_WH_PER||s_WH_CC||s_WH_VEN

I tried with no luck:

execute immediate ssql

I just want to see the values of the sql statement. Any help is much appreciated!

[Jorgen Andersson]

Either use PRINT ssql; or SELECT ssql FROM DUAL; Really depends on how the rest of the procedure looks like.

Wrong is evil and must be defeated. - Jeff Ello

[Member 14474607]

Thank you for your reply.

I did the following:

execute immediate ssql;
SELECT ssql FROM DUAL;

I got the following:
[Error] Execution (313: 1): ORA-06550: line 313, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

[Qingyong Yu]

In my opinion , if you can create log table in your database. A easy way is insert the sql into your log table. e.g. Create log table:

CREATE TABLE SQLLOG_DEBUG
(
SQL_LOG VARCHAR2(4000 BYTE)
)

Insert the sql into your log table.

INSERT INTO SQLLOG_DEBUG (SQL_LOG ) VALUES (ssql )

*This is just a sample, I didn't test the code. If the sql is big , you can change SQL_LOG's datatype into clob. You can also use DBMS_OUTPUT.PUT_LINE and redirect the output to a file. The following page shows how to do it. [stackoverflow.com]