Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit (How to add a procedure into a package. )
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421126] Fri, 04 September 2009 07:34 Go to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I have about 250 procedures and each 25 is a group. These procedures are created dynamically by using definition tables. What I want to do is to group each 25 procedure in a package but I can not execute these procedures in a package since the length of the string is to big to execute. I use execute immediate or dbms_sql.parse to execute the string . What should I do ? Is there a method of adding procedures to a package or is there a way to execute strings having millions of characters ?

Thanks in advance.
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421129 is a reply to message #421126] Fri, 04 September 2009 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use dbms_sql procedure that take an array as parameter.
Have a look at documentation.

Regards
Michel
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421131 is a reply to message #421129] Fri, 04 September 2009 08:20 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
Can that array take a query like,
Insert into xtable
select a,b,c,d
from ytable
............

I tried it before but as i remember it didnt work. Maybe Im wrong.
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421132 is a reply to message #421131] Fri, 04 September 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you mean.

Post an example of what you want to do and what failed.

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

Regards
Michel
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421134 is a reply to message #421132] Fri, 04 September 2009 08:40 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
All procedures are created dynamically like.

XSTATEMENT := 'CREATE OR REPLACE PROCEDURE PROCEDURE_'||XTABLE_NAME||'(XDATA_DATE IN DATE) AS
XREC_COUNT NUMBER;
BEGIN '||
' MERGE /*+ PARALLEL */ INTO
TEMP_NORTHI.'||XTABLE_NAME||' A
USING ('||'SELECT '||
XREMOTE_FIELDS||
' FROM '||XREMOTE_FROM_CLAUSE||
' WHERE '||XWHERE_CLAUSE||') E'||
' ON (A.FRAGMENT_DATE=E.FRAGMENT_DATE
AND A.NETWORK_ID=E.NETWORK_ID) WHEN NOT MATCHED THEN' ||
' INSERT ('||XINSERT_LOCAL_FIELDS||')'||
' VALUES ('||XVALUES_LOCAL_FIELDS||');'||
' XREC_COUNT:=SQL%ROWCOUNT; '||
' INSERT INTO NORTHI_TABLE_LOGS
(TABLE_NAME,FRAGMENT_DATE,AGGREGATE_TYPE,DATA_COUNT) VALUES('''||XTABLE_NAME||''',XDATA_DATE,''LOADER'',XREC_COUNT );'||
' COMMIT; END;';
Execute Immediate xstatement;

And at the end i create following procedures in a loop by using the below code. I want to put all these procedures in a package. I can create procedures one by one but if I try to execute all of them ?

PROCEDURE_CARRSTS_5MA_TRX
PROCEDURE_CARRSTS_5MA_CLS
PROCEDURE_CARRSTS_5WA_CELL
PROCEDURE_CARRSTS_5MA_NW
PROCEDURE_CARRSTS_5MA_RGN
PROCEDURE_CARRSTS_5MA_CELL
PROCEDURE_CARRSTS2
PROCEDURE_CARRSTS
PROCEDURE_CARRSTS_5WA_CLS
PROCEDURE_CARRSTS_5WA_NW
PROCEDURE_CARRSTS_5WA_RGN
PROCEDURE_CARRSTS_5WA_TRX
PROCEDURE_CARRSTS_7MA_CELL
PROCEDURE_CARRSTS_7MA_CLS
PROCEDURE_CARRSTS_7MA_NW
PROCEDURE_CARRSTS_7MA_RGN
PROCEDURE_CARRSTS_7MA_TRX
PROCEDURE_CARRSTS_7WA_CELL
PROCEDURE_CARRSTS_7WA_CLS
PROCEDURE_CARRSTS_7WA_NW
PROCEDURE_CARRSTS_7WA_RGN
PROCEDURE_CARRSTS_7WA_TRX
PROCEDURE_CARRSTS_DA_CELL
PROCEDURE_CARRSTS_DA_CLS
PROCEDURE_CARRSTS_DA_NW
PROCEDURE_CARRSTS_DA_RGN
PROCEDURE_CARRSTS_DA_TRX
PROCEDURE_CARRSTS_H_CELL
PROCEDURE_CARRSTS_H_CLS
PROCEDURE_CARRSTS_H_NW
PROCEDURE_CARRSTS_H_RGN
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421135 is a reply to message #421134] Fri, 04 September 2009 08:44 Go to previous messageGo to next message
vesile_taskiran
Messages: 66
Registered: August 2008
Location: Turkey
Member
I tried to format the code but it gave me
Can't format input sql, make sure there is no syntax error and select correct database. Sorry.
Re: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit [message #421136 is a reply to message #421135] Fri, 04 September 2009 09:00 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
tried to format the code but it gave me
Can't format input sql,

Use code tags as explained in the guide.

Quote:
I want to put all these procedures in a package.

So do it.

Quote:
I can create procedures one by one but if I try to execute all of them ?

What does this mean?

Regards
Michel
Previous Topic: IN problem
Next Topic: condition required in trigger
Goto Forum:
  


Current Time: Tue Sep 27 05:57:40 CDT 2016

Total time taken to generate the page: 0.18563 seconds