PL-SQL error

I am writing below small pl-sql program but I face errors. I look at but I could not find the solution.

Help me please.


DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
 
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ;';
         
        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAA', 'FIRM_CODE' INTO FIELD_NAME_COUNT;  
 
    END LOOP;
   
   
END;


ERROR IS

DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
        --MY_SQL := 'SELECT COUNT(*) FROM '|| KAYIT.TABLE_NAME ||' WHERE OWNER = :a AND COLUMN_NAME = :b ;'
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ;';
         
        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAAA', 'FIRM_CODE' INTO FIELD_NAME_COUNT;  
       -- DBMS_OUTPUT.PUT_LINE (KAYIT.TABLE_NAME || '|' FIELD_NAME_COUNT);  
    END LOOP;
   
   
END;
Error at line 1
ORA-06550: satir 17, sütun 63:
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

   , * & = - + ; < / > at in is mod remainder not rem return
   returning <üstel (**)> <> or != or ~= >= <= <> and or like
   like2 like4 likec between || multiset member submultiset
The symbol "return" was substituted for "INTO" to continue.

Script Terminated on line 1.
scientiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
Try replacing
EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAA', 'FIRM_CODE' INTO FIELD_NAME_COUNT;  
with
EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA', 'FIRM_CODE' ;  
0
scientiaAuthor Commented:
Still I face same error.
0
cyberkiwiCommented:
       EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAAA', 'FIRM_CODE';  
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

cyberkiwiCommented:
Can you confirm with this?
DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
 
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ;';
         
        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAAA', 'FIRM_CODE';
 
    END LOOP;
   
   
END;

Open in new window

0
ISCCommented:
Trying removing the comma at the end of the line below and then re-compile...

FETCH CUR_TABLO_ISMI INTO KAYIT;
0
ISCCommented:
ignore my previous comment its wrong...
0
scientiaAuthor Commented:
Same error error is below.

DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
 
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ;';
         
        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA','FIRM_CODE';
 
    END LOOP;
   
   
END;
Error at line 1
ORA-00911: geçersiz karakter  // invalid crackter
ORA-06512: konum  satir 17 // at line 17
0
cyberkiwiCommented:
ah, not a row.
Try this then

        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAAA', 'FIRM_CODE' RETURNING INTO FIELD_NAME_COUNT;  
0
scientiaAuthor Commented:
Error is same in below,

I want to select FIRM_CODE coloum included tables and then there is need to me column count of this tables.  So I opened an cursor that CUR_TABLO_ISMI to select these type tables and I try to write dynamic sql with execute immediate using KAYIT.TABLE_NAME as parameter .

Thanks for immediate help.



DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
 
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ;';
         
        --EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AIG','FIRM_CODE';
        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAA', 'FIRM_CODE' RETURNING INTO FIELD_NAME_COUNT;  
 
    END LOOP;
   
   
END;
Error at line 1
ORA-00911: geçersiz karakter // invaild cracter
ORA-06512: konum  satir 18 // at line 18

Script Terminated on line 1.

0
cyberkiwiCommented:
        MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2';
         
        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AIG','FIRM_CODE';


This is the right form, but you should not have a ; inside MY_SQL
0
scientiaAuthor Commented:
cybrtkiwi ; your commet is very useful I delete ; and I thing I solve the one of the erors. but there is another error is still here.

DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
       
 
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AIG.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ';
         
        --EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA','FIRM_CODE';
        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAA, 'FIRM_CODE' RETURNING INTO FIELD_NAME_COUNT;  
 
    END LOOP;
   
   
END;
Error at line 1
ORA-06547: INSERT, UPDATE veya DELETE deyimleriyle birlikte RETURNING yan tümcesi kullanilmali
ORA-06512: konum  satir 18
0
cyberkiwiCommented:
Use this:

        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA','FIRM_CODE';
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
scientiaAuthor Commented:
CYBERWIKI, error is changed. below I ting we take way.


DECLARE
    CURSOR CUR_TABLO_ISMI IS SELECT OWNER,TABLE_NAME FROM DBA_TAB_COLUMNS WHERE OWNER ='WIN_AIG' AND COLUMN_NAME = 'FIRM_CODE';
    KAYIT CUR_TABLO_ISMI%ROWTYPE;
    FIELD_NAME_COUNT VARCHAR2(200);
    MY_SQL VARCHAR2(2000);
BEGIN
    OPEN CUR_TABLO_ISMI;
   
           
    LOOP
        FETCH CUR_TABLO_ISMI INTO KAYIT;
        EXIT WHEN CUR_TABLO_ISMI%NOTFOUND;
     
         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ';
                   
        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA','FIRM_CODE';
 
    END LOOP;
   
   
END;
Error at line 1
ORA-00904: "COLUMN_NAME": geçersiz belirleyici  // "COLUMN_NAME": not vaild identifier
ORA-06512: konum  satir 16 // at line 16

Script Terminated on line 1.


VIEW DBA_TAB_COLUMNS
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)                
 TABLE_NAME                                NOT NULL VARCHAR2(30)                
 COLUMN_NAME                               NOT NULL VARCHAR2(30)                
 DATA_TYPE                                          VARCHAR2(106)              
 DATA_TYPE_MOD                                      VARCHAR2(3)                
 DATA_TYPE_OWNER                                    VARCHAR2(30)                
 DATA_LENGTH                               NOT NULL NUMBER                      
 DATA_PRECISION                                     NUMBER                      
 DATA_SCALE                                         NUMBER                      
 NULLABLE                                           VARCHAR2(1)                
 COLUMN_ID                                          NUMBER                      
 DEFAULT_LENGTH                                     NUMBER                      
 DATA_DEFAULT                                       LONG                        
 NUM_DISTINCT                                       NUMBER                      
 LOW_VALUE                                          RAW(32)                    
 HIGH_VALUE                                         RAW(32)                    
 DENSITY                                            NUMBER                      
 NUM_NULLS                                          NUMBER                      
 NUM_BUCKETS                                        NUMBER                      
 LAST_ANALYZED                                      DATE                        
 SAMPLE_SIZE                                        NUMBER                      
 CHARACTER_SET_NAME                                 VARCHAR2(44)                
 CHAR_COL_DECL_LENGTH                               NUMBER                      
 GLOBAL_STATS                                       VARCHAR2(3)                
 USER_STATS                                         VARCHAR2(3)                
 AVG_COL_LEN                                        NUMBER                      
 CHAR_LENGTH                                        NUMBER                      
 CHAR_USED                                          VARCHAR2(1)                
 V80_FMT_IMAGE                                      VARCHAR2(3)                
 DATA_UPGRADED                                      VARCHAR2(3)                
 HISTOGRAM                                          VARCHAR2(15)
0
cyberkiwiCommented:
The error is here

         MY_SQL := 'SELECT COUNT(*) FROM WIN_AAA.'|| KAYIT.TABLE_NAME ||  ' WHERE OWNER = :1 AND COLUMN_NAME = :2 ';

KAYIT.TABLE_NAME => a table name
In this table, there is no column named "COLUMN_NAME"
0
scientiaAuthor Commented:
Thanks my friend. Your solution worked.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.