pl/sql in oracle plugin

[mystuff]

Can we have PL/SQL statements in oracle plugin (in the before button of the plugin)?

Code: Select all

declare
  r_val varchar2(10);
begin
  r_val := ops$oracle.alter_session_set_guard('ENABLE');
  insert into temp_table values (#value1#,#value2#)
end

I am getting this

Code: Select all

ORA-06550 error 

[DSguru2B]

I am afraid not. But you can create a procedure out of your pl/sql and call the stored proc. from within datastage.

[mystuff]

I guess this would be a layman question, but what if I use two statements in Before button of oracle plugin (will it work?)

The database is down, so I can't test is as of now.

Code: Select all

ops$oracle.alter_session_set_guard('ENABLE'); 
insert into temp_table values (#value1#,#value2#);

We don't have priviledge to create procedures on database, so will there be any alternative?

[DSguru2B]

Sure. You can wrap your pl/sql in a script and execute that script. This way you can capture your return as well.
I am not sure if multiple commands can be run. I doubt it.

[mystuff]

I just tried to execute multiple statements in Before button of oracle plugin and it works :D

How can we call the PL/SQL script from Before button of oracle plugin?

Lets say I have a script abc.pl on unix server. How can I run this on the database?

[DSguru2B]

You will have to incorporate that pl/sql script into a shell script, kinda like a "here" script. Search the forum or on the net for examples of an "here".
See this example where Ray describes a "here" script for ftp.

[Hemant_Kulkarni]

I think you can call the pl/sql script using call function

Code: Select all

 call plsqlfunction(param1,param2) 

[mystuff]

You will have to incorporate that pl/sql script into a shell script, kinda like a "here" script. Search the forum or on the net for examples of an "here".
See this example where Ray describes a "here" script for ftp.

I was trying to ftp

ftp ServerName --- of database, which I need to connect

and it errors out as unknown host.

I guess its because we have non-login accounts setup. So in this case I will only have the option to create a stored procedure on the database or ask the dba group to create login account (will they wouldn't for sure).

Am I right?

[mystuff]

You will have to incorporate that pl/sql script into a shell script, kinda like a "here" script. Search the forum or on the net for examples of an "here".
See this example where Ray describes a "here" script for ftp.

I was trying to ftp

ftp ServerName --- of database, which I need to connect

and it errors out as unknown host.

I guess its because we have non-login accounts setup on the database. So in this case I will only have the option to create a stored procedure on the database or ask the dba group to create login account (which they wouldn't for sure).

Am I right?

[DSguru2B]

Can you run sqlplus from your datastage server? You should be able to. Run a sqlplus command and excute your query.

[chulett]

What the heck does sqlplus or your database have to do with needing to ftp something?

[DSguru2B]

Actually, the question should have been,
Where the heck did ftp come in from?
This entire thread is to get a pl/sql code working from within datastage. I mentioned a 'here script' and referenced Ray's post where he explains a 'here script' using an ftp example. According to me, thats all that ftp relates to the context of this thread.
I am confused as to why the OP wants to create an ftp connection to talk to the database?

[mystuff]

sorry about the ftp..... I was thinking, need to connect to that server and thats all used it...

Can you run sqlplus from your datastage server? You should be able to. Run a sqlplus command and excute your query.

yes, I am able to connect from datastage server via sqlplus CLI.

I wrote a script for it sql_script.sh.

How do I call this script from oracle plugin from before SQL?

As the PL/SQL, should be run after connection is made to the database and before select SQL queries are made.

I tried call DSU.

[chulett]

Ah, ok. You can't do this before sql so put that from your mind. Your two options are before job or before stage and I'd suggest the former.

[mystuff]

Ah, ok. You can't do this before sql so put that from your mind. Your two options are before job or before stage and I'd suggest the former.

I need to run this PL/SQL after making the connection to database (i.e. opening the session) and before making and any other(select) SQL queries.

so would the only option is to call a procedure/function using Callfrom the Before of Oracle Plugin.