SQL & PL/SQL :: Package For Web Service

Nov 22, 2011

We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.

The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.

what type of PL/SQL output we should use here?

Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.

View 4 Replies


ADVERTISEMENT

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related

SQL & PL/SQL :: Global Exception In Package Available Outside Package?

Jan 15, 2012

I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:

e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);

Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?

View 4 Replies View Related

XML DB :: PL/SQL Proc As A Web Service

Mar 1, 2013

Oracle 11.2.0.x

Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).

The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.

Question.

Can one support WSS (Web Services Security) with this feature?

I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.

It however uses Basic Authentication (schema name and password). Not WSSE.

As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).

So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.

Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.

View 2 Replies View Related

TNS - Listener Does Not Currently Know Of Service?

Feb 27, 2013

I am new to Oracle DB. I have installed Oracle 10g in XP Service Pack2 and was able to login using SYS/SYSTEM users and also was able to create a new user XYZ...

Once I restarted my system, I am facing a strange problem while login. It is continuously throwing the error "tns: listener does not currently know of service requested in connect descriptor". I do not understand the reason. I have checked the services and all oracle related services are running.

However, when I tried to login with the same userid/password from command prompt using 'sqlplus' command, I am able to login ! There was no error this time.

View 2 Replies View Related

PL/SQL :: Max Date Of Service

Dec 24, 2012

I need to determine the max date of service for each mbr_idfrom the following data:

mbr_id mbr_asc mbr_dept mbr_service

1162903 00 1723 01-MAR-11
1162903 00 1708 12-DEC-11
1162903 00 1708 12-AUG-12
2262903 01 1723 01-MAR-11

[Code]....

Results should be:

mbr_id mbr_asc mbr_dept mbr_service

1162903 00 1708 12-AUG-12
2262903 01 1708 21-AUG-12
3362903 00 1708 23-AUG-12
4462903 00 1708 24-AUG-12
5562903 00 1708 25-AUG-12

View 6 Replies View Related

SQL Statements Behind Self Service Page?

Aug 29, 2011

I have a question regarding the SQL statements embedded behind the Self Service Pages in Oracle Applications.

Taking an example of Oracle E-business Suite, Is there a way to check what SQL statement is hard coded or embedded in a particular required Self Service Page?

View 1 Replies View Related

Web Service On Forms 6i (Patch 17)

Oct 16, 2010

I have a "pure" client / server environment with forms/reports 6i (NO form server architecture) and my necessity is to retrieve data from Web Service Call within Forms.

Do you have any simple example for realize this? Javaimporter is the right solution or not?

View 1 Replies View Related

PL/SQL :: UTL_HTTP For Web Service To Send A SMS

Dec 11, 2012

I am trying to send SMS through a web service, and before doing it on a form, I tried to check this with UTL_HTTP from sql plus

below given is the code i tried

