Script questions

[dba_admin]

1. How do you pass a variable's value from PL/SQL to SQL script?
2. How do you pass a variable's value from "anonymous " PL/SQL to SQL script?

3. In SQL, you can execute an OS command by use "HOST." Can you do the same thing in PL/SQL? I mean, how do you run OS commands from PL/SQL?

[m1l]

I'm fairly sure that you can't do any of the things you say here, sorry !

[pras]

hi
i think u can pass the value of the variable to sql script from the sqlplus using &&variable_value in ur sql script

hope this is what u want
pras

[Pinakin]

1. & 2. I am unable to locate any stright forward solution. You can make use of temp tables. Create a temp table in oracle having 2 cols - col_name and col_value and insert the variable name and it's value in these two cols. And then you can fetch these values from your SQL Script.

3. This can be worked out by calling an EXTERNAL ROUTINE The required OS command can be stored in a DLL (any routine written in C or C++). . You register the routine in your PL/SQL routine, and then call it to executu your OS command. OR you can even your DBMS_PIPE

Do let me know for other alternative solutions ....

Good Luck,

Pinakin.

[ami]

hi
Pras is right guys...for first two questions..
about third question it is not possible to use host command in pl/sql block cause it will give u error
Identifier HOST must be declared...
Try out with UTL_FILE...
regards
Amit

[JChiappa]

For 1 &2 : if the script and the PL/SQL will be executed from the SAME session, maybe the vars could be put in a PACKAGE spec.

Regards,

Chiappa