Home » SQL & PL/SQL » SQL & PL/SQL » How to capture particular column from select statement inside a package body in oracle pl sql (oracle pl sql)
icon5.gif  How to capture particular column from select statement inside a package body in oracle pl sql [message #646945] Fri, 15 January 2016 03:05 Go to next message
adfire
Messages: 6
Registered: January 2016
Junior Member
I have more than 10 packages in a database package body like this.

package pk_name body
insert into table
(id,
name,
roll_no)
(select
s.id id,
case when s.name in ('ad','gd')
then 'sam'
else 'pam' end name,
s.rid roll_no )
What I need to capture is the column logic from the select clause

for example

I need case statement from column NAME.

case when s.name in ('ad','gd')
then 'sam'
else 'pam' end
I need to do it for all columns.

I can see all my packages in the user_source table.

I thought of using regular expression but am unable to do so.

As you see columns are separated by comma in select clause (I thought of using it as a separator) but there is chance that a comma could come inside a case statement as well. How would I cope with that?

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646948 is a reply to message #646945] Fri, 15 January 2016 03:21 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You could put your code through a code formatter to standardize it, and then use a utility such as grep to extract the case statements. For example, this code formatter
http://www.dpriver.com/pp/sqlformat.htm
makes your statement look like this
SELECT s.id  id,
       CASE
         WHEN s.NAME IN ( 'ad', 'gd' ) THEN 'sam'
         ELSE 'pam'
       END   NAME,
       s.rid roll_no;

so grep -B1-A3 CASE should get them all.

[Updated on: Fri, 15 January 2016 03:22]

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646953 is a reply to message #646945] Fri, 15 January 2016 04:52 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member
you probably can use dbms_sql to do this. Example to loop thru columns of query:

DECLARE
l_cursor PLS_INTEGER;
l_rows PLS_INTEGER;
l_col_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_query VARCHAR2(32767) :='your query';

l_file UTL_FILE.file_type;
BEGIN
l_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_cursor, l_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP
DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
END LOOP;
-- Output the column names.
FOR i IN 1 .. l_col_cnt LOOP
dbms_output.put_line(l_desc_tab(i).col_name);
END LOOP;
END;
/

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646962 is a reply to message #646953] Fri, 15 January 2016 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@JNagtzaam,

Read the links John posted.

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646967 is a reply to message #646948] Fri, 15 January 2016 11:45 Go to previous messageGo to next message
adfire
Messages: 6
Registered: January 2016
Junior Member
Hi Thank a ton for getting back ...Can u pls tel me how do i use it ?...i guess i need grep command is used in shell script but i dnt unix accesss...!!!

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646968 is a reply to message #646967] Fri, 15 January 2016 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What grep? What has this to do with the question? What is the relation with SQL or PL/SQL?

Please read John's links and do NOT use IM/SMS speak.
Write complete words and sentences.
What does mean "i dnt unix accesss...!!!"?

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646995 is a reply to message #646968] Sun, 17 January 2016 04:19 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member

Dear,

You can use like:


   select  *
   from    all_source
   where   type = 'PACKAGE BODY'
   and     text like '%s.name%'


Pls Note: This text field supports upto 4000 Bytes.

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #646996 is a reply to message #646995] Sun, 17 January 2016 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As OP said "I can see all my packages in the user_source table." I bet he knows the query.
In addition, this is not the question.

In the end, is there REALLY someone writing more than 4000 bytes on a line of code?

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647000 is a reply to message #646945] Sun, 17 January 2016 15:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
Your requirements are unclear. If you are just trying to extract case statements from package bodies, then you can use the code below. The code assumes that you may have multiple packages with multiple case statements and that there may be multiple lines per case statement or multiple case statements per line. I have eliminated carriage returns and leading and trailing spaces, but you can leave those in if you like.

If you have something like this:
SCOTT@orcl> COLUMN name FORMAT A30
SCOTT@orcl> COLUMN text FORMAT A45 WORD_WRAPPED
SCOTT@orcl> SELECT name, text
  2  FROM   user_source
  3  WHERE  TYPE = 'PACKAGE BODY'
  4  ORDER  BY name, line
  5  /

NAME                           TEXT
------------------------------ ---------------------------------------------
PK_NAME                        PACKAGE BODY pk_name
PK_NAME                        AS
PK_NAME                        PROCEDURE pr_name
PK_NAME                        IS
PK_NAME                        BEGIN
PK_NAME                        INSERT INTO tb_name (id, name, roll_no)
PK_NAME                        SELECT s.empno,
PK_NAME                        CASE WHEN s.ename IN ('ad', 'gd')
PK_NAME                        THEN 'sam'
PK_NAME                        ELSE 'pam'
PK_NAME                        END name,
PK_NAME                        s.deptno roll_no
PK_NAME                        FROM   emp s;
PK_NAME                        END pr_name;
PK_NAME                        END pk_name;

15 rows selected.


then you can extract the case statement(s) like this:
SCOTT@orcl> DECLARE
  2    v_name  VARCHAR2(30);
  3    v_text  CLOB;
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE ('NAME			     TEXT');
  6    DBMS_OUTPUT.PUT_LINE ('------------------------------ ---------------------------------------------');
  7    FOR r IN
  8  	 (SELECT name,
  9  		 LTRIM (RTRIM (REPLACE (text, CHR(10), ''))) text
 10  	  FROM	 user_source
 11  	  WHERE  type = 'PACKAGE BODY'
 12  	  ORDER  BY name, line)
 13    LOOP
 14  	 IF r.name != v_name OR v_name IS NULL THEN
 15  	   IF v_name IS NOT NULL THEN
 16  	     v_text := ' ' || v_text;
 17  	     WHILE INSTR (UPPER (v_text), ' CASE ') > 0 LOOP
 18  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' CASE ') + 1);
 19  	       DBMS_OUTPUT.PUT_LINE (RPAD (v_name, 31) || SUBSTR (v_text, 1, INSTR (UPPER (v_text), ' END') + 4));
 20  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' END') + 4);
 21  	     END LOOP;
 22  	   END IF;
 23  	   v_name := r.name;
 24  	   v_text := r.text;
 25  	 ELSE
 26  	   v_text := v_text || ' ' || r.text;
 27  	 END IF;
 28    END LOOP;
 29    WHILE INSTR (UPPER (v_text), ' CASE ') > 0 LOOP
 30  	 v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' CASE ') + 1);
 31  	 DBMS_OUTPUT.PUT_LINE (RPAD (v_name, 31) || SUBSTR (v_text, 1, INSTR (UPPER (v_text), ' END') + 4));
 32  	 v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' END') + 4);
 33    END LOOP;
 34  END;
 35  /
