SQL action within loaddb.cfg

sbaumann

hello,
we use a <sql-action> tag within a special deployment in our loaddb.cfg.

--------------------------------------------
...
<destinations>
<database use = "CMSDatenbank"
...
>
<sql action="tracker-exist" type="update">
call cms_im_full_deploy_delete()
</sql>
<substitution>
....
--------------------------------------------

the procedure works fine (all tables are empty after this call) but we get the following error every time:

------------------------------------------------
SELECT * FROM USER_TABLES WHERE TABLE_NAME='IWT_MIU_P'
Table [IWT_MIU_P] exists.
SQL Exists check:
call cms_im_full_deploy_delete()
call cms_im_full_deploy_delete()

*******************************************************
SQLException occured in TableSchema:Exists()
Exception Message: ORA-01009: missing mandatory parameter

Vendor Error Code: 1009
SQL state: 72000
*******************************************************

*******STACK TRACE*************

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

what we can do, that this error not occur? can we use another sql-action statement?

Thanks
Steve

Edited by sbaumann on 06/28/02 01:40 AM (server time).

Migrateduser

It seems like you are trying to execute a stored procedure. The syntax for that is little different:

If user wants to execute a stored procedure called ‘proc1’, the dd config file element looks like:

Assuming that this stored procedure takes no input parameters, there is a variation in the syntax:

For Oracle, Informix and Sybase SQLAnywhere:

<sql user-action=”execute_my_procedure” type=exec-proc>
proc1()
</sql>

For Microsoft SQLServer and Sybase Adaptive Server Enterprise:

<sql user-action=”execute_my_procedure” type=exec-proc>
proc1
</sql>


Example 2:

If user wants to execute a stored procedure called ‘proc2’ that takes two parameters (integer, character), the dd config file for the action looks like:

For Oracle, Informix and Sybase SQLAnywhere:

<sql user-action=”execute_my_procedure” type=exec-proc>
proc2(1,’testval’)
</sql>

For Microsoft SQLServer and Sybase Adaptive Server Enterprise:

<sql user-action=”execute_my_procedure” type=exec-proc>
proc1 1, ‘testval’
</sql>

How to invoke:

Iwdd.ipl deployment=<deployment> cfg=<cfg file> iwdd-op=sql user-op=execute_my_procedure

Result of executing the stored procedure is reported as either successful or failure.