CODEWITHSUNDEEP
plsqldeveloper
user2343328
user2343328

Reputation: 3

SQL query for PL/SQL scripting

I am writing a PL/SQL Procedure on SQL Developer, and I am running the below query :

EXECUTE IMMEDIATE 'select count(*) from '||var_Table||' into var_NumRec where COMPANY = '||var_company||' order by COMPANY';
OR
EXECUTE IMMEDIATE 'select count(*) from '||var_Table||' into '||var_NumRec||' where COMPANY = '||var_company||' order by COMPANY';

I am getting error "SQL command not properly ended"

The below query is working fine, but I have to add the "where" clause to this query:

select count(*) from '||var_Table into var_NumRec;

Upvotes: 0

Views: 656

Answers (2)

Suresh
Suresh

Reputation: 489

I have checked the procedure and running below code , which is working fine.

DECLARE 
    var_NumRec NUMBER(15); 
    var_Table VARCHAR2(40); 
    var_company VARCHAR2(25) := 'ABC'; 
    TYPE 
        my_nested_table 
        IS TABLE OF VARCHAR2(40); 
    var_Direct my_nested_table := my_nested_table ('Employee', 'Accounts', 'FEE'); 
BEGIN 
    FOR i IN 1.. var_direct.count 
    LOOP 
        EXECUTE IMMEDIATE 'select count(*) from '||var_Direct(1) ||' where 1=1' into var_NumRec ;  
        DBMS_OUTPUT.PUT_LINE('ABC : '||var_NumRec); 
    END LOOP;
END;

You are using identifier "Company" which is invalid here, so earlier code was not running.

Upvotes: 1

Suresh
Suresh

Reputation: 489

Use below query in place of your execute immediate in pl SQL procedure :

EXECUTE IMMEDIATE 'select count(*) from '||var_Table||'  where COMPANY = '||var_company order by COMPANY  
into var_NumRec;

When we use Execute immediate, we should put INTO outside the statement.. Hope this will work for you.

Upvotes: 0

Related Questions