Home » SQL & PL/SQL » SQL & PL/SQL » Error is missing keyword when am executing dynamic PL/SQL program
Error is missing keyword when am executing dynamic PL/SQL program [message #256536] Sun, 05 August 2007 13:36 Go to next message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

DECLARE
     lv_count_num     PLS_INTEGER:=0;
     lv_sql_txt      VARCHAR2(200);

     CURSOR cur_counts 
     IS
          SELECT table_name
          FROM user_tables
          ORDER BY table_name;
BEGIN

      FOR cur_counts_rec IN cur_counts 
      LOOP
        lv_sql_txt := 'SELECT count(*) INTO 
                      lv_count_num  FROM ' ||
                      cur_counts_rec.table_name;

        dbms_output.put_line (lv_sql_txt);
        EXECUTE IMMEDIATE lv_sql_txt; 

        DBMS_OUTPUT.put_line (' Table Name: ' ||
             RPAD(cur_counts_rec.table_name, 15) ||
             'Rows: ' || lv_count_num);
      END LOOP;
END;
/

[Updated on: Mon, 06 August 2007 00:51]

Report message to a moderator

Re: Error is missing keyword when am executing dynamic PL/SQL program [message #256537 is a reply to message #256536] Sun, 05 August 2007 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
No code formatting (see http://www.orafaq.com/forum/t/59964/74940/)

Keep in mind that when using EXECUTE IMMEDIATE it is the equivelent to starting a new SQL*Plus session.

What happens when you use CUT & PASTE & invoke the SQL statement in SQL*Plus?

Re: Error is missing keyword when am executing dynamic PL/SQL program [message #256570 is a reply to message #256537] Mon, 06 August 2007 00:56 Go to previous messageGo to next message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

Thanks for your information.

I have changed the code format.

Still I didn't get the what have to do. Can you please explain clearly.

Thanks in advance.

Regards,
Ram Kishan Nagumalla
Re: Error is missing keyword when am executing dynamic PL/SQL program [message #256573 is a reply to message #256536] Mon, 06 August 2007 00:59 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I have changed the code format.
Since you chose NOT to share the changes, I refuse to guess at a solution.

You're On Your Own (YOYO)!
Re: Error is missing keyword when am executing dynamic PL/SQL program [message #256574 is a reply to message #256536] Mon, 06 August 2007 01:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Also one more suggestion. It will be a lot more useful if you post the error message . For example like this

  1  DECLARE
  2  lv_count_num PLS_INTEGER:=0;
  3  lv_sql_txt VARCHAR2(200);
  4  -- NUMBER(10) :=0;
  5  CURSOR cur_counts IS
  6  SELECT table_name
  7  FROM user_tables
  8  ORDER BY table_name;
  9  BEGIN
 10  FOR cur_counts_rec IN cur_counts LOOP
 11  dbms_output.put_line (cur_counts_rec.table_name);
 12  lv_sql_txt := 'SELECT count(*) into lv_count_num FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt;
 16  DBMS_OUTPUT.put_line (' Table Name: ' ||
 17  RPAD(cur_counts_rec.table_name, 15) || 'Rows: ' ||
 18  lv_count_num);
 19  END LOOP;
 20* END;
SQL> /
TEST
SELECT count(*) into lv_count_num FROM TEST
DECLARE
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 15



From the error message it is quite obvious it is not a valid sql statement.

Change the execute immediate as follows

 12  lv_sql_txt := 'SELECT count(*) into lv_count_num FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt into lv_count_num;

 to

 12  lv_sql_txt := 'SELECT count(*) FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt into lv_count_num;



Regards

Rajaram
Re: Error is missing keyword when am executing dynamic PL/SQL program [message #257140 is a reply to message #256574] Tue, 07 August 2007 11:29 Go to previous message
kishan_nag
Messages: 19
Registered: June 2005
Location: Mumbai
Junior Member

Thanks a lot.

Now code is working fine.

Regards,
Ram Kishan
Previous Topic: XML to oracle
Next Topic: PL/SQL function (merged)
Goto Forum:
  


Current Time: Wed Dec 07 18:21:00 CST 2016

Total time taken to generate the page: 0.09432 seconds