Fetching Dynamic Execute into Record...???

[shailesh]

Originally posted by ales
Hi,
a possible workaround follows.
Avoid declaration of record-type in the package, declare record variables directly.
In addition, you shoul use bind variables for NDS, at least it's more readable.

create or replace package PkgTrial as 
--Type Pkg_Test IS RECORD (vTest Tbltest%Rowtype); 
  vFeld1 VARCHAR2(4000); 
  sPkgcode TblTest.Code%TYPE; 
End PkgTrial; 
/ 

Create or replace PROCEDURE ProTestOut(vTestOutStmt OUT Tbltest%Rowtype)as 
vStmt VARCHAR2(4000); 
Begin 
  -- use BIND VARIABLE here
  vStmt := 'SELECT '||PkgTrial.vFeld1||' FROM Tbltest WHERE CODE = :code';
  Execute Immediate vStmt into vTestOutStmt USING PkgTrial.sPkgcode;
END; 
/ 

CREATE OR REPLACE PROCEDURE ProTest(sCode VARCHAR2) AS 
  vFeld VARCHAR2(30); 
  vFeld2 VARCHAR2(4000); 

  Cursor CurFeld IS SELECT FeldName 
  FROM TblFieldName 
  WHERE TbName = 'TBLTEST'; 

  MyRec Tbltest%Rowtype;

BEGIN 
  PkgTrial.vFeld1:='';
  OPEN CurFeld; 
  LOOP 
    FETCH CurFeld INTO vFeld; 
    EXIT WHEN CurFeld%NOTFOUND; 
    IF CurFeld%ROWCOUNT=1 THEN 
      PkgTrial.vFeld1:=PkgTrial.vFeld1||' '||vFeld; 
    ELSE 
      PkgTrial.vFeld1:=PkgTrial.vFeld1||','||vFeld; 
    END IF; 
  END LOOP; 
  CLOSE CurFeld; 

  PkgTrial.sPkgcode:=sCode; 
  ProTestOut(MyRec); 
End; 
/

Ales

Hi Ales,

Thanks For your reply. ROWTYPE doesn't work. Because I don"t get all fields of table TBLTEST from table TBLFIELDNAME. If there are 10 fields in TBLTEST, TBLFIELDNAME's Feldname column has only 5 field entries of TBLTEST.

Second this number may change any time. tommarow we can have 7 fileds, So I can not hardcode fieldnames as well. So I don't know which and how many fields I have.

If I use record variable, I will get error "ORA-01007: variable not in select list". I have tried this.

Thanks & Regards,
Shailesh