Skip to main content
Solved

Accessing PL/SQL variable in insert statement using SQLExecuter.

G

Hello,

I am trying to execute below plsql statement using SQLExecuter.

But the declared variable (mId) is not populating select statement "mId" value.

 

DECLARE mId number;

BEGIN

  select MAX (id) INTO maxId from @Value(table);

  insert into @Value(table) (ID,@Value(columnsStr)) VALUES (mId, @Value(ValueStr));

END;

 

/

FME_SQL_DELIMITER /

 

Expected output during execution: insert into table1 (ID,name) VALUES (10, 'test');

 

FME Output during execution: insert into table1 (ID,name) VALUES (mId, 'test'); 

 

Thanks,

G

 

Best answer by david_r

Is the mixup of "maxId" and "mId" above a typo?

You could also simplify it to a single statement:

insert into @Value(table) (ID,@Value(columnsStr)) 
VALUES ((select MAX (id) from @Value(table)), @Value(ValueStr))

That way you don't need to use PLSQL.

View original
    Did this help you find an answer to your question?

    2 replies

    david_r
    Evangelist
    • david_rEvangelist
    • Best Answer
    • April 1, 2022

    Is the mixup of "maxId" and "mId" above a typo?

    You could also simplify it to a single statement:

    insert into @Value(table) (ID,@Value(columnsStr)) 
VALUES ((select MAX (id) from @Value(table)), @Value(ValueStr))

    That way you don't need to use PLSQL.

    G

    @david_r​ Thanks. sorry for the typo. It works

    Reply


    Helpful Members This Week

    Community Stats

    31,134
    Posts
    118,044
    Replies
    38,877
    Members

    Latest FME

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.