Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE trouble in stored procedures (any)
EXECUTE IMMEDIATE trouble in stored procedures [message #393075] Fri, 20 March 2009 04:44 Go to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
Hi all,
I ask you for help please. Now this forum is my only help.
This is a trouble part of whole STORED procedure. The problem is that i need to use execute immediate command and it is necessary to close the contain in quotes but this sub command: DBMS_METADATA.GET_DDL('INDEX', '||row.index_name||')
has to define 'INDEX' as type of object that dll I want to get. But inside execute immediate the quote ' involve end of command.
I have tried to use some escape characters as ' and " . Nothing helped.

CREATE OR REPLACE PROCEDURE proc_nmcrl_before_import AS

BEGIN

FOR ROW IN (SELECT DISTINCT index_name FROM user_indexes WHERE table_name IN ('KNSEGA', 'KNSEGB', 'KNSEGC', 'KNSEGK'))

LOOP

EXECUTE IMMEDIATE WITH QUOTES ON 'INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', '||row.index_name||') from user_indexes';

END LOOP;

END;

/



Any help would be appreciated. Thanks
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393078 is a reply to message #393075] Fri, 20 March 2009 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Double the quotes inside the string.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals) and search before posting.

Regards
Michel
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393081 is a reply to message #393075] Fri, 20 March 2009 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's a good idea if you're having trouble building a dynamic string to display it so you can see what you've done.
So declare a variable to hold the dynamic string, build it up, and use dbms_output to display it, then you'll be able to see if you've got the quotes right or not.
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393082 is a reply to message #393078] Fri, 20 March 2009 04:59 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
sorry - i tried to repair my post. doubling quotes doesnt help.

Hi all,
I ask you for help please. Now this forum is my only help.
This is a trouble part of whole STORED procedure. The problem is that i need to use execute immediate command and it is necessary to close the contain in quotes but this sub command:
DBMS_METADATA.GET_DDL('INDEX', '||row.index_name||')

... has to define 'INDEX' as type of object that dll I want to get.
But inside execute immediate the quote ' involve end of command.
I have tried to use some escape characters as ' and " . Nothing helped.

CREATE OR REPLACE PROCEDURE proc_nmcrl_before_import AS

BEGIN

	FOR ROW IN (SELECT DISTINCT index_name FROM user_indexes WHERE table_name IN ('KNSEGA', 'KNSEGB', 'KNSEGC', 'KNSEGK'))

	LOOP

		EXECUTE IMMEDIATE WITH QUOTES ON 'INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', '||row.index_name||') from user_indexes';

	END LOOP;

END;

/




Any help would be appreciated. Thanks
icon14.gif  Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393083 is a reply to message #393081] Fri, 20 March 2009 05:01 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
thanks,
ok i will try ...
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393090 is a reply to message #393081] Fri, 20 March 2009 05:26 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
i have changed it to this:

CREATE OR REPLACE PROCEDURE proc_nmcrl_before_import AS



fixed_string varchar2(8);

fixed_string :='INDEX';

BEGIN

	FOR ROW IN (SELECT DISTINCT index_name FROM user_indexes WHERE table_name IN ('KNSEGA', 'KNSEGB', 'KNSEGC', 'KNSEGK'))

	LOOP

		dbms_output.put_line('INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('||fixed_string||', '||row.index_name||') from user_indexes ');

	END LOOP;

END;

/



and procedure was compiled with errors. After show error pl/sql displays this message:

Errors for PROCEDURE PROC_NMCRL_BEFORE_IMPORT:

4/14 PLS-00103: Encountered the symbol "=" when expecting one of the

following:

constant exception <identifikßtor>

<odd?lenř_identifikßtor_v_dvojitřch_uvozovkßch> table LONG_

double ref char time timestamp interval date binary national

character nchar

The symbol "<identifikßtor>" was substituted for "=" to continue.

Am i missing something?
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393091 is a reply to message #393090] Fri, 20 March 2009 05:27 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
Sorry for national localization of message Sad
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393092 is a reply to message #393090] Fri, 20 March 2009 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Follow my advice.

And keep your lines in 80 characters.

Regards
Michel
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393094 is a reply to message #393075] Fri, 20 March 2009 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
This is not valid code for a declare section:
fixed_string :='INDEX';


Using this fixed_string isn't going to solve your issue either.

Michel Cadot wrote:

Double the quotes inside the string.


Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393098 is a reply to message #393094] Fri, 20 March 2009 05:42 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
sorry i am newbie in forums. for:
dbms_output.put_line('INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL(''INDEX'', '||row.index_name||') from user_indexes ');


it works right and rows are inserted in table exactly as i want Smile
...
but when i replace "dbms_output.put_line"
by "execute immediate" and execute
procedure, it will fire up with message:


SQL> exec proc_nmcrl_before_import

BEGIN proc_nmcrl_before_import; END;



*

ERROR at line 1:

ORA-00904: "I_KNSEGAC_KINC": invalid identifier

ORA-06512: at "NMCRL.PROC_NMCRL_BEFORE_IMPORT", line 6

ORA-06512: at line 1

any advice, what is wrong here?
thanks

