Home » SQL & PL/SQL » SQL & PL/SQL » How To Create Dynamic Query
How To Create Dynamic Query [message #295325] Tue, 22 January 2008 02:22 Go to next message
skkazmi
Messages: 44
Registered: April 2006
Member
Hi All,

I Create a Pl/Sql Routine which extrate the query. But how can i execute that query data.

DECLARE
V_SQL VARCHAR2(4000) := 'SELECT ';
BEGIN
FOR R IN (SELECT A.SNO,B.COL_NAME FROM PRO_TRANS_CHAR A,PRO_STP_ROU_CHAR B
WHERE A.CODE=4
AND A.CHAR_ID=B.CHAR_ID
AND A.ROU_CODE=B.ROU_CODE
AND Nvl(B.USE_IN,'N') IN ('I','B')
AND Nvl(B.INACTIVE,'N')='N'
ORDER BY A.SNO) LOOP
V_SQL := V_SQL||R.COL_NAME||',';
END LOOP;
V_SQL := SubStr(V_SQL,1,Length(V_SQL)-1);
V_SQL := V_SQL||' FROM PRO_TRANS_DTL WHERE TRANS_ID=10';
Dbms_Output.PUT_LINE(V_SQL);
END;

Result:
-------
Select Col1,Col2,Col3 From Pro_Trans_Dtl Where Trans_Id=10

I Want To Execute That Query which are store in v_sql variable.

Regards,
Khurram
Re: How To Create Dynamic Query [message #295326 is a reply to message #295325] Tue, 22 January 2008 02:30 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you have to dynamically create a view?
Do you dynamically generates your program while executing it?

Search "execute immediate".

Regards
Michel
Previous Topic: Hierarchical query exclusions
Next Topic: differentiate item units (merged)
Goto Forum:
  


Current Time: Sat Dec 10 04:56:21 CST 2016

Total time taken to generate the page: 0.10609 seconds