Solved

PL-SQL error

Posted on 2010-08-18
15
883 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

724 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