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

SQL & PL/SQL :: Limit On Number Of Lines Of Code Ijn Block

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

Count Number Of Lines In Multi Line String

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

Split Procedure Parameters Over Multiple Lines

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

SQL & PL/SQL :: Two Different Package Can Contain Same Procedure?

Mar 31, 2011

is it possible that two different package can contain same procedure?

View 1 Replies View Related

SQL & PL/SQL :: Package And Procedure?

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

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 View Related

Call A Procedure From Another Package?

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

SQL & PL/SQL :: Running Procedure Within A Package?

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

SQL & PL/SQL :: Call A Procedure In A Different Package?

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

SQL & PL/SQL :: Call Private Procedure Outside Of The Package?

Mar 2, 2010

There is any way to call private procedure out side of the package.

View 5 Replies View Related

SQL & PL/SQL :: Trigger Impacting A Procedure In The Package

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

SQL & PL/SQL :: Difference Between Oracle Procedure And Package?

Jan 7, 2013

difference between oracle procedure & package.

View 1 Replies View Related

SQL & PL/SQL :: Scheduling A Stored Procedure / Package?

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

PL/SQL :: How To Test Procedure / Function / Package

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

PL/SQL :: Get A Called Procedure / Function Name Within Package?

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

Export / Import Of Procedure - Function / Package

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

SQL & PL/SQL :: Mail Reading / Saving Through Package Or Procedure

Nov 14, 2011

sample code to read attachments in inbox(from POP3 mail server) using plsql package or procedure.

View 6 Replies View Related

SQL & PL/SQL :: Capture And Reference Parameter Value Of Procedure In Another Package

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

Trace Particular Object (procedure / Package) On Database?

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

How To Extract DDL Of Table / Package / Procedure Using SQL Code

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

PL/SQL :: Call VB Function From A Package Stored Procedure

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

How To Audit Execute On Specified Procedure Or Function In Package

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

SQL & PL/SQL :: Procedure Within Procedure In Package

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

Application Express :: Creating Procedure Using Database Package

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

SQL & PL/SQL :: Export Table Data Into Text File Through Procedure / Package

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

SQL & PL/SQL :: Counting Occurrences Of Max Value?

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

SQL & PL/SQL :: Counting Followed Last Occurrences

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

Counting People With Same Birthdays

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

SQL & PL/SQL :: Counting Partners Who Appeared Together With Each Other?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved