2

I need to alter session before executing the main sql in oracle connector used in my datastage job.

I tried altering the session from before sql tab as below.But seems this is not working alter session set star_transformation_enabled=TRUE;

When I alter the session from sql developer and run my main sql, it executes in 20 mins but from datastage job the job is running longer for more than 20 Hrs.It gives me the feeling that somehow my alter session doesnt work from datastage.

Harish Rathi
  • 21
  • 1
  • If you only want to modify one SQL statement it might be better to change the setting per statement, instead of per session. Can you directly modify the SQL and add `/*+ STAR_TRANSFORMATION */` after the first keyword? – Jon Heller Jul 11 '19 at 01:00
  • Your main SQL is a single SQL or is it PL/SQL block? – Popeye Jul 11 '19 at 06:44
  • @JonHeller Thank You! : I tried using hint /*+ STAR_TRANSFORMATION */ its not working either. – Harish Rathi Jul 12 '19 at 11:33
  • @Tejash----ORA-00980 : I am using SQL. but I tried using alter session in PL/SQL block in before sql statement in ds job but it didn't worked. – Harish Rathi Jul 12 '19 at 11:35
  • Is datastage using the same user ID as you are using in SQL*Developer? If not, that user ID probably needs to be granted `ALTER SESSION` privileges. – Mark Stewart Jul 12 '19 at 20:57

1 Answers1

0

Ok, I got your problem.

You can use the following technique to alter your session in the PL/SQL block.

BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MON-RRRR HH24:MI:SS'' ';
DBMS_OUTPUT.PUT_LINE('1 --> ' || SYSDATE);
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD/MON/RRRR HH24:MI:SS'' ';
DBMS_OUTPUT.PUT_LINE('2 --> ' || SYSDATE);
END;
/

--
Output
--

dbms_output:
1 --> 13-JUL-2019 04:52:58
2 --> 13/JUL/2019 04:52:58

db<>fiddle demo

Cheers!!

Popeye
  • 34,354
  • 4
  • 8
  • 30