Hi,
I have the following MS-SQL Server statement that I want to convert to
ORACLE 8.1
SET @pPhone = '%' + @pPhone + '%'
In ORACLE I tried
SELECT '%' || pPhone || '%' into pPhone from dual;
Where pPhone is a parameter that is passed to the procedure.
How could I do it?
Is it a set in ORACLE?
Thanks,
Florian 6 32942
Florian Marinoiu wrote: Hi,
I have the following MS-SQL Server statement that I want to convert to ORACLE 8.1 SET @pPhone = '%' + @pPhone + '%'
In ORACLE I tried SELECT '%' || pPhone || '%' into pPhone from dual;
Where pPhone is a parameter that is passed to the procedure.
How could I do it?
Is it a set in ORACLE?
Thanks, Florian
It would be helpful to know what you expect as the result since '%' is a
wildcard in Oracle.
But to accomplish what you appear to want ...
pPhone := '%' || pPhone || '%';
And please do not cross-post. One, appropriate, group is enough.
--
Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.washington.edu
(replace 'x' with a 'u' to reply)
Hi Daniel,
thanks for your answer. Unfortunately as soon as I put this line of code in
my procedure the package's status becomes invalid.
I tried a simple test
pName := '%' || '%';
and still doesn't work.
I am using the wildcard because later in my proc I have a select and I use a
LIKE.
Thanks,
Florian
Daniel Morgan wrote: Florian Marinoiu wrote:
Hi,
I have the following MS-SQL Server statement that I want to convert to ORACLE 8.1 SET @pPhone = '%' + @pPhone + '%'
In ORACLE I tried SELECT '%' || pPhone || '%' into pPhone from dual;
Where pPhone is a parameter that is passed to the procedure.
How could I do it?
Is it a set in ORACLE?
Thanks, Florian It would be helpful to know what you expect as the result since '%' is a wildcard in Oracle.
But to accomplish what you appear to want ...
pPhone := '%' || pPhone || '%';
And please do not cross-post. One, appropriate, group is enough.
-- Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.washington.edu (replace 'x' with a 'u' to reply)
Here is the code for the packages that I use
CREATE OR REPLACE PACKAGE "CDMVWEB"."TEST_PCKG"
IS
TYPE curTestSelect is ref cursor;
procedure STTESTSELECT (pPhone IN VARCHAR2 default null,
p_rc out curTestSelect);
end
;
And for the Body
CREATE OR REPLACE PACKAGE BODY "CDMVWEB"."TEST_PCKG"
AS
procedure STTESTSELECT (pPhone IN VARCHAR2 default null,
p_rc out curTestSelect)
IS
BEGIN
-- pName := '%' || pName || '%';
OPEN p_rc FOR
SELECT * FROM DUAL;
exception
when others then
raise;
END;
end TEST_PCKG;
As you can see I put the incriminated line in comments. If it stays like
this my package is valid if I uncomment it becomes invalid.
Daniel Morgan wrote: Florian Marinoiu wrote:
Hi,
I have the following MS-SQL Server statement that I want to convert to ORACLE 8.1 SET @pPhone = '%' + @pPhone + '%'
In ORACLE I tried SELECT '%' || pPhone || '%' into pPhone from dual;
Where pPhone is a parameter that is passed to the procedure.
How could I do it?
Is it a set in ORACLE?
Thanks, Florian It would be helpful to know what you expect as the result since '%' is a wildcard in Oracle.
But to accomplish what you appear to want ...
pPhone := '%' || pPhone || '%';
And please do not cross-post. One, appropriate, group is enough.
-- Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.washington.edu (replace 'x' with a 'u' to reply)
Floriane Marinou wrote:
........ procedure STTESTSELECT (pPhone IN VARCHAR2 default null, p_rc out curTestSelect)
IS
-- here you need to declare your local vars
pName VARCHAR2(100); -- place appropriate length instead of "100"
BEGIN
-- pName := '%' || pName || '%';
OPEN p_rc FOR SELECT * FROM DUAL;
exception when others then raise; END;
end TEST_PCKG;
As you can see I put the incriminated line in comments. If it stays like this my package is valid if I uncomment it becomes invalid.
As I can see, you should look at compiler error messages more closely :)
AFAIU in your case it looked like:
PLS-00201: identifier 'PNAME' must be declared
I think this gives enough information to understand what's the problem.
--
WBR,
Andrey Vakhnin
"Florian Marinoiu" <x@x.com> wrote in message
news:3F***************@x.com... Here is the code for the packages that I use
CREATE OR REPLACE PACKAGE "CDMVWEB"."TEST_PCKG" IS TYPE curTestSelect is ref cursor;
procedure STTESTSELECT (pPhone IN VARCHAR2 default null, p_rc out curTestSelect);
end ;
And for the Body CREATE OR REPLACE PACKAGE BODY "CDMVWEB"."TEST_PCKG" AS
procedure STTESTSELECT (pPhone IN VARCHAR2 default null, p_rc out curTestSelect)
IS
BEGIN
-- pName := '%' || pName || '%';
OPEN p_rc FOR SELECT * FROM DUAL;
exception when others then raise; END;
end TEST_PCKG;
As you can see I put the incriminated line in comments. If it stays like this my package is valid if I uncomment it becomes invalid.
Florian,
I can see a couple of problems.
First the variable pName doesn't exist so you can't assign it a value.
If you really meant to use the IN parameter pPhone rather than pName then
you will find that Oracle won't allow you to change the value if an IN
parameter, only an OUT or IN OUT parameter.
Paul Dixon
The problem is you have not declared the variable pName.
Regards,
Rama Krishna.
Florian Marinoiu <x@x.com> wrote in message news:<3F***************@x.com>... Here is the code for the packages that I use
CREATE OR REPLACE PACKAGE "CDMVWEB"."TEST_PCKG" IS TYPE curTestSelect is ref cursor;
procedure STTESTSELECT (pPhone IN VARCHAR2 default null, p_rc out curTestSelect);
end ;
And for the Body CREATE OR REPLACE PACKAGE BODY "CDMVWEB"."TEST_PCKG" AS
procedure STTESTSELECT (pPhone IN VARCHAR2 default null, p_rc out curTestSelect)
IS
BEGIN
-- pName := '%' || pName || '%';
OPEN p_rc FOR SELECT * FROM DUAL;
exception when others then raise; END;
end TEST_PCKG;
As you can see I put the incriminated line in comments. If it stays like this my package is valid if I uncomment it becomes invalid. Daniel Morgan wrote:
Florian Marinoiu wrote:
Hi,
I have the following MS-SQL Server statement that I want to convert to ORACLE 8.1 SET @pPhone = '%' + @pPhone + '%'
In ORACLE I tried SELECT '%' || pPhone || '%' into pPhone from dual;
Where pPhone is a parameter that is passed to the procedure.
How could I do it?
Is it a set in ORACLE?
Thanks, Florian It would be helpful to know what you expect as the result since '%' is a wildcard in Oracle.
But to accomplish what you appear to want ...
pPhone := '%' || pPhone || '%';
And please do not cross-post. One, appropriate, group is enough.
-- Daniel Morgan http://www.outreach.washington.edu/e...ad/oad_crs.asp http://www.outreach.washington.edu/e...oa/aoa_crs.asp da******@x.washington.edu (replace 'x' with a 'u' to reply) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mike |
last post by:
Is there any way I can use a function to create a variable and assign
a value to it? I have a Perl script that returns some LDAP
information:
sn=Shore
givenname=Mike
logintime=20041008153445Z...
|
by: Rim |
last post by:
Hi,
I have been thinking about how to overload the assign operation '='.
In many cases, I wanted to provide users of my packages a natural
interface to the extended built-in types I created for...
|
by: Matt |
last post by:
If I assign VBScript server side variable a to javascript variable x, it is
fine.
<%
Dim a, b
a = 10
%>
var x = <%= a %>;
alert(x);
But if I do the other way around, then it has 500 error....
|
by: Terry |
last post by:
I have a number of input boxes used to display totals based on
selected items for each row in a table. There are more than a few
rows that are identical, except for the form field name. I have...
|
by: Eric |
last post by:
How can I dynamically assign an event to an element?
I have tried :
(myelement is a text input)
document.getElementById('myelement').onKeyUp =
"myfnc(param1,param2,param3)";
...
|
by: Jim McGivney |
last post by:
In asp 2.0 I am trying to insert a row using a detailsview control connected
to an accessDataSource. I get the error message below. I am having trouble
identifing which data field is causing the...
|
by: david |
last post by:
I try to use "for" loop to retrieve and assign values in web form. The code
is in the following. But it can not be compiled.
What I want to do is:
txtQ1.Text =...
|
by: Mirovk |
last post by:
Hi,
I have a session variable wich value arrives from a previous .asp but
in my actual page I need to modify it based upon a selected value from
a radio button.
I will try to figure out for...
|
by: Don Lancaster |
last post by:
I need to progrmatically do this inside a loop
this.fh03.value = fixFloat (Harms, numPoints) ;
with the numbers changing per an index.
If I try
curHvals = "03" ; // (derived from...
|
by: Birky |
last post by:
Assign the output of a select to a variable?
I am unable to find a way to assign the results of an SQL statement to a variable. I know how to assign the SQL statement to a variable but again no...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |