1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL Record Type

Discussion in 'SQL PL/SQL' started by lokeshbao87, Jan 7, 2014.

  1. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi Friends,

    I am trying to load a feed into stage and finally into target table in our data warehouse system.

    We have a table which maps stage columns to fact table columns based on the feed.

    Based on this we create dynamic insert statement.

    Say emp_stage is stage table and emp_fact is fact/target table name. flex_field1 in emp_stage stage table will map to emp_name in emp_fact . So my dynamic insert will look like below . emp_rec is a record type which has a field flex_field1.

    'insert into emp_fact(emp_name) values(<emp_rec.flex_field1>)'

    Now I am trying to replace emp_rec.flex_field1 with value stored in flex_field1 field.

    I got the record name and field which I suppose to insert. But I don't know how to get the value of it.

    How do I do it ? your help on this is highly appreciated.

    Thanks,
    Lokesh
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why do you 'need' the value of the field before you insert it? To me that 'requirement' makes no sense. The insert statement will work just fine the way you have it written:


    Code (SQL):

    SQL> CREATE TABLE emp_stage(
      2  flex_field1 varchar2(40),
      3  flex_field2 varchar2(40),
      4  flex_field3 DATE);


    TABLE created.


    SQL>
    SQL> CREATE TABLE emp_fact(
      2  emp_name        varchar2(40),
      3  emp_job         varchar2(40),
      4  hiredt          DATE);


    TABLE created.


    SQL>
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..1000 loop
      3                  INSERT INTO emp_stage(flex_field1, flex_field2, flex_field3)
      4                  VALUES('Balrogg'||i, 'Plinkerfloom'||i, sysdate-i);
      5          END loop;
      6
      7          commit;
      8  END;
      9  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT *
      2  FROM emp_stage;


    FLEX_FIELD1                              FLEX_FIELD2                              FLEX_FIEL
    ---------------------------------------- ---------------------------------------- ---------
    Balrogg187                               Plinkerfloom187                          04-JUL-13
    Balrogg188                               Plinkerfloom188                          03-JUL-13
    Balrogg189                               Plinkerfloom189                          02-JUL-13
    Balrogg190                               Plinkerfloom190                          01-JUL-13
    Balrogg191                               Plinkerfloom191                          30-JUN-13
    Balrogg192                               Plinkerfloom192                          29-JUN-13
    Balrogg193                               Plinkerfloom193                          28-JUN-13
    Balrogg194                               Plinkerfloom194                          27-JUN-13
    Balrogg195                               Plinkerfloom195                          26-JUN-13
    Balrogg196                               Plinkerfloom196                          25-JUN-13
    Balrogg197                               Plinkerfloom197                          24-JUN-13
    ...
    Balrogg993                               Plinkerfloom993                          20-APR-11
    Balrogg994                               Plinkerfloom994                          19-APR-11
    Balrogg995                               Plinkerfloom995                          18-APR-11
    Balrogg996                               Plinkerfloom996                          17-APR-11
    Balrogg997                               Plinkerfloom997                          16-APR-11
    Balrogg998                               Plinkerfloom998                          15-APR-11
    Balrogg999                               Plinkerfloom999                          14-APR-11
    Balrogg1000                              Plinkerfloom1000                         13-APR-11


    1000 ROWS selected.


    SQL>
    SQL> DECLARE
      2          emp_rec emp_fact%rowtype;
      3
      4          cursor get_stage_data IS
      5          SELECT * FROM emp_stage;
      6  BEGIN
      7          FOR emp_rec IN get_stage_data loop
      8                  INSERT INTO emp_fact(emp_name, emp_job, hiredt)
      9                  VALUES(emp_rec.flex_field1, emp_rec.flex_field2, emp_rec.flex_field3);
     10          END loop;
     11
     12          commit;
     13  END;
     14  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT COUNT(*)
      2  FROM emp_fact;


      COUNT(*)
    ----------
          1000


    SQL>
    SQL> SELECT *
      2  FROM emp_fact
      3  WHERE emp_name NOT IN (SELECT flex_field1 FROM emp_stage);


    no ROWS selected


    SQL>

    There is no need to get the value of the variable; using the variable inserts the values as expected.
     
  3. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Thanks for the quick response.

    I definitely see below example works perfectly. But in my case the situation is little different.

    In your case you know the stage and fact column mapping straight away. But in our system we determine based on the feed we get .

    We load the record from stage to a record type object. Then we'll do the data transformation as per business rules.

    Finally in the load process we generate the dynamic insert.

    'INSERT INTO '||p_fact_table_name||'('||v_select_fields||') VALUES ('||v_select_values||')'

    ' insert into emp_fact(emp_name, emp_job, hiredt) values (<emp_rec.flex_field1>, <emp_rec.flex_field2, emp_rec.flex_field3)'

    emp_rec won't be a cursor type variable as you have mentioned in your example. emp_rec is a record type which will have 3 fields flex_field1,flex_field2.flex_field3.

    At this step I need to replace the placeholders with the actual values. emp_rec.flex_field1 has to be replaced as 'Balrogg187', similarly other fields too.

    That's why I was mentioning about a process/way to get the value of place holder .

    Please let me know if the problem scenario explanation is not clear.

    Thanks,
    Lokesh
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    IMO, you're making things harder than they need to be. It would appear from your post that you're accepting a data feed and attempting to create dynamic inserts on the fly. Is that correct? So the process you want keeps everything in memory...which can be quite resource intensive in both memory, time, and maintenance of the software process itself.

    If so, bear in mind that SQL is always faster. So why not dump your feed into table for processing? Then the PL/SQL process can use the BULK COLLECT / FORALL processing to transform the data to it's final destination. It will be faster. It will be more maintainable. It will save you money.

    Just my two cents...

    ;-)

    CJ
     
  5. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    Thanks for your response. I know like we are making it harder for our self. But that's how the whole logic has been defined in the system. They create dynamic insert string which contains the placeholder. I need to identify the placeholder and retrieve the value of it.

    As earlier mentioned my insert statement string will look like

    'insert into emp_fact(emp_name, emp_job, hiredt) values (<emp_rec.flex_field1>, <emp_rec.flex_field2>, <emp_rec.flex_field3>)'

    Then I am writing a function to identify the place holders, now my function returns string emp_rec.flex_field1. Then I need to find the value of emp_rec.flex_field1 which is where I couldn't progress.

    Similar to below example. From variable b I got the placeholder a, but how to print the value of a ? I expect variable b to have value 'test', is it possible ?

    declare
    a varchar2(10) :='test';
    b varchar2(10) := 'ba';
    BEGIN
    EXECUTE IMMEDIATE 'SELECT '||SUBSTR(b,2)||' FROM dual' into b;
    dbms_output.put_line(b);
    END;
     
  6. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    I believe the answer to your problem may be explained in Steven Feuerstein's "Oracle PL/SQL Programming", 5th Edition...Chapter 16, page 546. Something called Method 4 Dynamic SQL. I don't enough about this subject to explain it...

    HTH

    CJ
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your code doesn't work because you haven't properly quoted the literal text generated by the substr(b,2) call; this is a working example:


    Code (SQL):

    DECLARE
     a varchar2(10) :='test';
     b varchar2(10) := 'ba';
     BEGIN
     EXECUTE IMMEDIATE 'SELECT '''||SUBSTR(b,2)||''' FROM dual' INTO b;
     dbms_output.put_line(b);
     END;
    /
     

    Also you need to set serveroutput on size 1000000 (or any other size you choose) before any output will be displayed by dbms_output.put_line:


    Code (SQL):

    SQL> SET serveroutput ON SIZE 1000000
    SQL> DECLARE
      2   a varchar2(10) :='test';
      3   b varchar2(10) := 'ba';
      4   BEGIN
      5   EXECUTE IMMEDIATE 'SELECT '''||SUBSTR(b,2)||''' FROM dual' INTO b;
      6   dbms_output.put_line(b);
      7   END;
      8  /
    a


    PL/SQL PROCEDURE successfully completed.


    SQL>

    It has already been explained to you that this 'process' is defined in the most unorthodox and inefficient way; it really needs to be re-defined to take better advantage of the power of SQL and PL/SQL.
     
  8. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Thanks again for your help on this. Your code works, it gives me the value 'a'. But I wanted to get the value of (a), a is a variable which is defined.

    To make my case very simple, I have a string to work with which has the placeholder/variable name. I need to substr the name of variable, then I need to find the value of variable.

    In the below code we are finding variable name as 'a', but how to find the value of a ?

    DECLARE
    a varchar2(10) :='test';
    b varchar2(10) := 'ba';
    BEGIN
    EXECUTE IMMEDIATE 'SELECT '''||SUBSTR(b,2)||''' FROM dual' INTO b;
    dbms_output.put_line(b);
    END;
    /

    I know the process/design being used is not an efficient method, but for time being I can't change the process.

    Thanks,
    Lokesh
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Variable a is defined in the anonymous PL/SQL block but it is NOT defined for the context switch executed by the EXECUTE IMMEDIATE statement; you cannot get the value of variable a using EXECUTE IMMEDIATE:


    Code (SQL):

    SQL> DECLARE
      2   a varchar2(10) :='test';
      3   b varchar2(10) := 'ba';
      4   BEGIN
      5   EXECUTE IMMEDIATE 'SELECT '||SUBSTR(b,2)||' FROM dual' INTO b;
      6   dbms_output.put_line(b);
      7   END;
      8  /
    DECLARE
    *
    ERROR at line 1:
    ORA-00904: "A": invalid identifier
    ORA-06512: at line 5

    SQL>{/highlight]


    EXECUTE IMMEDIATE runs IN a SQL*Plus context outside OF the PL/SQL block so any VARIABLES defined IN the block are NOT visible TO the spawned context.
     
  10. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Hi ,

    I thank you for your suggestions and responses.

    Yes, I got the same error as you have got. Is there a way I could get the value of a ?



    1. Identify the placeholder in the string - a is the placeholder in string 'ab'

    2. Find the value of placeholder a ? - This is what I couldn't think of a solution .

    Thanks ,
    Lokesh
     
  11. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    That's why Dave pointed out :

    EXECUTE IMMEDIATE runs in a SQL*Plus context outside of the PL/SQL block so any variables defined IN the block are not visible to the spawned context.

    ......

    I think your only option, outside of redesigning the odd logic you're slaved to, is to check out the reference in the book I cited earlier. No idea if it fits, as I pointed out, but it might be worth a shot.
     
  12. lokeshbao87

    lokeshbao87 Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Yes, it's a very odd logic which I am forced to have a work around. I will look at the reference book to get a much deeper insight.

    Thanks,
    Lokesh
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not to my knowledge; substituting the variable a for the substr(b,2) [ which really should be substr(b,2,1)] replaces the variable name with the stored value but it still errors out:


    Code (SQL):

    SQL> DECLARE
      2   a varchar2(10) :='test';
      3   b varchar2(10) := 'ba';
      4   BEGIN
      5   EXECUTE IMMEDIATE 'SELECT '||a||' FROM dual' INTO b;
      6   dbms_output.put_line(b);
      7   END;
      8  /
    DECLARE
    *
    ERROR at line 1:
    ORA-00904: "TEST": invalid identifier
    ORA-06512: at line 5

    SQL>

    Properly quoting the string value returns your expected result:

    Code (SQL):

    SQL> DECLARE
      2   a varchar2(10) :='test';
      3   b varchar2(10) := 'ba';
      4   BEGIN
      5   EXECUTE IMMEDIATE 'SELECT '''||a||''' FROM dual' INTO b;
      6   dbms_output.put_line(b);
      7   END;
      8  /
    test


    PL/SQL PROCEDURE successfully completed.


    SQL>

    but it still requires that the string be properly quoted before it will print. You cannot expect substr(b,2,1) to provide a variable name to 'translate' into a stored value as it's simply returning a string value of one character.