How to assign a value to a variable

[Florian Marinoiu]

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

[Daniel Morgan]

Re: How to assign a value to a variable

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Florian Marinoiu]

Re: How to assign a value to a variable

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Florian Marinoiu]

Re: How to assign a value to a variable

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Andrey S. Vakhnin]

Re: How to assign a value to a variable

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

[Paul Dixon]

Re: How to assign a value to a variable


"Florian Marinoiu" <x@x.comwrote in message
news:3F9717E9.9 B580851@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

[Rama Krishna]

Re: How to assign a value to a variable

The problem is you have not declared the variable pName.

Regards,
Rama Krishna.

Florian Marinoiu <x@x.comwrote in message news:<3F9717E9. 9B580851@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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)