Oracle Database Discussions

I have not tried the suggestion I'm about to give; I just read it on the documentation before and hope it will help you.

You can try DBMS_HS_PASSTHROUGH package. Read more at
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_hspass.htm#i997379

Hope that helps.

hi,

thank you for the response.
when i call the sp using DBMS_HS_PASSTHROUGH, without parameters it works successfully, but with parameters it gives the following error

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver]Invalid parameter number[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index (SQL State: S1093; SQL Code: 0)

my code is,

declare
c INTEGER;
nr INTEGER;
begin
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@hsa;
DBMS_HS_PASSTHROUGH.PARSE@hsa(c, 'Create_Receipt(?,?)');
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsa(c,1,'abc');
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@hsa(c,2,'xyz');
nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@hsa(c);
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@hsa(c);
end;

Create_Receipt is the sp which requires two parameters.

please give me a solution

thanking you
sreejith

hi all,

solved the problem. the error was with syntax, instead brackets, need to put double quotes to pass parameters for SP

sample code is

declare
dummy integer;
string1 varchar2(100);
string2 varchar2(100);
begin
dummy:= DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsa('Create_Receipt "'||string1||'" , "'||string2||'"' );
end;

thnaking you
sreejith

another constraints are coming while going ahead

Now am facing another problem on this.
While passing parameters to the SP, i'm getting the error message

ORA-28551: pass-through SQL: SQL parse error
[Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with '<root><FactoryName>MAXIMUS COFFEE</FactoryName><VesselNo>JB</VesselNo><PORefNo>191</PORefNo><BlnktRelNo>25099</BlnktRelNo><Note>' is too long. Maximum length is 128. (SQL State: 37000; SQL Code: 103)

The parameter which i'm passing is an XML query which is big.

can any one help me on this urgently....

thanking you
sreejith

declare
dummy integer;
string1 varchar2(100);
string2 varchar2(100);
begin
dbms_output.put_line('Create_Receipt "'||string1||'" , "'||string2||'"');
dummy:= DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsa('Create_Receipt "'||string1||'" , "'||string2||'"' );
end;

so, you have to check what exact "Execute Statement" you are going to pass, because it will receive VARCHAR2 variable with the statement to be executed immediately. So see what it displays.

HTH
Girish Sharma
http://download.oracle.com/docs/cd/A58617_01/server.804/a58247/apc.htm#794