PL/SQL :: Counting The Number Of Lines For Each Procedure In A Package
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
ADVERTISEMENT
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
Aug 2, 2007
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
View 2 Replies
View Related
Apr 4, 2007
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
View 3 Replies
View Related
Mar 31, 2011
is it possible that two different package can contain same procedure?
View 1 Replies
View Related
Jul 17, 2012
In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?
View 14 Replies
View Related
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
View Related
Dec 10, 2007
in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:
Hops_Haku.veto(opnum);
But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?
Until now I've only gotten error, even after declaring the variable in the declaration part.
View 6 Replies
View Related
May 24, 2011
I have a package which has two procedures in it.
The second of which was put in just as a test:
PROCEDURE DST_RPT_INVOICE_REPRINT(refCur OUT Dsti_Rpt_Init_Pkg.RC, param_locationid VARCHAR2,
param_companycode VARCHAR2, param_frominvdate DATE, param_toinvdate DATE, param_project VARCHAR2,
param_invtype VARCHAR2, param_printed NUMBER) AS.....
Dsti_Rpt_Init_Pkg.PRINT_OUTPUT(strSql);
END DST_RPT_INVOICE_REPRINT
All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?
View 3 Replies
View Related
Sep 13, 2010
I am wrinting a procedure. I want to call a procedure in a different package and get its out value to a variable.
how can I do that in PL SQL?
View 10 Replies
View Related
Mar 2, 2010
There is any way to call private procedure out side of the package.
View 5 Replies
View Related
Jun 27, 2012
Recently I created a trigger in my production environment which affected a procedures execution.
Trigger code is as below
CREATE OR replace TRIGGER chk_fresh_lead_time
BEFORE INSERT ON location_refnum
REFERENCING NEW AS NEW
FOR EACH ROW
WHEN (NEW.location_refnum_qual_gid = 'FRESH_LEAD_TIME'
[code].......
This trigger will check if a value is inserted on Location ref num table for the qualifier LEAD_TIME only if the value exist in Fresh template table else it will throw an error message as Quote: Add the fresh template and then add the LEAD_TIME value
We have a procedure in one of the package which inserts values on the same Location ref num table but for a different qualifier say PENDING and not LEAD_TIME as above, but still the procedure is not being executed due to this trigger. How this trigger is affecting the procedures execution.
View 10 Replies
View Related
Jan 7, 2013
difference between oracle procedure & package.
View 1 Replies
View Related
Mar 9, 2010
I need to schedule a stored procedure and a stored package every day @6:30 AM.
I have also a merge statement that I need to execute before stored procedure is executed.
View 3 Replies
View Related
Sep 16, 2012
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
View 6 Replies
View Related
Jan 11, 2013
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS
PROCEDURE proc_1 IS
BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_2*
I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.
View 7 Replies
View Related
Jun 9, 2012
Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name
View 2 Replies
View Related
Nov 14, 2011
sample code to read attachments in inbox(from POP3 mail server) using plsql package or procedure.
View 6 Replies
View Related
Jun 8, 2011
I need to know how to capture the Parameter Value of a Procedure in a Variable and reference that variable in another Procedure or Package.
View 12 Replies
View Related
Dec 13, 2012
How do I trace an particular object (procedure/package) on the database. my database version is Oracle 11g(11.2.0.2)
View 2 Replies
View Related
Jan 5, 2007
how to extract the ddl of a table/package/procedure using SQL Code?
I found a method, but it's only supported from Oracle9i and obove, im using Oracle8i
View 3 Replies
View Related
Apr 30, 2013
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies
View Related
Oct 4, 2012
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
View 1 Replies
View Related
Mar 16, 2012
i need to clarify that, we can call a procedure inside a procedure, when i am using inside a package,whether i have specify the called procedure in the Package specification?
View 5 Replies
View Related
Oct 23, 2012
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
View 4 Replies
View Related
Oct 8, 2012
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
noted that the data does exist 32k.
View 39 Replies
View Related
Feb 19, 2011
I have a table "exam results"
create table eresults
(student_name varchar2(20),
section_name varchar2(4),
exam_id NUMBER (4))
marks NUMBER (3))
[code]....
My requirement is that I need another column named "top scored" which will show how many times each student took highest marks for his Section in each exam_id"
For example in above data the following students "Top scored" for thier respective section in each exam_id:
STUDENT_NAME SECTION EXAM_ID
DOLLY A 1
RIZWAN B 1
PAUL C 2
ZAKIR D 2
[code]....
So, based on above my requirement is as below
STUDENT_N SECTION_NAME COUNT(EXAM_ID) SUM(MARKS) MAX(MARKS) top_scored
ALEENA C 2 147 91 0
ASIM D 2 68 45 1
ASLAM B 2 70 56 0
ATIF D 2 2 2 0
AYSHA B 2 114 78 0
[code]....
View 1 Replies
View Related
Jul 4, 2013
the following conundrum.
Having the Following information:
Col A Col B
---------------
134 | 1049
0 | 1050
12 | 1051
0 | 1052
0 | 1053
0 | 1054
0 | 1055
0 | 1056
0 | 1057
What I want is to count the number of the similar occurrences on Col A starting from the bottom and stopping at the first that is different.
Taking the example above I would get 6, that is the number of repeated "0" from the last value "1057" until "1052".
View 15 Replies
View Related
Jun 30, 2008
I have a q, If i have:
table1(p_id, birth_date,lid)
table2(lid)
table3(s_id, birth_date, lid)
Now i would like to count from table 1 all p_id with birthday is the same as a s_id.Currently i am doing this:
select s_id, count(tbl1.pid)
inner join table 3
using (lid)
inner join table 1
using (lid)
Where to_char(tbl1.birth_date,'DD') = to_char(tbl2.birth_date,'DD') AND
to_char(tbl1.birth_date,'MM') = to_char(tbl2.birth_date,'MM')
when i check it using data, i get a target_id whose birth_day is same as TWO people in tbl1 yet according to that query i am only getting ONE person! rest of the results from that query look fine!
View 6 Replies
View Related
Nov 27, 2011
I have a table t
create table T
(player varchar2(40),
for_team varchar2(10),
Vs_team varchar2(10),
matchid number,
[code]...
Note: Each player can appear for more than one team in different matches. For exmaple, John appeared for Team A in matchid = 1 and then in matchid = 2 he appeared for team B. So same could be for other players
Requirment: I want for each player, the sum of total number of matches and points (which is easy using SUM) but along with total number of different teammates he played with in all the matches he appeared [for his team(s)] and also the total number opposition players and lastly the total number of different players he played with in all the matches he appeared in.
Just to clarify some terms, incase any doubt:
Here teammates = all players who appeared (for_team) in a match from the same team for whom the respective player also appeared in the same match.
opposition players = all players who appeared for VS_team played by each player.
total different players = all unique players who appeared for for_team or vs_team in the matches in which each player appeared.
Here is my desired outout:
Player total Matches Sum(points) Different teammates Different opposition players total different players
player played with player played with player played with
John 3 4 5 5 10
Fulton 2 11 3 4 6
Sarah 1 9 2 3 5
[code]..
I want one simple query and shortest query to achieve about output since in my actual table data is huge.
View 4 Replies
View Related