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]....
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.
•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.
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.
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?
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.
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 .
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:
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.
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 ???
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?
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.
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?
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
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!
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.