NAME                           TEXT
------------------------------ ---------------------------------------------
PK_NAME                        CASE WHEN s.ename IN ('ad', 'gd') THEN 'sam' ELSE 'pam' END

PL/SQL procedure successfully completed.


Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647190 is a reply to message #647000] Thu, 21 January 2016 10:37 Go to previous messageGo to next message
adfire
Messages: 6
Registered: January 2016
Junior Member
@Barbara Boehmer You are the best thank you mate for your suggestion i used your cursor and i am able to capture Case statement from package body.Yes you are correct i have multiple case statements .
But i need to capture other columns as well for example s.id id from below package body similar to that i need to capture every column from select statement in package body like s.rid roll_no.Please i need full line like field s.id and id both .

package pk_name body
insert into table
(id,
name,
roll_no)
(select
s.id id,
case when s.name in ('ad','gd')
then 'sam'
else 'pam' end NAME,
s.rid roll_no )


Many thanks again...!!!

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647192 is a reply to message #647190] Thu, 21 January 2016 15:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
The following extracts the full column descriptions separated by commas, allowing for commas within case statements. There will be other exceptions where commas within the column descriptions will cause problems, such as decode statements. If you have such things, then you will need to modify the code in the example below, using something to identify the start and end of the section that may contain commas, in the same manner that I used case and end or perhaps counting parentheses or some such thing. You should be able to analyze and understand the code below and make any further modifications yourself.

SCOTT@orcl> COLUMN name FORMAT A30
SCOTT@orcl> COLUMN text FORMAT A45 WORD_WRAPPED
SCOTT@orcl> SELECT name, text
  2  FROM   user_source
  3  WHERE  TYPE = 'PACKAGE BODY'
  4  ORDER  BY name, line
  5  /