SQL> SET DEFINE OFF
SQL> SELECT UTL_HTTP.REQUEST('http://80.227.146.106/Receiver/sms99.aspx?usr=JOE123&pass=JOE321&
msisdn=971556221690&msg=test msg through ibuzz&mt=0') FROM DUAL;

and am getting the result as shown below

UTL_HTTP.REQUEST('HTTP://80.227.146.106/RECEIVER/SMS99.ASPX?USR=JOE123&PASS=JOE321&MSISDN=97155
<HTML><HEAD>
<TITLE>Request Error</TITLE>
</HEAD>
<BODY>
[code]......

Your request used a protocol that is not currently supported.

</TD></TR>
UTL_HTTP.REQUEST('HTTP://80.227.146.106/RECEIVER/SMS99.ASPX?USR=JOE123&PASS=JOE321&MSISDN=97155
<TR><TD>
</TD></TR>
<TR><TD>
</TD></TR>
</TABLE>
</BODY></HTML>
**********-----------------------------------------------------------------------------------------------------------------------------------------------------------********
protocol used for the web service is HTTP. they have given an IP with port number . and the method is GET

[URL]........

parameters are msisdn, msg, mt their response would be either true or false. I tried this on Oracle9i Enterprise Edition Release 9.2.0.1.0
and the message is Your request used a protocol that is not currently supported. when i tried the same on Oracle Database 11g Release 11.2.0.1.0
i got

Request Error (unsupported_protocol)

View 0 Replies View Related

ODP.NET :: Creating Windows Service

Jul 20, 2012

I have created a program to access a oracle database using oracle.data access, .net framework 4, visual studio 2010 in C#. The program runs without any problem. If I try to create a windows service, I receive the error

Service cannot be started. System.BadImageFormatException:
Could not load file or assembly 'Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies.
An attempt was made to load a program with an incorrect format.
File name: 'Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342'

when i execute the command

GlobalVariables.conn = new OracleConnection(oracelString);

View 3 Replies View Related

PL/SQL :: Download Data Through Web Service

Sep 12, 2013

We have a requirement to download data from SAP system to our Oracle DB. Issue is we will not be able to connect to SAP DB. They will provide a webservice and we should use that by passing parameters. My query is, how can we do that in Oracle DB. I want to write a Program unit and call web service whenever required. 

View 5 Replies View Related

RAC & Failsafe :: Service Always Offilne

Oct 26, 2013

OS :
6100-01-09-1015
oralce:
11.2.0.1.0 - 64bit RAC

View 1 Replies View Related

Calling Web Service From A Procedure Using SOAP API

Mar 5, 2013

I am trying to call a Web service using the SOAP API and I get the following error.

ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
ORA-06512: at "SYS.XMLTYPE", line 48
ORA-06512: at "MyConnection.SOAP_API", line 121
ORA-06512: at "MyConnection.MyFunction", line 28
31020. 00000 - "The operation is not allowed, Reason: %s"
*Cause: The operation attempted is not allowed
*Action: See reason and change to a valid operation.

However we do have another procedure that is able to make an HTTP call successfully within the same domain. Hence the ACLs that are required are for making an HTTP call are in place. Is there any other security setting to be tweaked to make the to enable me to make a Webservice call using the SOAP package?

View 1 Replies View Related

Create Another Listener Service In RAC 11gR2 Env?

Aug 30, 2011

I need to create another listener service in RAC 11gR2 env for a new application connecting to the database in 11gR2 env. I have an existing listener service listening on 2 RAC instances now.The new one needs to be created will have a new service_name, preferably using different port (other than 1521), listening on same same set of instances.

View 3 Replies View Related

Listener Doesn't Currently Know Of Service Requested

Jul 15, 2010

I am using Oracle 10g and when i start my server then my database is running and my application use this database.But after 15 min automatically application show this error

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

View 1 Replies View Related

TNSListener Service Stops Automatically?

Apr 7, 2011

In our production server, TNSListener Service stops automatically, atleast (once in a day). No alert in alert log file.

View 1 Replies View Related

Server Administration :: How To Switch Service

Nov 30, 2011

A sid which is using the service1, how can i switch it to the service2.

Select Value
from v$parameter where name='service_names'

VALUE
---------------
service1, service2, service3, service4
SQL> Select service_name From v$session where sid=1980;

SERVICE_NAME
----------------------------------------------------------------
service1

View 4 Replies View Related

How To Config Network Service By Script

Jul 5, 2013

I have created a batch for silent DB Creation through DBCA. I want to add a script to also create the Network Service after. I was told it can be done only with Response File.

Is a response file a script also that can be executed after the DBCA scripts? Where can I find a manual with the available commands so I can produce what I need? On the internet I find only examples for Silent DB installation.

edit-solution
Complete Solution posted as New article

[URL]..........

View 14 Replies View Related

Windows :: Oracle Service Not Starting?

Oct 23, 2012

I'm using Oracle 11g running over a Windows 7 OS. The service OracleServiceORCL doesn't start. Listener.log has the following registry:

23-OCT-2012 19:45:29 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=MBA-DB$))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=49445)) * establish * orcl * 12514

TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

When I try to start in services, is returned the OS error 1067. This error starts to occur when I did the follow. I executed a PLSQL command to fill a (big) table with a lot of data. More than 1 day after and while the program still running, I broke the program. After that, Oracle was consuming lot of disk. I broke the process (using taskkill /f) and, after that, the database stopped working.

What I can do to recover my database?Below I list my listener.ora and tnsnames.ora

mbentoalves

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Extracting Data From Web Service Response

Mar 26, 2013

I need to extract tag values from REST web service response.

Webservice response

Quote:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>

I am storing above response in a table column 'output_xm' of xmltype datatype.

My requirement is to extract 'ResponseCode' & 'quantity' values from above response. For which I am using below query but it's not working.

SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';

View 2 Replies View Related

Application Express :: How To Call EDQ Web Service

Oct 2, 2012

I am trying to figure out a way to call EDQ (enterprise Data Quality) web service from APEX ?

View 6 Replies View Related

SQL & PL/SQL :: How To Send SMS Through Oracle - Service Not Available Error

Jul 2, 2010

I am learning How to send sms thru PLSQL gateway?

I have searched for it on internet & got 1 procedure on OTN. I also tried it. But it is giving me the Errors .

The procedure is as below :

create or replace procedure SEND_SMS(p_sender in varchar2,
p_recipient in varchar2,
p_message in varchar2) as
mailcon utl_smtp.connection;
begin

