SQL & PL/SQL :: Limit Of Number Of Sub Programs In A Package
Aug 13, 2013
I'm looking for the information on:
•Limit of number of sub programs in a package.
•Limit of number of lines in a sub-program.
•Limit of number of statements in a sub-program.
I searched on net and found below useful information.
The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.
The most closely related number that a user can access is the PARSED_SIZE in the static data dictionary view *_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.
is there any recommendation on limit of number of subprograms should exist in a package exist, If there is any guidelines/recommendation exist for these.
View 2 Replies
ADVERTISEMENT
Feb 11, 2013
What is the limit on number of partitions on a table.on many forums , 1024k-1 is given the maximum limit.But Exactly , I am not able to understand this 1024k-1.
View 2 Replies
View Related
Jun 23, 2010
if i execute
begin
insert into t values('hgahaha');
.......
....
<17000 times insert statement>
end;
I get following error
ERROR at line 1:
ORA-06550: line 16385, column 13:
PLS-00123: program too large
But instead of pl/sql block if i write the insert statements as sql file and execute it produces no error
Does it mean pl/sql has limit on number of lines of code to 16385?
View 10 Replies
View Related
Sep 13, 2007
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
[code]....
View 1 Replies
View Related
Nov 10, 2010
1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
View 2 Replies
View Related
Sep 17, 2013
,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.
Can we restrict that somehow through the database or that needs to be done through application?
View 4 Replies
View Related
Jun 23, 2011
I'm facing a problem with the starting of java programs on my Win XP system. The startup most often takes from 5 to 60min. Only in rare cases java programs start-up normally. I've these problems with Eclipse, Nitro and ProM which are all Java-based tools.
I have used STraceNT to find the cause of my problems but I couldn't figure it out. I've attached the STraceNT log file for the execution of ProM. The program runs until the last line in the log while starting without showing up anything. After 20min suddenly the programs shows up and works as it normally does. The last line in the log where the software hangs for this long time is a call to WaitForeSingleObject. Before that it seams to allocate memory. I could see similar patterns in the executions of the other java programs, but I still can't see where the problem lies. I also tried removing the JRE and install different versions.
View -1 Replies
View Related
Dec 11, 2010
I need to generate a list of the programs on all channels for a specific day showing the channel number, supplier, package, program name, rating code, and show time. This will be similar to a program guide, only not package specific. This is a date-driven report, therefore it should only display programs for a single date specified. This is what I have but I cannot get it to run.
CREATE VIEW PROG_LINEUP AS
SELECT DISTINCT S.PROGTIME AS "SHOWTIME", S.PROGNAME AS "PROGRAM TITLE",
C.CHNUM AS "CHANNEL #", SU.SUPNAME AS "SUPPLIER", R.RATING AS "RATING"
FROM SCHEDULE S, CHANNELS C, SUPPLIERS SU INNER JOIN
PROGRAM P ON P.PROGNAME = S.PROGNAME INNER JOIN
CHANNEL_PACKAGE CP ON CP.CHID = S.CHID
INNER JOIN RATING R ON R.RATINGID = P.RATINGID
WHERE S.PROGTIME = '18-DEC-10'
ORDER BY S.CHID;
View 26 Replies
View Related
Jul 31, 2012
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;"
SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT
--------------------------------------------------
PACKAGE BODY PKG_TEST IS
/*************************************************
****/
PROCEDURE proc1 IS
BEGIN
update t1 set EDITION_NAME = 'AAAAAAA';
commit;
END proc1;
[code]....
View 14 Replies
View Related
May 28, 2013
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
View 8 Replies
View Related
Jan 15, 2012
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
View 4 Replies
View Related
Nov 13, 2007
I've written a .net program for inserting blobs into our database, and it works fine, except if the path has more than 30(+) characters. I've read that this is a limitation in oracle, but the actual contents being stored is just the filename, extension, and blob itself, so I'm not actually storing anything more than 30 characters.
View 4 Replies
View Related
Jul 8, 2010
I have just created a profile as below.
create profile test_idle limit idle_time 360;
When i queried the dba_profiles view, I observed that the LIMIT value mentioned as DEFAULT. what is the Default values for every resource name.
TEST_IDLE COMPOSITE_LIMITKERNEL DEFAULT
TEST_IDLE SESSIONS_PER_USERKERNELDEFAULT
TEST_IDLE CPU_PER_SESSIONKERNEL DEFAULT
TEST_IDLE CPU_PER_CALL KERNELDEFAULT
[code]...
For the another profile LIMIT value is mentioned as UNLIMITED.
View 5 Replies
View Related
Jun 18, 2012
Working on building an application which will be in C# (.NET 3.5) and some PL/SQL. However, for the tables which have to store the data, I added a column, comments, to the table and assigned it a data type CLOB. No big deal, except my PL/SQL function is giving me errors if the CLOB built from several varchar2 rows exceeds 4000 characters. I understand the varchar2 field is restricted in 10g, but I was under the impression there wasn't such a restriction on a CLOB (hence the reason we're using it.)
I end up getting:
ORA-6502: PL/SQL: numeric or value error: character string buffer too small
ORA-6512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:The db is 10g (will be upgrading to 11 in October, needs to work on both) on a Windows Server 2003 box (Archive/backup server and db)
View 6 Replies
View Related
May 27, 2009
I am using UTL_TCP package of Oracle to connect to remote server. As per my architecture, I am opening multiple connections TCP in the same Oracle session.
I have come arround the limiation of 16 connections with this package.
View 8 Replies
View Related
Mar 1, 2009
I want to write a procedure in such a way that the code in the proceduce should finish its work with in a given amount of time other wise it should exit from the procedure.
For example....
i am fetching data from a table and inserting into another table and i want ot finish this task within 10 mins if it does not i need to exit from the procedure.
View 1 Replies
View Related
Jun 16, 2010
Consider this query
select deptno, wm_concat(student_name) from id_student group by deptno;
I want to change this query in such a way that only 3 students per dept are listed.
View 11 Replies
View Related
Nov 19, 2012
I want to read a number of rows into an array of records.
I keep getting this error:
ERROR at line 1:
ORA-06532: Subscript outside of limit
here is the procedure:
CURSOR get_all_locations (type IN VARCHAR2) IS
SELECT
*
FROM group
WHERE type = type;
[code]...
View 16 Replies
View Related
Jan 17, 2013
I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies
View Related
Oct 9, 2012
I have a problem with one table.. First of all:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
The problem table resides in a locally managed tablespace. About 10 millions records is added in this table every day. After 36 hours all these records moved to another (partitioned) table, so the size of data in the problem table always about 75 Gb. But the size of table is reached 157 Gb today, and it still growing. The results of dbms_space.space_usage are showed below:
Size of blocks with:
0-25% free space: 4726784
25-50% free space: 17301504
50-75% free space: 24920064
75-100% free space: 102418669568
full blocks: 54761594880Thus, a lot of blocks have 75-100% free space but the table constantly growing: during last 9 days the size increased from 123 to 157 Gb.
how to stop the table growing? It there any way to limit the table size in locally managed tablespace?
View 20 Replies
View Related
May 6, 2011
The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
View 5 Replies
View Related
Jul 29, 2010
In regard to the Oracle 4GB limit, what exactly is this limit? Is this the total size of the datafiles of the user defined tablespaces (excluding SYSTEM and UNDO) so if, for example, we have a 2.8Gb datafile, can we only add another 1.2Gb datafile.
or is it the total size of extents/segments in the user defined tablespace i.e. if we have a 2.8Gb datafile but the database data comes to only 1.7Gb, can we add another tablespace with a new 2.8Gb datafile to allow for another 1.7Gb schema.
View 10 Replies
View Related
Apr 18, 2012
I have a question regarding a selection limit from a list of values. The table where my IDs (unit_codes) come from has another column called 'points'. each ID has its own number of points. Is there a way to put a selection limit on the display field that points will be placed on?
FOR EXAMPLE: a course block has a selection limit of 70 points, A person named someguy selects :
music = 20 points
maths = 20 points
mathsXTRA = 40 points
**ALERT!!! - U can ONLY select 70 points worth of units**
View 3 Replies
View Related
Jun 18, 2012
I need to display the data as range , i have created one table along with inserts as below , i need the desired output as mentioned below.The range will start from field STMH_PM_CODE , it will get the first and last stmh_pm_code and stmh_pm_desc which are in the same combination of STMH_NO,STMH_JOB_NO,STMH_batch_no and stmh_rev_no,
SQL> CREATE TABLE OT_SHOP_TRANSMITTAL_TEST
2 (
3 STMH_NO VARCHAR2(15),
4 STMH_JOB_NO VARCHAR2(12),
5 STMH_BATCH_NO VARCHAR2(12),
6 STMH_PM_CODE VARCHAR2(35),
7 STMH_PM_DESC VARCHAR2(240),
8 STMH_REV_NO VARCHAR2(12)
9 );
Table created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1001','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002',FRAME','0');
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ED
Wrote file afiedt.buf
1* INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002','FRAME','0')
SQL> /
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1003','COLUMN','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0002','1107040-1004','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1005','FRAME','0');
1 row created.
SQL> COMMIT;
Commit complete.
-- i want it as below see the line in stmh_pm_desc the description gets added until there is a change in either stmh_batch_no or stmh_rev_no since we have stmh_pm_code 1107040-1004 with stmh_batch_no 0002 it goes to other line as a seperate new range and first one will stop with 1107-1003 as that is the last combination and new line or range will get added over again.
STMH_NO STMH_BATCH_NOSTMH_PM_CODE STMH_PM_DESCSTMH_REV_NO
PR-1107-000100011107040-1001 - 1107040-1003FRAME, COLUMN0
PR-1107-000100021107040-1004 FRAME 0
PR-1107-000100011107040-1005 FRAME 0
View 39 Replies
View Related
Jul 21, 2011
The procedure uses bulk collect to fetch from a normal cursor, Then I am using for all to insert into target table, The number of rows are 234965470
Question: What should ideally be the limit for my bulk collect ?
According to below, it should be in hundreds[URL]...
I put a bulk collect limit of 50000 - took close to 2 hours then i tried 10000 - just 3 mins shorter than the above time But if you commit every 500 rows, Then is there not another theory that frequent commits is not good ?
Is there something I have to ask the DBAS ?
View 4 Replies
View Related
Sep 4, 2013
Here is my query to fetch only 10 records from order by result set.
select *from (select * from EOE_POC.PRODUCT_TEST_REPORT where PRODUCT_CODE='214d' order by CREATE_DATE desc ) where ROWNUM <= 10
I am having problem binding it with java API . how to query this without using sub query ?
View 11 Replies
View Related
Oct 29, 2013
I would like to know if it's possible to limit access to isqlplus to only one user in my case. I need to give access in read only mode to user in isqlplus.For that, I will create a oracle user with only select privilege on the tables, but I want to be sure that only this user will be used in isqlplus and not another.
View 1 Replies
View Related
Dec 22, 2010
I have a dynamic query that is ending up getting larger than 32k and this query is the base of a ref cursor that would in turn return the results to the application. How can I solve this problem since the largest content of a pl sql variable or literal is 32k ( said by oracle ) ?
PROCEDURE sp_large_query (c1 OUT sys_refcursor)
IS
BEGIN
OPEN c1 FOR ' SELECT STATEMENT WITH MORE THAN 32K ';
END;
View 2 Replies
View Related
Aug 14, 2012
i have a database nd only 10 users must allowed to access db at any point of time and sessions per user must be 1.that is only 10 users must be able to access the database.
View 6 Replies
View Related
Sep 18, 2011
I have to optimize a batch job which returns > 1 lakh records . I have a commit limit being passed . I am planning to divide the cursor records for processing as follows. If the cursor suppose returns 1000 rows and the commit limit passed is 200 , then i want to fetch 200 records first , bulk collect them into associative arrays and then bulk insert into target table.
After this is done, i will fetch the next 200 records from the cursor and repeat the processing. I would like to know how i can divide the cursor records, and fetch "limit" number of records at a time and also be able to go to the next 200 recs of the cursor next time.
View 1 Replies
View Related
