Debugger, CLOB/XML param issue?

[tcogill]

I have a procedure that accepts a CLOB parameter, into which I am sending and XML string. In the debugger variable pane, I select "..." for the parameter to open the Large Object Editor. I cut/paste the string into the Text tab; the Editor recognizes it as XML and allows me to see the parsed tree on the XML tab. When I run the procedure the variable (where things go wrong) is NULL so it fails. PL/SQL Developer returns to the Test window, but now the CLOB variable is empty. For other variable types I've found the Test Window remembers previously entered values.

Any thoughts?
PL/SQL Developer version 9.0.5.931
Oracle 9.2.0.5.0
Windows 2000

Thanks.
- Tim

 

[Marco Kalter]

Is this a CLOB or a Temporary CLOB variable? Only a Temporary CLOB can hold a value on input.

 

[tcogill]

I didn't realize there was a difference. The application that uses this works fine (and has been for a while), I've been tasked with a few enhancement requests. The original developer did not use PLSQL Developer to test.

Here's my procedure spec (if it helps).
procedure saveEmp
( p_token in varchar2
,p_empXML in clob );

Apologies if this is turning into an Oracle lesson, but what's the difference as far as PLSQL Developer is concerned?

- Tim

 

[tcogill]

I'm still at a loss as to why, inside a Test Window, cut/paste a string into my CLOB parameter doesn't work, but here's my work-around for anyone interested.

The theory is since this is development, store my long string(XML) in a table then select it out from with my Test Window. The actual procedure is called from Cold Fusion, so going through these steps are unnecessary for them.

So...

a) Create table and populate with CLOB data:
CREATE TABLE test_clob (
a INTEGER,
a_clob CLOB)
TABLESPACE data_128k;

INSERT INTO test_clob VALUES (
1, '');

COMMIT;

b) Startup a new Test Window. In the bottom pane, uncheck the parameter that is a CLOB to avoid ORA-01036 error.

c) Modify the test script to read:

DECLARE
v_clob CLOB;
--
CURSOR get_clob IS
SELECT a_clob
FROM test_clob
WHERE a = 1;
BEGIN
OPEN get_clob;
FETCH get_clob INTO v_clob;
CLOSE get_clob;

-- Call the procedure
emp_mgr.saveemp(p_token => _token,
p_empXML => v_clob);
end;

Now I can step through the custom XML/tag parsing routines to see what is going in.

- Tim

 

[Marco Kalter]

This works fine for me with a Temporary CLOB. I can paste text in the CLOB editor, and pass it into a stored procedure.

 

[vllr]

We had similar issue. Easy way to bypass it is to change your parameter type in debugger from CLOB to String. You don't have to change anything in your code - just change the type of the parameter. String is automatically converted to CLOB input parameter.

 

[Marco Kalter]

Did you try a "Temporary CLOB" variable? That should work too.

 

[Lawrence J. Sylvain]

Thanks much. When I first encountered this issue I thought my function had a bug in it! It cost me some time to find out that was not the case... Even after I realized this issue was there and communicated it to my team, the issue has been driving us nuts off-and-on for a while now.

May I suggest that you do one of two things with the product?

1. My first choice is this. If the only way to cause PL/SQL Developer to actually *pass* the value of a CLOB parameter from the test screen to a function or stored procedure is to use a Temporary CLOB, the drop-down should be pre-populated that way when a new test script is created. I never would have thought to change the parameter type because as a developer it seems so hackish to do something like that. Even if I thought to do this, I never *heard* of a Temporary CLOB before!

2. Second choice. Please document prominently that a Temporary CLOB should be used in these cases in the help. I looked there and did not see anything about this issue.

Thank God I found something on this forum because this was causing us a great deal of inconvenience. Plus, I was looking sort of stupid since I have been singing the praises of PL/SQL Developer as a Toad replacement to convince my team to convert over to the product.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Lawrence J. Sylvain]

Thanks. One of the reasons that you have such an great product is that you listen to your customers. It really has been a liberating experience switching over to PL/SQL Developer from the Quest products. Great job!