Home » SQL & PL/SQL » SQL & PL/SQL » how to code this in PLSQL(dynamic) (merged 3 cross-posts)
how to code this in PLSQL(dynamic) (merged 3 cross-posts) [message #204774] Wed, 22 November 2006 01:37 Go to next message
talktopankajk
Messages: 4
Registered: November 2006
Junior Member
here is my problem: i have to generate the following query dynamically using PLSQL.value of k is user specified.

Insert into Fk
Select item1, … , itemk, count(*)
From Ck, T t1, … , T tk
Where t1.item = Ck.item1 and
:
tk.item = Ck.itemk and
t1.tid = t2.tid and
:
tk-1.tid = tk.tid
Group by item1, item2, … ,itemk
Having count(*) > minsup


help me its urgent.
how to code this in PLSQL(dynamic) [message #204778 is a reply to message #204774] Wed, 22 November 2006 01:46 Go to previous messageGo to next message
talktopankajk
Messages: 4
Registered: November 2006
Junior Member
hi all,
here is my problem: i have to generate the following query dynamically using PLSQL.value of k is user specified.

Insert into Fk
Select item1, … , itemk, count(*)
From Ck, T t1, … , T tk
Where t1.item = Ck.item1 and
:
tk.item = Ck.itemk and
t1.tid = t2.tid and
:
tk-1.tid = tk.tid
Group by item1, item2, … ,itemk
Having count(*) > minsup


help me its urgent.
how to code this in PLSQL(dynamic) [message #204779 is a reply to message #204774] Wed, 22 November 2006 01:50 Go to previous messageGo to next message
talktopankajk
Messages: 4
Registered: November 2006
Junior Member
hi all,
here is my problem: i have to generate the following query dynamically using PLSQL.value of k is user specified.

Insert into Fk
Select item1, … , itemk, count(*)
From Ck, T t1, … , T tk
Where t1.item = Ck.item1 and
:
tk.item = Ck.itemk and
t1.tid = t2.tid and
:
tk-1.tid = tk.tid
Group by item1, item2, … ,itemk
Having count(*) > minsup


help me its urgent.
Re: how to code this in PLSQL(dynamic) [message #204783 is a reply to message #204774] Wed, 22 November 2006 02:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, so build the column list, table list and where clause list in pl/Sql, join them together and execute the query using NDS.

What problem have you encountered?
Re: how to code this in PLSQL(dynamic) [message #204786 is a reply to message #204778] Wed, 22 November 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not post the same question in multiple forums.
Re: how to code this in PLSQL(dynamic) [message #204787 is a reply to message #204779] Wed, 22 November 2006 02:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not post the same question in multiple forums.

Re: how to code this in PLSQL(dynamic) [message #204858 is a reply to message #204783] Wed, 22 November 2006 07:14 Go to previous messageGo to next message
talktopankajk
Messages: 4
Registered: November 2006
Junior Member
thanx for replying.actually i don't know how to build where list or table list dynamically.please guide me for this wid the help of an example if u can.
thanx.
Re: how to code this in PLSQL(dynamic) (merged 3 cross-posts) [message #205065 is a reply to message #204774] Thu, 23 November 2006 03:50 Go to previous message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE
   sql_stmt    VARCHAR2(100);
   plsql_block VARCHAR2(200);
   my_deptno   NUMBER(2) := 50;
   my_dname    VARCHAR2(15) := 'PERSONNEL';
   my_loc      VARCHAR2(15) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc;

   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788;

   EXECUTE IMMEDIATE 'DELETE FROM dept 
      WHERE deptno = :n' USING my_deptno;

   plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;

   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

   sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE';
   EXECUTE IMMEDIATE sql_stmt;
END;


Previous Topic: delete clause
Next Topic: How can I delay a dbms_job?
Goto Forum:
  


Current Time: Sat Dec 03 18:29:02 CST 2016

Total time taken to generate the page: 0.05885 seconds