Solved

PL-SQL error

Posted on 2010-08-18
15
868 Views
Last Modified: 2013-12-07
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.
0
Comment
Question by:scientia
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 22

Assisted Solution

by:Om Prakash
Om Prakash earned 83 total points
ID: 33463368
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
 

Author Comment

by:scientia
ID: 33463386
Still I face same error.
0
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 417 total points
ID: 33463393
       EXECUTE IMMEDIATE MY_SQL INTO FIELD_NAME_COUNT USING 'WIN_AAAA', 'FIRM_CODE';  
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33463399
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
 
LVL 1

Expert Comment

by:ISC
ID: 33463408
Trying removing the comma at the end of the line below and then re-compile...

FETCH CUR_TABLO_ISMI INTO KAYIT;
0
 
LVL 1

Expert Comment

by:ISC
ID: 33463418
ignore my previous comment its wrong...
0
 

Author Comment

by:scientia
ID: 33463424
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 417 total points
ID: 33463511
ah, not a row.
Try this then

        EXECUTE IMMEDIATE MY_SQL USING 'WIN_AAAA', 'FIRM_CODE' RETURNING INTO FIELD_NAME_COUNT;  
0
 

Author Comment

by:scientia
ID: 33463636
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
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 417 total points
ID: 33463801
        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
 

Author Comment

by:scientia
ID: 33463838
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 417 total points
ID: 33463867
Use this:

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

Author Comment

by:scientia
ID: 33463918
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
 
LVL 58

Assisted Solution

by:cyberkiwi
cyberkiwi earned 417 total points
ID: 33463953
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
 

Author Closing Comment

by:scientia
ID: 33464092
Thanks my friend. Your solution worked.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query 15 63
Parametric query in oracle 6 42
Oracle SQL Select within a Where Clause 9 42
dates - loop 12 42
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now