Debugger, CLOB/XML param issue?
|
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA
Member
|
OP
Member
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA |
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
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Aug 1999
Posts: 22,220
Member
|
Member
Joined: Aug 1999
Posts: 22,220 |
Is this a CLOB or a Temporary CLOB variable? Only a Temporary CLOB can hold a value on input.
Marco Kalter Allround Automations
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA
Member
|
OP
Member
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA |
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
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA
Member
|
OP
Member
Joined: Feb 2003
Posts: 22 Lakewood, CO, USA |
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, '<long string of XML here>');
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 => :p_token, p_empXML => v_clob); end;
Now I can step through the custom XML/tag parsing routines to see what is going in.
- Tim
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Aug 1999
Posts: 22,220
Member
|
Member
Joined: Aug 1999
Posts: 22,220 |
This works fine for me with a Temporary CLOB. I can paste text in the CLOB editor, and pass it into a stored procedure.
Marco Kalter Allround Automations
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Aug 2003
Posts: 63
Member
|
Member
Joined: Aug 2003
Posts: 63 |
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.
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Aug 1999
Posts: 22,220
Member
|
Member
Joined: Aug 1999
Posts: 22,220 |
Did you try a "Temporary CLOB" variable? That should work too.
Marco Kalter Allround Automations
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Apr 2006
Posts: 4 Gainesville, VA
Member
|
Member
Joined: Apr 2006
Posts: 4 Gainesville, VA |
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.
Lawrence J. Sylvain
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Aug 1999
Posts: 22,220
Member
|
Member
Joined: Aug 1999
Posts: 22,220 |
I have added this to the list of enhancement requests.
Marco Kalter Allround Automations
|
|
|
Re: Debugger, CLOB/XML param issue?
|
Joined: Apr 2006
Posts: 4 Gainesville, VA
Member
|
Member
Joined: Apr 2006
Posts: 4 Gainesville, VA |
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!
Lawrence J. Sylvain
|
|
|
|
|