Fetching Dynamic Execute into Record...???
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Fetching Dynamic Execute into Record...???

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Question

    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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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.
    Code:
    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


  3. #3
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    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.
    Code:
    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

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    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

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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


  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    BTW, is something wrong with "SELECT * FROM TBLTEST" and concatenating only fields named in TblFieldName?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width