DSXchange

Posted: **Tue Oct 31, 2006 2:00 am**

Hi to all,

I want to execute sql statement or database procedure before DS job run. How can i do this.....May be my question is very simple.....but i dont know.

Thanks to all.

ITsMe

Posted: **Tue Oct 31, 2006 2:13 am**

You can either write a script to call the stored procedure and call this script in before job subroutine.

or you can write a routine to call the srored procedure.

try it, there are many other alternatives

Posted: **Tue Oct 31, 2006 3:02 am**

You can call a batch file which involves
set ORACLE_SID=orcl
sqlplus userid/pwd@schema
sqlplus "Call StroedProcedure"

Posted: **Tue Oct 31, 2006 4:20 am**

Thanx..

Can you please tell me the command or any sample code to write a routine for this.

Regards,

ITsMe

Posted: **Tue Oct 31, 2006 7:24 am**

What is the database you are trying to run the procedure in? Also, if the job you are trying to run after executing the procedure has a database stage in it, you can as well call the procedure from the before sql tab itself.When do you intend to call this procedure, before start of every job?

Posted: **Tue Oct 31, 2006 10:16 am**

Here is a routine to execute an SQL command via an ODBC connection.

Code: Select all

$INCLUDE UNIVERSE.INCLUDE ODBC.H 

*** INITIALIZE VARIABLES 

      NUM.COLS = 0 
      RSTATUS = 0 
      * DSN = "" 
       *UID = "" 
       *PWD = "" 

      STMT = "drop table soi_wl_table" 
      Ans = 0

* Check and see if user passed command line args 

      GET(ARG.)DSN 
     GET(ARG.)UID 
    GET(ARG.)PWD 

      GOSUB ALLOCENV 
      GOSUB ALLOCCONNECTENV 
      GOSUB DBCONNECT 
      GOSUB ALLOCSTMTENV 
      GOSUB EXECUTESQL 
     *GOSUB CHECKCOLUMNS 
     *GOSUB BINDCOLUMNS 
     *GOSUB FETCHDATA 
      GOSUB DESTROYOBJECTS 

ALLOCENV: 

*** ALLOCATE ENVIRONMENT 

      Fn = "SQLAllocEnv" 
      IF SQLAllocEnv(henv) # SQL.SUCCESS THEN STOP "Unable to allocate SQL Environment" 

      RETURN (Ans)

ALLOCCONNECTENV: 

*** ALLOCATE CONNECTION ENVIRONMENT 

      Fn = "SQLAllocConnect" 
      IF SQLAllocConnect(henv,hdbc) # SQL.SUCCESS THEN STOP "Unable to allocate connection environment" 

      RETURN (Ans)
     
DBCONNECT: 


*** CONNECT TO DATASOURCE (ODBC) 

      Fn = "SQLConnect" 
      IF TRIM(DSN) = "" THEN STOP "Missing DSN" 
      IF TRIM(UID) = "" THEN STOP "Missing User ID" 
      IF TRIM(PWD) = "" THEN STOP "Missing User Password" 

      RSTATUS = SQLConnect(hdbc,DSN,UID,PWD) 

*** CHECK RETURN STATUS 

      IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN 
         ESTAT = SQLError(-1,hdbc,-1,SQLSTATE,DBMSCODE,ETEXT) 
         PRINT "ERROR TEXT ":ETEXT 
         PRINT "ERROR STATUS ":ESTAT 
         STOP 
      END 

      RETURN (Ans)


ALLOCSTMTENV: 

*** NOW ALLOCATE STATEMENT ENVIRONMENT 

      Fn = "SQLAllocStmt" 
      IF SQLAllocStmt(hdbc,hstmt) # SQL.SUCCESS THEN STOP "Unable to allocate statement environment" 

      RETURN (Ans)


EXECUTESQL: 

      Fn = "SQLExecDirect" 
      RSTATUS = SQLExecDirect(hstmt,STMT) 
      IF RSTATUS # SQL.SUCCESS AND RSTATUS # SQL.SUCCESS.WITH.INFO THEN 
          PRINT "Error while executing SQL statement ":STMT 
         STOP 
      END 
      RETURN (RSTATUS)
 

CHECKCOLUMNS: 
* 
* Now find out if any columns were produced. 
* 
      FUNC = "SQLNumResultCols" 
      RSTATUS = SQLNumResultCols(hstmt, NUM.COLS) 
      IF RSTATUS # SQL.SUCCESS THEN STOP "Error checking for columns" 
      
      RETURN (Ans)

BINDCOLUMNS: 

      FUNC = "SQLBindCol" 
      FOR ICOL = 1 to NUM.COLS 
         RSTATUS = SQLBindCol(hstmt,ICOL,SQL.B.DEFAULT,MAX.RET) 
         IF RSTATUS # SQL.SUCCESS THEN STOP "Error binding columns" 
      NEXT ICOL 
      
      RETURN (Ans)

FETCHDATA: 
      FUNC = "SQLFetch" 
      LOOP 
         STATUS = SQLFetch(hstmt) 
      WHILE STATUS <> SQL.NO.DATA.FOUND DO 

         PRINT MAX.RET 

      REPEAT 
      
      RETURN (Ans)

DESTROYOBJECTS: 

*** DESTROY STATEMENT ENVIRONMENT 

      RSTATUS = SQLFreeStmt(henv,SQL.UNBIND) 

*** DESTROY CONNECTION ENVIRONMENT 

      Fn = "SQLDisconnect" 
      RSTATUS = SQLDisconnect(hdbc) 
      IF RSTATUS # SQL.SUCCESS THEN STOP "Unable to destroy SQL connection environment" 


      RETURN (Ans)

Warning : No guarantees that it will work!

You need to tailor this routine to your database and table specifics and use the help of universe odbc guide to make sense of this routine code.

To find universe odbc guide, use google search.

HTH
Thanks

DSXchange

How to execute database procedure before job run?

How to execute database procedure before job run?