NAME                           TEXT
------------------------------ ---------------------------------------------
PK_NAME                        PACKAGE BODY pk_name
PK_NAME                        AS
PK_NAME                        PROCEDURE pr_name
PK_NAME                        IS
PK_NAME                        BEGIN
PK_NAME                        INSERT INTO tb_name (id, name, roll_no)
PK_NAME                        SELECT s.id id,
PK_NAME                        CASE WHEN s.name IN ('ad', 'gd')
PK_NAME                        THEN 'sam'
PK_NAME                        ELSE 'pam'
PK_NAME                        END name,
PK_NAME                        s.rid roll_no
PK_NAME                        FROM    test_tab s
PK_NAME                        END pr_name;
PK_NAME                        END pk_name;

15 rows selected.

SCOTT@orcl> DECLARE
  2    v_name	 VARCHAR2(30);
  3    v_text	 CLOB;
  4    v_cols	 CLOB;
  5    v_col	 VARCHAR2(4000);
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE ('NAME			     TEXT');
  8    DBMS_OUTPUT.PUT_LINE ('------------------------------ ---------------------------------------------');
  9    FOR r IN
 10  	 (SELECT name, LTRIM (RTRIM (REPLACE (text, CHR(10), ''))) text
 11  	  FROM	 user_source
 12  	  WHERE  type = 'PACKAGE BODY'
 13  	  ORDER  BY name, line)
 14    LOOP
 15  	 IF r.name != v_name OR v_name IS NULL THEN
 16  	   IF v_name IS NOT NULL THEN
 17  	     v_text := ' ' || v_text || ' ';
 18  	     --
 19  	     WHILE INSTR (UPPER (v_text), ' SELECT ') > 0 AND INSTR (UPPER (v_text), ' FROM ') > 0 LOOP
 20  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' SELECT ') + 7);
 21  	       v_cols := SUBSTR (v_text, 1, INSTR (UPPER (v_text), ' FROM ') - 1) || ',';
 22  	       WHILE INSTR (v_cols, ',') > 0 LOOP
 23  		 IF INSTR (UPPER (v_cols), ' CASE ') < INSTR (v_cols, ',') AND
 24  		    INSTR (UPPER (v_cols), ' END') > 0 THEN
 25  		   v_cols := SUBSTR (v_cols, INSTR (UPPER (v_cols), ' CASE ') + 1);
 26  		   v_col := SUBSTR (v_cols, 1, INSTR (UPPER (v_cols), ' END') + 3);
 27  		   v_cols := SUBSTR (v_cols, INSTR (UPPER (v_cols), ' END') + 4);
 28  		   v_col := v_col || SUBSTR (v_cols, 1, INSTR (v_cols, ',') - 1);
 29  		 ELSE
 30  		   v_col := SUBSTR (v_cols, 1, INSTR (v_cols, ',') - 1);
 31  		 END IF;
 32  		 DBMS_OUTPUT.PUT_LINE (RPAD (v_name, 31) || LTRIM (v_col));
 33  		 v_cols := SUBSTR (v_cols, INSTR (v_cols, ',') + 1);
 34  	       END LOOP;
 35  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' FROM ') + 5);
 36  	     END LOOP;
 37  	     --
 38  	   END IF;
 39  	   v_name := r.name;
 40  	   v_text := r.text;
 41  	 ELSE
 42  	   v_text := v_text || ' ' || r.text;
 43  	 END IF;
 44    END LOOP;
 45  	     --
 46  	     WHILE INSTR (UPPER (v_text), ' SELECT ') > 0 AND INSTR (UPPER (v_text), ' FROM ') > 0 LOOP
 47  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' SELECT ') + 7);
 48  	       v_cols := SUBSTR (v_text, 1, INSTR (UPPER (v_text), ' FROM ') - 1) || ',';
 49  	       WHILE INSTR (v_cols, ',') > 0 LOOP
 50  		 IF INSTR (UPPER (v_cols), ' CASE ') < INSTR (v_cols, ',') AND
 51  		    INSTR (UPPER (v_cols), ' END') > 0 THEN
 52  		   v_cols := SUBSTR (v_cols, INSTR (UPPER (v_cols), ' CASE ') + 1);
 53  		   v_col := SUBSTR (v_cols, 1, INSTR (UPPER (v_cols), ' END') + 3);
 54  		   v_cols := SUBSTR (v_cols, INSTR (UPPER (v_cols), ' END') + 4);
 55  		   v_col := v_col || SUBSTR (v_cols, 1, INSTR (v_cols, ',') - 1);
 56  		 ELSE
 57  		   v_col := SUBSTR (v_cols, 1, INSTR (v_cols, ',') - 1);
 58  		 END IF;
 59  		 DBMS_OUTPUT.PUT_LINE (RPAD (v_name, 31) || LTRIM (v_col));
 60  		 v_cols := SUBSTR (v_cols, INSTR (v_cols, ',') + 1);
 61  	       END LOOP;
 62  	       v_text := SUBSTR (v_text, INSTR (UPPER (v_text), ' FROM ') + 5);
 63  	     END LOOP;
 64  	     --
 65  END;
 66  /
