PL/SQL- Dynamic SQL And Bind Variable

From: baka <mesundara_at_hotmail.com>
Date: 23 Jun 2005 03:58:35 -0700
Message-ID: <1119524315.464081.215000@o13g2000cwo.googlegroups.com>

Hello Oracle Guru's

Once again i might in need your help.

I have written a PL/SQL to Dynamically
pass Parameters to procedure--> construct an SQL--
--> the SQL ---> Bind the SQL-(fetch the cursor write to a CSV file.
(as per http://www.unix.org.ua/orelly/oracle/bipack/ch02_03.htm)

1.Procedure READ1data to construct the Dynamic SQL   extract from this procedure will be like this.

   tselectsql VARCHAR2(300) :='SELECT ...........'

   tlwhere:=tlwhere ||' AND acmast.ACCOUNTCODE= '|| ':taccountcode';
   tfrmsql:=tfrmsql ||' ,PEJECTMEMBER prjmem';
   tfinsql:= tselectsql ||  tfrmsql   || tlwhere;

    here call the READ2writetofile(tfinsql,directory,file,delimeter)

2. Procedure READ2writetofile(tfinsql1,dire,filen,delw) will look like below

     dbms_sql.parse(  My_Cursor,  p_query, dbms_sql.native );
     -- Here we are Binding the variable:
    IF (INSTR(p_query,'taccountcode',1,1)!=0)  THEN
        DBMS_SQL.BIND_VARIABLE
(l_theCursor,':taccountcode',btaccountcode);
        p('Its found and binded');
       p(btaccountcode);      ---prints the proper value like P123456

    END IF;     repeat fetch and write a file
    end

Here are the problem

   If there are no Binded varible in the Dynamic SQL. These 2 Procedure works

   and get the out put a File

 if i Use any one of the Binded variable like the ':taccountcode' etc. Then The Program does't give any error but it does not generate the proper output
 only blank file is created. i.e not fetched any records.

Can any one advise me where i am going wrong. if possible how to debug this kind of program Thanks in Advance.

---

By the way what all i ma doing in these procedures

1.Accepts one parameter as a sting
  break these strings to different variable

2. depending up on the variables ,Construct the SQL
3. catenate the select,frm,where into one sql string
4. execute the sql and write the o/p to CSV file

Received on Thu Jun 23 2005 - 05:58:35 CDT