Home » SQL & PL/SQL » SQL & PL/SQL » dbms_sql
dbms_sql [message #336572] Mon, 28 July 2008 03:35 Go to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi all,

Oracle : 9.2.0.7.0
OS : AIX (5.3)

I have the following anonymous PL/SQL block:
set serveroutput on
set lines 160
declare
  cursor c1 is select owner, table_name from dba_tables;
  r_owner dba_tables.owner%type;
  r_table dba_tables.table_name%type;
  res number;
  sqlstmt varchar(200);
  cursor_name int;
  ff int;
begin
  dbms_output.enable(999999);
  for tabje in c1 loop
    r_owner:=tabje.owner;
    r_table:=tabje.table_name;
    sqlstmt:='select count(*) from :1.:2';

    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, sqlstmt, dbms_sql.native);
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':1', r_owner);
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':2', r_table);
    DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, res);
    ff := dbms_sql.execute_and_fetch(cursor_name);

    DBMS_SQL.COLUMN_VALUE(cursor_name, 1, res);

    DBMS_SQL.CLOSE_CURSOR(cursor_name);

    dbms_output.put_line('Table : '||r_table||' Owner : '||r_owner||' Aantal rijen : '||res);
  end loop;

end;
/


It's a bit of PL/SQL which counts rows of all tables in a database. I know that there are several (much more efficient) ways of achieving this. But I use this "case" to learn a little about dbms_sql. So, at this moment I don't care to much about efficiency Smile

When I run this code I get thee following:
Quote:

SQL> @rowcount
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at line 17


SQL>



I have searched the internet for a while, but I can't seem to find the answers or causes to this problem.

Does anyone have ideas about waht the cause of this error can be? Pointers in the right direction would be appreciated


Best regards,

Martijn


--EDIT:
I forgot to mention. When debuging I noticed that the error occurs at the following line:
DBMS_SQL.PARSE(cursor_name, sqlstmt, dbms_sql.native);

[Updated on: Mon, 28 July 2008 03:38]

Report message to a moderator

Re: dbms_sql [message #336574 is a reply to message #336572] Mon, 28 July 2008 03:47 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
You cannot use bind variables in this way. i.e. you cannot bind object names. You will need to concatenate the parameter values into your dynamic string.
Re: dbms_sql [message #336575 is a reply to message #336574] Mon, 28 July 2008 03:50 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Ah OK.

For "execute immediately" that is mentioned explicitly in the documentation. I didn't see that for dbms_sql (probably my sloppy reading Sad)

tnx. I will try what you suggest.


Cheers
Martijn
Re: dbms_sql [message #336578 is a reply to message #336575] Mon, 28 July 2008 04:08 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Works like a charm Smile

thanks again

Martijn
Re: dbms_sql [message #336580 is a reply to message #336578] Mon, 28 July 2008 04:11 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Glad to hear it Smile
Previous Topic: which is better Bulk Insert/Update or Merge Command
Next Topic: Performance Essue
Goto Forum:
  


Current Time: Sat Dec 03 14:22:37 CST 2016

Total time taken to generate the page: 0.17897 seconds