NAME                           TEXT
------------------------------ ---------------------------------------------
PK_NAME                        s.id id
PK_NAME                        CASE WHEN s.name IN ('ad', 'gd') THEN 'sam' ELSE 'pam' END name
PK_NAME                        s.rid roll_no

PL/SQL procedure successfully completed.



Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647429 is a reply to message #647192] Wed, 27 January 2016 11:22 Go to previous messageGo to next message
adfire
Messages: 6
Registered: January 2016
Junior Member
Thanks Barbara.I have executed code its giving desired output .But unfortunately i have got new problem now for couple of packages below code is working fine .But in rest of packages package structure is totally different.They contain multiple insert statements inside procedures.Even they contain sub query's .

For example below.

package pk_name body

procedure update_b
insert into table
(id,
name,
roll_no)
(select
s.id id,
case when s.name in ('ad','gd')
then 'sam'
else 'pam' end name,
s.rid roll_no )
end;

procedure update_b
insert into table
(id,
name,
roll_no,
charge)
(select
s.id id,
case when s.name in ('ad','gd')
then 'sam'
else 'pam' end name,
s.rid roll_no
cust.charge charge ) FROM test s,SELECT customer.customer_num,
(SELECT SUM(ship_charge) as charge
FROM orders
WHERE customer in '09') cust
end;
Similarly Procedure update_c
--------
end;

end package;




Can you please explain your code which you have written.Requirement remains the same i need to capture data before FROM clause of select statement for all the procedures .I have been trying to write code but no luck so far. Sad



Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647430 is a reply to message #647429] Wed, 27 January 2016 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Once again, Please read How to use [code] tags and make your code easier to read.

Compare your post and Barbara's ones, which ones are more readable and understandable?

Report message to a moderator

Re: How to capture particular column from select statement inside a package body in oracle pl sql [message #647442 is a reply to message #647429] Wed, 27 January 2016 16:16 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
The last code that I provided loops through a cursor of your user_source for all of your package bodies and concatenates one package body at a time into the variable v_text. It then parses out the section between the first SELECT and the first FROM and stores that in the variable v_cols and removes everything before the first FROM including FROM from v_text. It then parses out the column descriptions from v_cols one at a time, storing them to v_col and outputting them. This processes is repeated until all of the column descriptions have been processed from v_cols, and every package has been processed. The parsing is done using SUBSTR. INSTR is used to check whether a comma or CASE or END is next, in order to separate out the CASE statements.

Your initial problem seemed to suggest a limited number of packages in similar format. It sounds like you have much more and will encounter many things such as your sub-queries. Designing a full SQL parser is a major project and you may be better off searching for some such existing thing on the internet.

Your whole requirement is unusual. If you will tell us why you think you need to get the column descriptions from your package bodies and explain the purpose, and what you plan to do with this information, perhaps we can come up with a better solution for the whole problem.

Report message to a moderator

Previous Topic: Determine Missing Route_Seq Numbers
Next Topic: How can I pass UserId/Password securely as a parameter using UTL_HTTP
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 18:29:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.