CODEWITHSUNDEEP
oracle-databaseplsql
Niall
Niall

Reputation: 518

Storing a Query In A Variable

I've been looking into storing a query into a variable but I am having trouble doing so. I keep getting the error

wrong number or types of arguments in call to '||'

This is what I tried

DECLARE
addresses VARCHAR(200);

 CURSOR allAddresses IS
      SELECT * 
      FROM ALL_ADDRESS;
BEGIN
 FOR rec IN allAddresses LOOP
      addresses := addresses || rec;  
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(addresses);
END;

Upvotes: 1

Views: 77

Answers (4)

Sabarish Mahalingam
Sabarish Mahalingam

Reputation: 145

For this scenario you should understand cursor variable calling things:

DECLARE
  addresses VARCHAR(200);

  CURSOR allAddresses IS
    SELECT * 
    FROM ALL_ADDRESS;
BEGIN
  FOR rec IN allAddresses LOOP
    addresses := addresses || rec.a;   -- A as attribute of your cursor
--- so your need to access your cursor attribute using loop name i.e rec
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(addresses);
    END;
    /

whenever you are trying to access your cursor record, you should call cursor variable before the attribute. Hope it will help you.. thank you

Upvotes: 2

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You cannot use a CURSOR for loop iterator directly in your expressions.You can only refer to the column names as rec.col1 , rec.col2 ..etc

DECLARE
addresses VARCHAR(200);

 CURSOR allAddresses IS
      SELECT * 
      FROM ALL_ADDRESS;
BEGIN
 FOR rec IN allAddresses LOOP
      addresses := addresses || rec.<address_column>;  
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(addresses);
END;

Upvotes: 2

MT0
MT0

Reputation: 167867

You refer to the columns in the record rec that you are using by name:

DECLARE
  addresses VARCHAR(200);

  CURSOR allAddresses IS
    SELECT * 
    FROM ALL_ADDRESS;
BEGIN
  FOR rec IN allAddresses LOOP
    addresses := addresses || rec.house_number
                           || ' ' || rec.house_name
                           || ' ' || rec.address1
                           || ' ' || rec.address2;  
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(addresses);
END;
/

Upvotes: 3

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this - 

addresses := addresses || '' || rec;

Upvotes: -1

Related Questions