Home » SQL & PL/SQL » SQL & PL/SQL » how can make use of p_tier_id and still compiled?
how can make use of p_tier_id and still compiled? [message #229195] Thu, 05 April 2007 19:10 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
Hi,

I've a hard time making use of P_tier_id that will be assigned a value in the declare block.

I am trying to do it in a plain sql command that is then fetched by a ref cursor (cur_1)...

What I'm trying to achieve here is very simple.
SELECT all the org info from ALL_ORGNAMES_FROM_ORGS_TABLE that matches the corresponding org_name in the respective table "tier2_orgs" (if p_tier_id = 2)

Then insert all the records into the table competitor_orgs_all using bulk collect and forall (didn't intend to complicate the matter here. But I just wanted to try out bulk collect and forall).

Please let me know if you've any questions.

DECLARE

  v_emprecs  emp_util.emprec_tab_t; 
  --cur_1        SYS_REFCURSOR;
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);

  sqlCommand sqlType;
  cur_1 curtype;
  p_tier_id NUMBER := 2;
  

  
  max_rows       CONSTANT NUMBER := 100; 

--  CURSOR cur_1 (p_tier_id NUMBER) IS 
--            SELECT *
--                FROM ALL_ORGNAMES_FROM_ORGS_TABLE a
--				WHERE exists (SELECT 'x' FROM 'tier'||p_tier_id||'_orgs' b
--					  		  WHERE a.org_name like b.org_name
--							 ) 
 

							 
   sqlCommand := 'SELECT * FROM ALL_ORGNAMES_FROM_ORGS_TABLE a WHERE exists (SELECT ''x'' FROM ''tier'''||
p_tier_id
||'''_orgs'' b WHERE a.org_name like b.org_name );';							 							 
							 							 		
BEGIN
  OPEN cur_1 FOR SQLCommand;
  LOOP  
    EXIT WHEN cur_1%NOTFOUND;
    FETCH cur_1 BULK COLLECT INTO v_emprecs LIMIT max_rows;
    FORALL i IN 1..org.COUNT
      INSERT INTO competitor_orgs_all (market_share_tier, org_id, org_name, )
         VALUES(p_tier_id, org.org_id(i), org.org_name(i) );
   END LOOP;
 CLOSE cur_1;
END;


Remarks: fonts in bold is where I am having the errors.

[Updated on: Thu, 05 April 2007 19:24]

Report message to a moderator

Re: how can make use of p_tier_id and still compiled? [message #229197 is a reply to message #229195] Thu, 05 April 2007 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It appears to this observer that you have a data type/mismatch issue.
Re: how can make use of p_tier_id and still compiled? [message #229354 is a reply to message #229195] Fri, 06 April 2007 22:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Did you try to print the content of sqlCommand and run it?
If so, you could debug it easily. I see there two potential problems:
- concatenate string with number (the bold one) without explicit conversion (TO_CHAR)
- covering the table name in single quotes; either remove them or replace with double quotes, if the table name is 'special' as described in documentation

However, the rest of your code seems like you never ran this block. You fetch into v_emprecs, but use undeclared variable org later. You have also extra comma in the column list of the insert statement.
Re: how can make use of p_tier_id and still compiled? [message #232894 is a reply to message #229354] Mon, 23 April 2007 19:19 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Flyboy,

Eventually, I have a chance to get back to this project. I might need some guidance here. I am not sure if I know how to proceed in the right direction.

So this is what I have and what I wanted to achieve.


create table 		competitor_tier2_orgs nologging as
select distinct		org_id, org_name
from   				ALL_ORGNAMES_FROM_ORGS_TABLE
where				[B]org_name like 'ACTUATE%'[/B]
or					org_name like 'ARIBA%'
or 					org_name like '%BMC SOFTWARE%'
or 					org_name like 'BORLAND%' 
or 					org_name like 'BRIO%'
or					org_name like 'COMMERCE ONE%'
-- or					(org_name like 'COMPUTER SCIENCES CORP%' or org_name like 'CSC COMPUTER SCIENCES %'
-- 					or org_name in ('COMPUTER SCIENCES','CSC')) -- CONSULTING 
or					org_name like 'I2%'
or					org_name like 'INFORMATICA%'
or					(org_name like 'INFORMATION BUILDERS%' or org_name like 'IBI%')
or					org_name like 'JBOSS%'
or					org_name like 'MANUGISTICS%'
or					org_name like '%MICROSTRATEGY%'
or					(org_name like 'NATIONAL CASH REGISTER%' or org_name like '%NCR%' 
					or org_name like 'TERADATA%')
or					org_name like 'PLUMTREE SOFTWARE%'
or					org_name like 'QUEST%'
--					or org_name like '%QUEST - JD EDWARDS USERS GROUP%')
or					([B]org_name like 'QWEST %' 
					and org_name not like 'QWEST%DIAGNOSTIC%'
					and org_name not like 'QWEST%DENTAL%'
					and	org_name not like 'QWEST%FOODS%'
					and	org_name not like 'QWEST%AIR%PART%'
					and	org_name not like 'QWEST%BRIAN%ALBANO'[/B])
					/* QWEST COMMUNICATIONS - not direct competitor or in Oracle's space */
or					org_name like 'RATIONAL%' 
-- or					(org_name like 'SIEMENS BUSINESS SERVICE%') -- CONSULTING; PRIMARY INDUSTRY IS TELECOMMUNICATIONS --
or					org_name like 'SILVERSTREAM%'
or					org_name like 'SUN MICRO%SYSTEM%'
or					(org_name like '%SYBASE%' )
order by			org_name asc
;

alter table			competitor_tier2_orgs
add	  				(competitor_name		  varchar2(500),
					mkt_share_tier		   		varchar2(3))
;


update				competitor_tier2_orgs
set					competitor_name = 'ACTUATE'
where				org_name like 'ACTUATE%'
;



and so I created a table something like the following

Lookup table:
p_tier_id competitor_name org_like org_not_like
2         ACTUATE         ACTUATE% 
2 QWEST COMMUNICATIONS QWEST %  QWEST%DIAGNOSTIC%|QWEST%DENTAL%|QWEST%FOODS%|QWEST%AIR%PART%|QWEST%BRIAN%ALBANO



and the following is the PL/SQL anonymous block I have written so far [still in the development stage] to try to make use of the lookup table I just created above.

DECLARE

v_emprecs emp_util.emprec_tab_t;
--cur_1 SYS_REFCURSOR;
type curtype is ref cursor;
subtype sqltype is varchar2(1000);

sqlCommand sqlType;
sqlCommand2 sqlType;
cur_1 curtype;
p_tier_id NUMBER := 2;


code_text varchar2(2000):=

'begin split_vc2; end;'


max_rows CONSTANT NUMBER := 100;

sqlCommand2 := 'split_vc2';

sqlCommand := 'SELECT * FROM ALL_ORGNAMES_FROM_ORGS_TABLE WHERE' || sqlCommand2;

BEGIN

OPEN cur_1 FOR SQLCommand;
LOOP
EXIT WHEN cur_1%NOTFOUND;
FETCH cur_1 BULK COLLECT INTO v_emprecs LIMIT max_rows;
FORALL i IN 1..v_emprecs .COUNT
INSERT INTO competitor_orgs_all (market_share_tier, org_id, org_name, )
VALUES(p_tier_id, v_emprecs.org_id(i), v_emprecs.org_name(i) );
END LOOP;
CLOSE cur_1;
END;

What would be an quick-and-easy way to have sqlCommand2 call a function to generate "where org_name like/ not like str1..n"? (if it's possible)?
Or any other means to have a procedure called an independent str tokenizer and do what needs to be done.


[Updated on: Tue, 24 April 2007 13:34]

Report message to a moderator

Re: how can make use of p_tier_id and still compiled? [message #233125 is a reply to message #229195] Tue, 24 April 2007 16:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
dpong,

suppose, you want to fill sqlCommand2 with the WHERE condition.
as I do not want to rid you of the pleasure when debugging this generic solution, I just the framework, which looks like
sqlCommand2 := '';
FOR l_lookup in (SELECT competitor_name, org_like, org_not_like
                   FROM <lookup_table_name>
                   WHERE p_tier_id = p_tier_id -- oops column and variable names equal, you should change one of them)
                ) LOOP
   -- fill sqlCommand2 from l_lookup.org_like and l_lookup.org_not_like
   -- maybe it would help to use REPLACE( l_lookup.org_not_like, '|', ''' and org_name not like ''' )
END LOOP;
sqlCommand2 := substr(sqlCommand2, 1, length(sqlCommand2)-<appropriate_length>);
dbms_output.put_line(sqlCommand2);  -- just show the content

However, if you would like to extract the values divided by vertical bars, use in_list function described in this thread on AskTom.

Re: how can make use of p_tier_id and still compiled? [message #233129 is a reply to message #233125] Tue, 24 April 2007 18:03 Go to previous message
dpong
Messages: 73
Registered: January 2007
Member
Flyboy,

I have not had a chance yet to digest your post. But this is what I have so far. Seems like it's complete. As I'm trying to learn all these syntax on the fly, I might not have a fully correct way of using them.

CREATE OR REPLACE PROCEDURE COMPETITOR_ORGS_LOOKUP IS
  
  org tier_orgs%ROWTYPE;
  
  TYPE nest_tab_t IS TABLE OF NUMBER;
  TYPE nest_tab_t2 IS TABLE OF VARCHAR;
  nt   nest_tab_t2 := nest_tab_t2(); 
  nt_comp nest_tab_t2 := nest_tab_t2();
  nt_tier_id  nest_tab_t := nest_tab_t();
  nt2  nest_tab_t2 := nest_tab_t2();
  nt2_comp nest_tab_t2 := nest_tab_t2();
  nt2_tier_id  nest_tab_t := nest_tab_t();
  ret  VARCHAR2(100) := NULL;
   
  
  TYPE orgnames_t IS TABLE OF ALL_ORGNAMES_FROM_ORGS_TABLE%ROWTYPE; 
  v_orgrecs    orgnames_t;
  cur_1        SYS_REFCURSOR;
  type curtype is ref cursor;
  subtype sqltype is varchar2(1000);

  sqlCommand sqlType;
  sqlCommand2 sqlType;

  
  objcur curtype;
  cur1 	curtype;
  cur2  curtype;

  oname VARCHAR2(100);
  --oname2 VARCHAR2(200); 

  p_tier_id NUMBER := 2;


-- code_text varchar2(2000):=      'begin split_vc2; end;'

  
  max_rows       CONSTANT NUMBER := 100; 


							 							 		
BEGIN

  sqlCommand2 := 'SELECT * FROM tier_orgs';							 
						 							  
   sqlCommand := 'SELECT func_sys_refcursor( cursor ( select :cur_1, :cur_2  from dual ) ) INTO cur_1 from dual' ;

	OPEN cur2 FOR sqlCommand2;
	FETCH cur2 INTO ORG;
	
	  FOR i IN 1..100 LOOP 
	    nt.EXTEND; 

		IF i = 1 THEN 		SELECT 'org_name like' || parse(org.org_like, i), org.competitor_name, org.p_tier_id
		   		 			into nt(i), nt_comp(i), nt_tier_id (i) FROM DUAL; 
		ELSE   	 			SELECT 'or org_name like' || parse(org.org_like, i), org.competitor_name, org.p_tier_id
							into nt(i), nt_comp(i), nt_tier_id (i) FROM DUAL;
		END IF;
	  END LOOP; 

  	  FOR j IN 1..100 LOOP 
	    nt2.EXTEND; 
					  	   SELECT 'and org_name not like' || parse(org.org_like, j), org.competitor_name, org.p_tier_id
						   into nt2(j), nt2_comp(j), nt2_tier_id (j) FROM DUAL; 
	  END LOOP;  
	  
	  
   CLOSE cur2;
   
   
   IF nt.count != 0 THEN
   
	   For i IN 1..nt.count LOOP
	   	   
		   	  ret := ret || nt(i);
			  
	   END LOOP;
	   	   
		   IF (nt2 IS NULL) THEN 
		   	  ret := 'WHERE ( ' || ret || ' )';
		   ELSE 
		   
		   		For j IN 1..nt2.count LOOP
					  
					ret := ret || nt2(j);
				END LOOP;
		
			ret := 'WHERE ( ' || ret || ' )';   
		   END IF;
   
   END IF;

   	 
  OPEN objcur FOR SQLCommand;	 
  
  
 LOOP  
    EXIT WHEN objcur%NOTFOUND;

  FETCH objcur INTO oname;

  
   execute immediate oname USING 'SELECT org_id, org_name FROM ALL_ORGNAMES_FROM_ORGS_TABLE ', ret;
   
  CLOSE objcur;
  
    FETCH cur_1 BULK COLLECT INTO v_orgrecs LIMIT max_rows;
    FORALL i IN 1..v_orgrecs.COUNT
      INSERT INTO competitor_orgs_all(ORG_ID,ORG_NAME,COMPETITOR_NAME,MKT_SHARE_TIER	) --(market_share_tier, org_id, org_name)
         VALUES ( v_orgrecs(i), org.competitor_name, org.p_tier_id)  ;--(p_tier_id, v_orgrecs.org_id, v_orgrecs.org_name(i) );
   END LOOP;
  CLOSE cur_1; 
   
  
END;

/*This is an additional function I wrote to accomplish what I wanted above.*/

create or replace function func_sys_refcursor (
  rc in sys_refcursor
) return VARCHAR as

  v_a VARCHAR2(100);
  v_b varchar2(100);

  v_ret VARCHAR2(200) := NULL;

begin

  loop
    fetch rc into v_a, v_b;
    exit when rc%notfound;
	
	  v_ret := v_a ||' ' || v_b;
  end loop;

  return v_ret;
end;
/


/* ..and one more parse function to parse my strings in the nested tables I have created.*/

CREATE OR REPLACE FUNCTION parse (par_str IN CHAR, par_n IN NUMBER) 
  RETURN CHAR 
IS
  SUBSTR VARCHAR2(100);
BEGIN
  SELECT SUBSTR(par_str, 
                INSTR(par_str, '|', 1, par_n - 1) + 1,
                INSTR(par_str, '|', 1, par_n) - INSTR(par_str, '|', 1, par_n - 1) - 1
               )
  INTO SUBSTR
  FROM dual;
                    
  RETURN trim(SUBSTR);
END;
/





I literally just finished writing all these Pl/SQL right now. Still have to go thru' all the errors before it compiles. Let me digest your post later this evening. Thanks!!

p.s. If you do see an obvious logical error/syntax error, please don't hesitate to point it out blatantly!
p.p.s. I'm not sure if I should give up on this project. It turned out I overlook the concept of bringing in competitor_name, and p_tier_id into the queries. My queries is right now so fixed and rigid that I am not sure if there's a way for me to add more to it. Let me sleep on this tonight and see if I could come up with a better way of doing this.




[Updated on: Tue, 24 April 2007 20:55]

Report message to a moderator

Previous Topic: Return code from a SQL script
Next Topic: username, password, host sting
Goto Forum:
  


Current Time: Sun Dec 04 02:22:56 CST 2016

Total time taken to generate the page: 0.18840 seconds