is the encoding better now?
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393099 is a reply to message #393098] Fri, 20 March 2009 05:46 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
*encoding
*formatting Smile

for explanation line 6 is that with execute immediate
command...

and "I_KNSEGAC_KINC" is proper name of existing index
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393102 is a reply to message #393075] Fri, 20 March 2009 05:49 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does that mean it's working now?
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393103 is a reply to message #393102] Fri, 20 March 2009 05:51 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> drop table tst;

Table dropped.

SQL> create table tst (a varchar2(1000), b varchar2(2000));

Table created.

SQL> CREATE OR REPLACE PROCEDURE proc_nmcrl_before_import AS
  2  BEGIN
  3     FOR ROW IN (SELECT DISTINCT index_name FROM user_indexes WHERE table_name IN ('KNSEGA', 'KNSEGB', 'KNSEGC', 'KNS
EGK'))
  4     LOOP
  5             INSERT INTO tst SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', '||row.index_name||') from user_indexe
s;
  6     END LOOP;
  7  END;
  8
  9  /

Procedure created.

SQL> create index tst_ind on tst (a);

Index created.

SQL> exec proc_nmcrl_before_import

PL/SQL procedure successfully completed.

SQL> select count(*) from tst;

  COUNT(*)
----------
         0

SQL> create table KNSEGA (b int);

Table created.

SQL> create index KNSEGA_IND on KNSEGA(b);

Index created.

SQL> exec proc_nmcrl_before_import
BEGIN proc_nmcrl_before_import; END;

*
ERROR at line 1:
ORA-31603: object "||row.index_name||" of type INDEX not found in schema "BABU"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
ORA-06512: at "BABU.PROC_NMCRL_BEFORE_IMPORT", line 5
ORA-06512: at line 1


Modified Procedure:

CREATE OR REPLACE PROCEDURE proc_nmcrl_before_import AS 
BEGIN
	FOR ROW IN (SELECT DISTINCT index_name FROM user_indexes WHERE table_name IN ('KNSEGA', 'KNSEGB', 'KNSEGC', 'KNSEGK'))
	LOOP
		INSERT INTO tst SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', index_name) from user_indexes;
	END LOOP;
END;

/

SQL> exec proc_nmcrl_before_import

PL/SQL procedure successfully completed.

SQL> select count(*) from tst;

  COUNT(*)
----------
         2

SQL> select * from tst;

A
--------------------------------------------------------------------------------
B
--------------------------------------------------------------------------------
KNSEGA_IND

  CREATE INDEX "BABU"."KNSEGA_IND" ON "BABU"."KNSEGA" ("B")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "BABU"



A
--------------------------------------------------------------------------------
B
--------------------------------------------------------------------------------
TST_IND

  CREATE INDEX "BABU"."TST_IND" ON "BABU"."TST" ("A")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "BABU"



SQL>


Are you expecting the above code??

Babu

[Updated on: Fri, 20 March 2009 05:57]

Report message to a moderator

Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393104 is a reply to message #393075] Fri, 20 March 2009 05:53 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
unfortunately not.
for code with "dbms_output.put_line" it behaves fine:
it will put out these lines:
INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', I_KNSEGAC_KINC) from user_indexes


INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', I_KNSEGA_KNSN) from user_indexes


INSERT INTO temporary_for_index SELECT index_name, DBMS_METADATA.GET_DDL('INDEX', I_KNSEGBC_KNSC) from user_indexes


but when i replace it with
"execute immediate" all goes wrong Sad

Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393105 is a reply to message #393103] Fri, 20 March 2009 05:59 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
without execute immediate it is compiled with
errors ....
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393106 is a reply to message #393105] Fri, 20 March 2009 06:00 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

@srg_skinner

Can you check the above update.

Babu
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393110 is a reply to message #393075] Fri, 20 March 2009 06:04 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
@gentlebabu

oh sorry my fault i used your table, now it seems
things gonna better, many thanks
i suppose i will give an info in few minutes
about closing this topic
Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393111 is a reply to message #393110] Fri, 20 March 2009 06:08 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks for the feedback.

Babu
icon14.gif  Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393113 is a reply to message #393106] Fri, 20 March 2009 06:10 Go to previous messageGo to next message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
exhausting reply from babu Smile
babu you are [censored] genius, thank you very much.
but i would like to thank to all, who tried to help me.
i almost become hopeless Smile

this message #393103 is solvation of my trouble
once again thanks to all !!

Mod Edit: Please refrain from using such language. Thanks

[Updated on: Fri, 20 March 2009 07:27] by Moderator

Report message to a moderator

Re: EXECUTE IMMEDIATE trouble in stored procedures [message #393141 is a reply to message #393111] Fri, 20 March 2009 08:54 Go to previous message
srg_skinner
Messages: 12
Registered: March 2009
Junior Member
@gentlebabu
sorry,but i didnt think it bad
but as a compliment.
i hope babu got it in this meaning.

... and it doesnt always mean the vulgarism i thought.
but i agree and i will avoid using such a words, yup Smile ?
Previous Topic: Invalid cursor while inserting rows
Next Topic: queston on view (merged)
Goto Forum:
  


Current Time: Sun Dec 04 16:30:30 CST 2016

Total time taken to generate the page: 0.04567 seconds