Home » SQL & PL/SQL » SQL & PL/SQL » DBMS STATS import issue. (oracle 10g)
DBMS STATS import issue. [message #299944] Wed, 13 February 2008 18:14 Go to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
I am trying to import tables and indexes from Production to Dev database. Since the partition of tables does not exist in Dev the dbms_stats.import_schema_stats is failing. So am trying to import identical tables and indexes.

Any other solution would be helpful.

HEre is the code,

Declare
cursor C1
select table_name from all_tables
where owner='CLAMSAPP' and table_name in
(select table_name from all_tables@CLDEV where owner='CLAMSAPP');
Begin
For rec in C1
loop
exec dbms_stats.export_table_stats('CLAMSAPP',rec.table_name,NULL,'STATS_NEW',NULL,TRUE);
End loop;
End;

but am receving error message like this

Error:

exec dbms_stats.export_table_stats('CLAMSAPP','rec.table_name',NULL,'STATS_NEW',NULL,TRUE);
*

ERROR at line 13:
ORA-06550: line 13, column 6:

PLS-00103: Encountered the symbol "DBMS_STATS" when expecting one of the following:

:= . ( @ % ;

The symbol ":=" was substituted for "DBMS_STATS" to continue.

Can you please suggest me to resolve this issue.
Re: DBMS STATS import issue. [message #299945 is a reply to message #299944] Wed, 13 February 2008 18:26 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
What do you think exec does? Is it a PL/SQL keyword? Are you sure?

Michael
Re: DBMS STATS import issue. [message #299946 is a reply to message #299944] Wed, 13 February 2008 18:31 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
Exec command executes built in package.
Re: DBMS STATS import issue. [message #299949 is a reply to message #299944] Wed, 13 February 2008 18:37 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Exec command executes built in package.
Please provide URL which shows how/where EXEC is used in PL/SQL.
Re: DBMS STATS import issue. [message #299952 is a reply to message #299944] Wed, 13 February 2008 18:48 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
SQL> CREATE OR REPLACE PACKAGE not_a_built_in_package
  2  IS
  3    PROCEDURE foo;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY not_a_built_in_package
  2  IS
  3    PROCEDURE foo
  4    IS
  5    BEGIN
  6      --do stuff
  7      null;
  8    END;
  9  END;
 10  /

Package body created.

SQL>  exec not_a_built_in_package.foo;

PL/SQL procedure successfully completed.

SQL>


Like I said, Are you sure?

Michael
Re: DBMS STATS import issue. [message #299960 is a reply to message #299944] Wed, 13 February 2008 19:50 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
yes. this is how i used the exec command to import. Hope am posting in the correct section
Re: DBMS STATS import issue. [message #299961 is a reply to message #299944] Wed, 13 February 2008 20:02 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
So back to my original question. Is exec a PLSQL keyword? If you think it is, then please ( as anacedent suggests ) provide URL of something showing exec being used in PLSQL.

Note : The example above shows exec being used as a SQLPLUS command.

Re: DBMS STATS import issue. [message #299962 is a reply to message #299944] Wed, 13 February 2008 20:07 Go to previous messageGo to next message
kulkarni_m6
Messages: 10
Registered: February 2008
Junior Member
Now i got your point. You mean to say we cannot use exec command in pl/sql program am i right?.

If not how can i insert this command in PL/SQL program?

Re: DBMS STATS import issue. [message #299995 is a reply to message #299962] Wed, 13 February 2008 23:43 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@kulkarni_m6 : just get rid of the exec from your code.its a sqlplus command.

begin
dbms_stats.export_table_stats(....);
end;


please refer the oracle doc.

regards,




Previous Topic: PLSQL Table Arrays
Next Topic: Difference between DES and DES3 procedures in Obfuscation toolkit
Goto Forum:
  


Current Time: Sun Dec 04 09:00:28 CST 2016

Total time taken to generate the page: 0.04077 seconds