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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
cyberkiwiConnect With a Mentor Commented:
Use this:

        EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAA','FIRM_CODE';
0
 
Om PrakashConnect With a Mentor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
cyberkiwiConnect With a Mentor Commented:
       EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAAA', 'FIRM_CODE';  
0
 
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
 
cyberkiwiConnect With a Mentor Commented:
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
 
cyberkiwiConnect With a Mentor Commented:
        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
 
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
 
cyberkiwiConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.