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]

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)

[Florian Marinoiu]

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)

[Florian Marinoiu]

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)

[Andrey S. Vakhnin]

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]

"Florian Marinoiu" <[e-mail]> 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

[Rama Krishna]

The problem is you have not declared the variable pName.

Regards,
Rama Krishna.

Florian Marinoiu <[e-mail]> 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)