Fetching Dynamic Execute into Record...???

[shailesh]

Hi

We are using Oracle 8.1.6 on Win 2000. I have created a package with Record Type and One more procedure with OUT Parameter. Now Procedure ProTest takes IN parameter CODE and stores in Package and passes to second procedure ProTestOut which returns OUT value in RECORD TYPE.

Please Guide me to get return value of Dynamically executed statement into Record. Since I need to process it. I tried few options but not getting proper result. So Please help

But When I tried to "Execute ProTest(CODE);
I received following error...

ERROR at line 1:
ORA-00600: internal error code, arguments: [7005], [118], [], [], [], [], [], []
ORA-06512: at "SYSADM.PROTESTOUT", line 6
ORA-06512: at "SYSADM.PROTEST", line 25
ORA-06512: at line 1
---------------------------------------------------
Please Refer code below....

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 PkgTrial.Pkg_Test)as
vStmt VARCHAR2(4000);
Begin
vStmt := 'SELECT '||PkgTrial.vFeld1||' FROM Tbltest WHERE CODE = '''||PkgTrial.sPkgcode||'''' ;
Execute Immediate vStmt into vTestOutStmt;

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 PkgTrial.Pkg_Test;

BEGIN
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;
/

Thanks & Regards,
Shailesh

[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

[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

[ales]

I don't understand what's the variable select-list good for, IMHO.
It's hard to do something like that with NDS.
Perhaps ... if you build the select-list dynamically, you could add "nulls" after the last field taken from TBLFIELDNAME up to number of columns in the TBLTEST. Then you could select into record with static number of fields.
That means, try to build select command as follows (assuming the TBLTEST has six fields and you want to select just three).
SELECT Field1, Field2, Field3, null, null, null FROM TBLTEST ...

HTH, Ales

[shailesh]

Originally posted by ales
I don't understand what's the variable select-list good for, IMHO.
It's hard to do something like that with NDS.
Perhaps ... if you build the select-list dynamically, you could add "nulls" after the last field taken from TBLFIELDNAME up to number of columns in the TBLTEST. Then you could select into record with static number of fields.
That means, try to build select command as follows (assuming the TBLTEST has six fields and you want to select just three).
SELECT Field1, Field2, Field3, null, null, null FROM TBLTEST ...

HTH, Ales

Hi I have changed my code and got required result. This may be inefficient But right now I don"t have any other option. Rather than construction query and then executing, Here I am executing each field. Storing into String Then will take this string for processing. Please look below


CREATE OR REPLACE PROCEDURE ProTest(sCode VARCHAR2) AS
vFeld VARCHAR2(30);
vFeld1 VARCHAR2(4000);
vFeld2 VARCHAR2(4000);
vStmt VARCHAR2(4000);
TYPE Cur IS REF CURSOR;
C1 Cur;
Cursor CurFeld IS SELECT FeldName
FROM TblFieldName
WHERE TbName = 'TBLTEST';


BEGIN
OPEN CurFeld;
LOOP
FETCH CurFeld INTO vFeld;
EXIT WHEN CurFeld%NOTFOUND;
VSTMT := 'SELECT '||VFELD||' FROM TBLTEST WHERE CODE = '''||sCode ||'''' ;
Open C1 FOR vSTMT ;
FETCH C1 INTO Feld1;
Feld2:=Feld2||' '||Feld1;
CLOSE C1;
END LOOP;
CLOSE CurFeld;
DBMS_OUTPUT.PUT_LINE('Final OUTPUT....'||VFELD2);
End;
/

So I get Final output in vFeld2.

Regards,
Shailesh

[ales]

Hi,
athough it's possible to do that, it is, as you said, inefficient.
Consider that processing of your code will parse, execute and fetch a query for each field you want to select. The queries will be stored in the library cache without chance to be reused. (Once again, use bind variable for sCode.)

Generally, it's advisable (and only possible, IMHO) to use dbms_sql for variable select-list since you have access to metadata. That means you can construct a query, parse, execute, get metadata and fetch. In the metadata you find the count of fields that the query returns and since the're indexed by integer you can easily concatenate their values in one string with a loop taking an advantage of implict conversion to char type.

If you still want to use NDS and don't want to decrease performance, use the trick with null fields I posted earlier. Then open the cursor CurFeld again and in a IF-THEN-ELSIF-ENDIF statement concatenate only the columns you selected. Looks clumsy but will perform better.

Ales

[ales]

BTW, is something wrong with "SELECT * FROM TBLTEST" and concatenating only fields named in TblFieldName?