always return NULL from calling a stored func

[jiniduck]

I generates the test case xml using TUI.  The content of the  "tui.properties":

capturefile = /applParamTest.xml
driver = oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@host:1561:name
user = userid
password = passwd

__test!name = Get an application param value
__test!call!stmt = { ? = call Get_App_Param(?, ?) }

___test!call!param[1]!name = p_return_value
___test!call!param[1]!type = VARCHAR
___test!call!param[1]!inout = out
___test!call!param[1]!is-null = false
___test!call!param[1]!value = ${p_return_value}

___test!call!param[2]!name = p_app_code
___test!call!param[2]!type = VARCHAR
___test!call!param[2]!inout = in
___test!call!param[2]!is-null = true
___test!call!param[2]!value = appCode

___test!call!param[3]!name = p_param
___test!call!param[3]!type = VARCHAR
___test!call!param[3]!inout = in
___test!call!param[3]!is-null = false
___test!call!param[3]!value = helpDesk

The content of the generated "applParamTest.xml":

<test name="Get an application param value">
  <call>
  <stmt>{ ? = call Get_App_Param(?, ?) }</stmt>
  <param id="1" name="c1" type="VARCHAR" is-null="false" inout="out">${p_return_value}</param>
  <param id="2" name="c2" type="VARCHAR" is-null="true" inout="in">appCode</param>
  <param id="3" name="c3" type="VARCHAR" is-null="false" inout="in">helpDesk</param>
  </call>
  <result>
    <outparam id="1" name="c1" type="VARCHAR">null</outparam>
  </result>
</test>

The problem is result expected is "null" value returned from the stored func.  However, when I write a PL/SQL to call Get_App_Param, it will return some value.  Do I type something wrong in "tui.properties"?  Is there some settings missed out?

Thanks

[Sujit Pal]

Hi,

The latest version of TUITool use the same code that SQLUnit does to process a call statement. If you take this resultant XML file and run it through the sqlunit tag, does this pass or fail? I think it will pass since you are setting the value, but you can check to see if the value is what you expect with an echo tag after the call.

Your call looks ok. I will have to check why the outparam is not showing the correct value. I will update the thread. Please let me know what you found.

Thanks
Sujit

[jiniduck]

Hi,

I think the problem is due to SQLUnit code cannot access the table.  The similar problem happens when I execute a SQL statement. 
E.g.
select APPL_ID from TBL_CORP_APPLICATION where APPL_CODE = 'rra'
It returns 224.
APPL_ID column type is NUMBER.
APPL_CODE column type is VARCHAR2(16).

However, when I prepare test case as following
<?xml version="1.0"?>
<!DOCTYPE sqlunit SYSTEM "file:docs/sqlunit.dtd">
<sqlunit>
  <connection>
    <driver>oracle.jdbc.driver.OracleDriver</driver>
    <url>jdbc:oracle:thin:@dbname</url>
    <user>userid</user>
    <password>passwd</password>
  </connection>
<test name="Get an application ID">
  <sql>
    <stmt>select APPL_ID from TBL_CORP_APPLICATION where APPL_CODE = ?</stmt>
    <param id="1" name="c1" type="VARCHAR" is-null="true" inout="in">rra</param>
  </sql>
    <result>
      <resultset id="1">
        <row id="1">
          <col id="01" name="" type="NUMERIC">224</col>
        </row>
      </resultset>
    </result>
</test>
</sqlunit>

I get assertionError, it says the actual result is
<result>
  <resultset id="1" />
</result>

i.e. resultset is 0.  This error is consistent with always return null from a stored func.

Is there configuration required?

Thanks,

jini

[jiniduck]

Hi Sujit,

I make a stupid mistake.
1. If the input param is not null, I should set is-null="false".
2. If the param type is VARCHAR, I should only enter the textual value without enclosing them with single quotation marks.

B Rgds

[Sathi Chowdhury]

Hi ,
I am testing a simple stored proc which returns a single varchar2 value and has no input parameters .My sql unit testcase file looks like this:
<sqlunit> 
<connection connection-id="1"> 
<driver>oracle.jdbc.driver.OracleDriver</driver> 
other info I can't print here
</connection>

<test name="Find Document Types through a procedure">
  <call connection-id="1">
  <stmt>{call IDE_ROOT.GET_DOCTYPES(?)}</stmt>
    <param id="1" name="p_result" type="VARCHAR" inout="out">${p_result}</param>
</call>
  <result>
    <outparam id="1" name="p_result" type="VARCHAR" >XLS</outparam>
  </result>
</test>
</sqlunit> 
-----------------

when I run this

I get the fiollowing output
C:\sqlunit-4.9>ant run-second-test
Buildfile: build.xml

def:

run-second-test:
  [sqlunit]  WARN [main] (SQLUnit.java:168) - log4j.properties not found (using
defaults)
  [sqlunit] *** Running SQLUnit file: test/oracle/secondtest.xml
  [sqlunit] Getting connection(1)
  [sqlunit] Setting up test...
  [sqlunit]  (0ms)
  [sqlunit] Assertion "exception-matches" failed (result != exception)
  [sqlunit] *** expected:
  [sqlunit] <result>
  [sqlunit]   <outparam id="1" name="p_result" type="VARCHAR">XLS</outparam>
  [sqlunit] </result>
  [sqlunit] *** but got:
  [sqlunit] <result>
  [sqlunit]   <outparam id="1" name="p_result" type="VARCHAR">NULL</outparam>
  [sqlunit]   <exception>
  [sqlunit]     <code>6550</code>
  [sqlunit]     <message>ORA-06550: line 1, column 7: PLS-00201: identifier 'IDE
_ROOT.GET_DOCTYPES' must be declared ORA-06550: line 1, column 7: PL/SQL: Statem
ent ignored</message>
  [sqlunit]   </exception>
  [sqlunit] </result>

  [sqlunit] Tearing down test...
  [sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: test/oracle/secondtest.x
ml, tests: 1, failures: 1, errors = 0
  [sqlunit] One or more SQLUnit Tests failed, see the console for details

-----------------------------------

Can you tell me what's wrong here ?why is it not able to find the procedure ?