OraFAQ Forum: SQL & PL/SQL » Declare and assign a value ot a variable

Home » SQL & PL/SQL » SQL & PL/SQL » Declare and assign a value ot a variable (Oracle 11, SQL developer)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Declare and assign a value ot a variable [message #618638]

Mon, 14 July 2014 09:37

itmasterw
Messages: 13
Registered: July 2014
Location: NY

Junior Member

Hi.
I am trying to declare and assign a value ot a variable, adn then use it in the code.

Declare in_cust_num NUMBER
in_cust_num = 2546895;

BEGIN
select cust_acct_id as MSS_ID, 1 as Parent
                from rptadmin.MV_CONQ_PARTY
                where BILL_PRNT_SUB_ID = in_cust_num
                and BILL_PRNT_SUB_ID <> cust_acct_nbr
                Union
                select cust_acct_id as MSS_ID, 0 as Parent
                from asap.cust_acct
                where cust_acct_nbr = TO_CHAR(in_cust_num);
END;

When I do this it comes back with an error at line one but it is not telling me what.
Andnt ideas of wht I am doing wrong?

Thank you

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618639 is a reply to message #618638]

Mon, 14 July 2014 09:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

your SELECT (inside PL/SQL) must contain INTO clause & return a single row.

URL below for working code examples

http://www.orafaq.com/wiki/Scripts#General_PL.2FSQL_Scripts

[Updated on: Mon, 14 July 2014 09:43]

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618642 is a reply to message #618638]

Mon, 14 July 2014 09:56

Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA

Senior Member

Also,

Declare in_cust_num NUMBER
in_cust_num = 2546895;

is wrong. It should be:

Declare in_cust_num NUMBER := 2546895;

SY.

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618647 is a reply to message #618639]

Mon, 14 July 2014 10:36

itmasterw
Messages: 13
Registered: July 2014
Location: NY

Junior Member

Hi, I saw that when I looked it up on the web myself, but I could not see how I would apply it in the code htat I gave here?
Any ideas?

Thank you

[Updated on: Mon, 14 July 2014 11:28]

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618648 is a reply to message #618642]

Mon, 14 July 2014 10:37

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Declare in_cust_num NUMBER;
begin
in_cust_num := 2546895;

2 good books to study:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618653 is a reply to message #618648]

Mon, 14 July 2014 11:30

itmasterw
Messages: 13
Registered: July 2014
Location: NY

Junior Member

I changed it to this but I canot see how to apply it in the code. When I try it comes back with an error.
This my code now:

Declare in_cust_num NUMBER;

BEGIN

in_cust_num := 2546895;

select cust_acct_id as MSS_ID, 1 as Parent
                from rptadmin.MV_CONQ_PARTY
                where BILL_PRNT_SUB_ID = :in_cust_num
                and BILL_PRNT_SUB_ID <> cust_acct_nbr
                Union
                select cust_acct_id as MSS_ID, 0 as Parent
                from asap.cust_acct
                where cust_acct_nbr = TO_CHAR(:in_cust_num);
END;

[Updated on: Mon, 14 July 2014 11:40]

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618654 is a reply to message #618653]

Mon, 14 July 2014 11:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

DECLARE 
    in_cust_num NUMBER := 2546895; 
BEGIN 
    SELECT cust_acct_id AS MSS_ID, 
           1            AS Parent 
    FROM   rptadmin.mv_conq_party 
    WHERE  bill_prnt_sub_id = in_cust_num 
           AND bill_prnt_sub_id <> cust_acct_nbr 
    UNION 
    SELECT cust_acct_id AS MSS_ID, 
           0            AS Parent 
    FROM   asap.cust_acct 
    WHERE  cust_acct_nbr = To_char(in_cust_num); 
END; 

/

post whole session including any & all errors

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618656 is a reply to message #618654]

Mon, 14 July 2014 11:55

itmasterw
Messages: 13
Registered: July 2014
Location: NY

Junior Member

Hi,
Unfortantly that still did not work.
It is aying that "an INTO clause is expected in this SELECT statement", but again I do not wee how you would apply this.
Any ideas I would really appreciate.

Thank you

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618657 is a reply to message #618656]

Mon, 14 July 2014 12:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>It is aying that "an INTO clause is expected in this SELECT statement",
I told you this in my first response.

Did you look at the coding examples in URL that I posted for you?

We don't have your tables.
We don't have your data.
We don't have your requirements.
We can't write your code when we lack all the above.

How will you & I know when correct solution has been posted?

[Updated on: Mon, 14 July 2014 12:04]

Report message to a moderator

Re: Declare and assign a value ot a variable [message #618659 is a reply to message #618657]

Mon, 14 July 2014 12:16

itmasterw
Messages: 13
Registered: July 2014
Location: NY

Junior Member

I did go there and there is a ton links, of which I could not find any that applyed here; of course I did not go into each one of them.
Anywas I am sorry I did not mean to upset anyone I thought that this was a an easy question.
I will just drop this, sorry to have bothered you.

Thanks

Report message to a moderator