Executing oracle Stored procedure with parameters in OScript

SP defination in database:

CREATE OR REPLACE PROCEDURE "DBName"."SP_KLREPORTDOCSSEARCH"
(
p_sqlstr in long,
p_filetype in varchar2
)

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

I am trying to execute the above SP though oscript as below:

Dynamic l_str="q'[Select Document_Id,parentid,vercdate,valdate,versionnum,DOCUMENT_SIZE,pathids from llnk.mvw_doc_msg where ftype = 'doc']' "

String vardoc="doc"

dynamic execResult =CAPI.ExecSp(objPrgCtx.fDbConnect.fConnection,"SP_KLREPORTDOCSSEARCH", l_str, vardoc)

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

I am getting below error:

E675086350,'Wrong number of bind values passed.',''},{E675086349,'Could not bind.',''},{E675086344,'Error executing an Sql statement.

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

I have tried below options as well but none of these work for me..

option 1:

dynamic execResult= CAPI.Exec( objPrgCtx.fDbConnect.fConnection, "select SP_KLREPORTDOCSSEARCH(:A1 :A2) as reserved from dual",l_str, varmsg)

Error:
E675414018,'Oracle reported error.','ORA-00904: "SP_KLREPORTDOCSSEARCH": invalid identifier\n'},{E675086346,'Could not prepare cursor.',''},{E675086344,'Error executing an Sql statement

option 2:

String query1 = Str.Format("exec SP_KLREPORTDOCSSEARCH %1 %2", l_str, varmsg)
dynamic execResult = CAPI.Exec( objPrgCtx.fDbConnect.fConnection, query1 )

error:

E675086344,'Error executing an Sql statement.'

Please suggest .

Find more posts tagged with

Comments

Walt_Ruppar

Thanks for posting the question here Aswhini!

All - We could use a second set of eyes on this code since we are able to successfully run the SP within SQL Developer, i.e.

execute KLReportOldDocsSearch(q'[Select Document_Id,parentid,vercdate,valdate,versionnum,DOCUMENT_SIZE,pathids from llnk.mvw_doc_msg where ftype = 'doc']','doc')

So we know it is not the SP, but ratehr something to do witht he syntax in the OScript... Any help would be greatly appricaited!

Appu_Nair1

What Livelink is this being attempted.If it is 971 or 10 and if you have Recycle Bin,you can see developer comments ReservedDocument:RecyclePre .I seem to have done this easily in livelink on SQLserver but my mind tells me we always resort to Oracle Functions when you are in Oracle.Not being a DBA I do not know if a function vs SP is better or not but I can vouch we recently created an Oracle function adn it works well in our environment.

Run a Capi.ExecSP search in builder or CSIDE and in almost every case you will hit developer comments saying for Oracle we go with functions.In otherword code form OT you can see the saying if dbtype is Oracle then go CAPI.Exec whereas if sybase or sql go ExecSP in CS10.5 they have commented that line and said

query = Str.Format("exec sp_CheckReservedDocument %1", Str.String(node.pID))
                   result = CAPI.Exec( cconn, query )

                   //result = CAPI.ExecSP( cconn, "sp_CheckReservedDocument :A1", node.pID )

If you have A D N in your install you can see that as well.Just bring up SQldeveloper or TOAD and look at functions and procedures in your install and then search inside oscript to see its implementation

Not saying anything I post is correct or should be followed but want you to think how OT devlopers think If you do crack this one correctly with CAPI.ExecSP I would like to know and stay corrected as well.

Robert_Haskett

Hello Ashwini,

Since your stored procedure has two parameters you need to pass those as bind variables, hence the reason you get the "Bind error" on the first attempt.

Try this instead:

result = CAPI.ExecSP( cconn, "SP_KLREPORTDOCSSEARCH :A1, :A2", l_str, vardoc)

result = CAPI.Exec( cconn, "select SP_KLREPORTDOCSSEARCH(:A1,:A2) as reserved from dual", l_str, vardoc)

.r.