Home » SQL & PL/SQL » SQL & PL/SQL » select into a variable problem (oracle 10g)
select into a variable problem [message #354949] Wed, 22 October 2008 01:09 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
hi
i have created a cursor that retrives table names
and i have a variable that stores the table name.

Now i want to count all rows from tables and assign the value to another variable 'total'
but i have not been able to do so..
please help.

part of my code is ...

----------------------------------------

DECLARE
CURSOR GRP_cursor IS
SELECT DISTINCT customerid FROM TBL_CUSTOMER;

cgroup NUMBER;
tblname VARCHAR2(30);
total NUMBER;

BEGIN
FOR a IN GRP_cursor LOOP
BEGIN
cgroup := a.customerid ;

tblname:='CLIENT_'||cgroup;
Dbms_Output.put_line (tblname);
begin
execute immediate 'total:=SELECT COUNT(*) FROM '||tblname;
END;
.....

And error is:
--------

ORA-00900: invalid SQL statement
ORA-06512: at line 19
-----------------------line 19 is : begin

please help...

thanx.
Re: select into a variable problem [message #354960 is a reply to message #354949] Wed, 22 October 2008 01:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You shall not guess the syntax and find it in Oracle documentation instead. It is accessible e.g. online on http://tahiti.oracle.com/.

PL/SQL User's Guide and Reference
Chapter 7 Performing SQL Operations with Native Dynamic SQL
Using the EXECUTE IMMEDIATE Statement in PL/SQL

or

PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
EXECUTE IMMEDIATE Statement
(with examples referenced on the bottom)
Re: select into a variable problem [message #354964 is a reply to message #354949] Wed, 22 October 2008 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: select into a variable problem [message #354965 is a reply to message #354949] Wed, 22 October 2008 01:39 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Your execute immediate statment is wrong.

Use like this.

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tblname into total;
Re: select into a variable problem [message #354995 is a reply to message #354965] Wed, 22 October 2008 02:56 Go to previous message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
thanx a million for your suggestion.

It really worked.
I also found another workaround using vquery string

THANKS
Previous Topic: Find a row with NULL Value
Next Topic: ORA-00904:
Goto Forum:
  


Current Time: Sat Dec 03 18:07:56 CST 2016

Total time taken to generate the page: 0.12844 seconds