[code]....

I also tried to Execute the same Procedure. The execute statement is below :

EXECUTE send_sms('myemail@server.com', '9822553277@smsserver.com', 'My Subject', 'My Message');

But i got the errors as :

begin send_sms('myemail@server.com', '9822553277@smsserver.com', 'My Subject', 'My Message'); end;

ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 139
ORA-06512: at "ADMIN.SEND_SMS", line 13
ORA-06512: at line 2

View 2 Replies View Related

How To Configure Network Service By Script

Jul 5, 2013

I have created a batch for silent DB Creation through DBCA. I want to add a script to also create the Network Service after. I was told it can be done only with Response File. Is a response file a script also that can be executed after the DBCA scripts?Where can I find a manual with the available commands so I can produce what I need? On the internet I find only examples for Silent DB instalation. EDITOS is Windows 7 Oracle 11.2.0.1 I want to create the Network Service that will add these lines to the tsnames.ora.

DBNAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wsName)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = dbname)
)
)

 on both the client and the server.How can i do it automatically through a batch script, so to add it to DBCA scripts after creation? I was told once in atopic that it could be done through a response file.

View 9 Replies View Related

XML DB :: Unable To Execute Sql Queries Using XDB Service

Aug 3, 2012

I am not able to execute sql queries using XDB service

My wsdl is

[URL]......

My manually written call is

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:oraw="http://xmlns.oracle.com/orawsv">
<soapenv:Header/>
<soapenv:Body>
<oraw:query>
<oraw:query_text type="SQL">select 1 from dual</oraw:query_text>
</oraw:query>
</soapenv:Body>
</soapenv:Envelope>

I have tried a few more flavors of soap envelope but I always get the below response instead of the actual response

The response is

<definitions name="orawsv" targetNamespace="http://xmlns.oracle.com/orawsv"
xmlns="http://schemas.xmlsoap.org/wsdl/"
xmlns:tns="http://xmlns.oracle.com/orawsv"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
[code].........

View 9 Replies View Related

PL/SQL :: Oracle Web Service - Dynamic Parameters

Apr 4, 2013

Oracle 11.2.0.3 - newbie on web services! Currently we have a pl/sql web service taking in 2 static parameters and returning a varchar2 response.

I was asked today if it's possible for a web service to take in a dynamic set of parameters and return a corresponding set of results. It's as if I need to pass the web service a table with two columns and return a table of one column.

For example:

                    Input                          Output
          1 2                             3
          3 4                             7
          5 6                            11output being in XML of course. It is possible?

I posted this in OC4J also - no response hence the re-post !

View 3 Replies View Related

ORA-12514 / TNS / Listener Does Not Currently Know Of Service Requested In Con

Nov 19, 2012

I am facing problem "ORA-12514" error. my database is register in listener but when i connected throw "sqlplus" then i got "ORA-12514" error.

As show below

[oracle@sun3 ~]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 19-NOV-2012 14:40:15
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production

[code]....

View 14 Replies View Related

Ora-24324 / Service Handle Not Initialized

Sep 21, 2012

SQL>startup

These error occur:

ora-24324: service handle not initialized
ora-01041: internal error. hostdef extension doesn't exist

As my database is 10.2.0.1 and Linux O/S.

View 6 Replies View Related

XML DB :: APEX Soap Web Service Response Into DB

Oct 29, 2013

How do i get a response from a soap webservice and insert it into a DB

View 0 Replies View Related

Ora-24324 - Service Handle Not Initialized?

Sep 21, 2012

What is the solution of following error?

SQL>startup

These error occur:
ora-24324: service handle not initialized
ora-01041: internal error. hostdef extension doesn't exist

View 1 Replies View Related

Server Administration :: Create New Service For Database?

Jul 16, 2010

create a new DB service and apply the load , then I need to verify the service load from Enterprise Manager or table V$SERVICEMETRIC .

Below are what I did

Quote:I tried to add service in tnsnames.ora like below

SERVICE = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=25057)(HOST=dadvmc))(CONNECT_DATA=(SID=tgc4)))

/************
Also I tried the serviceName like below, but not working when I conneted SALES1 from sqlplus
SALES1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=25057)(HOST=dadvmc))(CONNECT_DATA=(SERVICE_NAME=host.us.xxx.com)))
**********/

and login as

sqlplus scott/tiger@SERVICE[/email][/email]

then lock table EMP in exclusive mode in two session.

I can see the load is added to service SYS$USERS , but I can't see the new service from V$SERVICEMETRIC or Enterprise Manager.

select
SERVICE_NAME
from V$SERVICEMETRIC

How could we create the new DB service?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved