Howto to fill a (define) variable in sqlplus with an "host" output

[PTameris]

Hi,

I want to fill an sqlplus variable with the output of an external command, but before the connect to a database...

So:

# sqlplus /nolog
sql >!echo $PPID
123456

and now i want to fill (with define?) an variable (let see ospid) with 123456

but how?

Greetings
Peter

 

[sem]

Try to tass that variable from shell script to sqlplus and then you may refer to it as &&1.

sqlplus /nolog @script $PPID

Regards, Dima

http://seminihin.narod.ru

 

[PTameris]

Thanks Dima...

But PPID reflects on that moment not the OS Process ID from the sqlplus command....

and that is the one i want to have....(this reflects the ProcesID from the OS-shell and not the sqlplus process id)
and when i am in sqlplus and than starts the ! or host command the OS-variable PPID reflects the procesnumber of the sqlplus command....

And the OSvariable PID doesn't exist....
The variable $$ olso reflects the wrong number, because he gives me the OS Pprocess ID from the os-shell

What i need is the OSProcess ID of the sqlplus command...

But thx for the quick response.....

 

[hoinz]

If you need PID of sqlplus, you might consider to query view v$session instead of host command;

for a start:
select process from v$session
where program like 'sqlplus%'
/

This should work if you run sqlplus on your database server;
not sure if it can be done this way for remote logon.
You may get more than one row though,
and so you may need additional columns in your where condition.

hope this helps

 

[sem]

!echo -e select $PPID' a from dual \n/'>1.sql
column a new_value b
@1

Now &b holds your PPID value. Note that you should be connected before the last step.



Regards, Dima

http://seminihin.narod.ru

 

[PTameris]

Hai, Hoinz,

But i don't want to connect to the database (yet)...
So i can't run your select....
Because i am not connected.....

Greetz
Peter

 

[hoinz]

hm ...,
I see

 

[PTameris]

Sorry Sem...
For your info i am working on AIX 5.2 ML5...

SQL> !echo -e select $PPID' a from dual \n/'>1.sql
column a new_value b
@1

SQL> SQL> SP2-0734: unknown command beginning "-e select ..." - rest of line ignored.
SP2-0103: Nothing in SQL buffer to run.

 

[sem]

-e is Linux key to make echo interpret \n. The only reason is writing to file 2 lines like:

select 69435 from dual
/

You may issue 2 ehoe's:

 !echo select $PPID a from dual>1.sql
 !echo />>1.sql

Regards, Dima

http://seminihin.narod.ru

 

[sem]

PTameris, can you explain the final target? Why don't you want to connect? What's so perfect in using not connected sql*plus? Does it have an advanced editor? Or pretty look and feel?

Regards, Dima

http://seminihin.narod.ru

 

[PTameris]

Hi Sem...

We cannot connect (yet) because we are making a new login procedure with encrypted password program....

after we have a unique number for the sqlplus process, we create an tempory file (with this number) where the connect string is in the (new)(temporarily)sql-script....
for example tmp123456.sql

because we have 1250 user who can login/logout, we must have a unique number and the only way to do this, is to pick the OSprocessid number....

That's way we cannot connect yet...
so your "....from dual" doesn't work....

greetz
Peter

 

[sem]

Ok, my first idea was too complicated. The correct is much simplier

!echo define a=$PPID >1.sql

Regards, Dima

http://seminihin.narod.ru

 

[sem]

And after all, I suppose that all your 1250 users have different names, aren't they? So why don't you use their names?

Regards, Dima

http://seminihin.narod.ru

 

[PTameris]

THX Sem....

SQL> !echo define a=$PPID >1.sql

SQL> @1
SQL> prompt &a
41832

It works.....
Again Thank you......

 

[PTameris]

Hi, Dima....

uuuhhhh.. no... every one logs in al the same osuser....

greetz
Peter

 

[sem]

because we have 1250 user who can login/logout, we must have a unique number and the only way to do this, is to pick the OSprocessid number...
[/qoute]

You may generate unique numbers even from Oracle sequence after connecting to Oracle by some "nobody" (powerless) account. Dozen of other ways exists.

BTW, are you developing web application and all users connect through web server? If this is the case I'd suggest you to choose other strategy, as launching sql*plus each time would kill your application server and everlasting connects - your database. In any case I'm sure you should make a step back and revise your task and solution again.

Regards, Dima

http://seminihin.narod.ru

 

[hoinz]

Dima's solution looks good,
but may I play the role of the advocatus diaboli ?

With 1250 users, it might be that two of them are going to log in at the same time. How big are the chances that they will overwrite each other's file 1.sql, and so use the same file 1.sql, and hence get the same PPID?
According to Murphy's Law this will happen.


regards

 

[sem]

Good catch! I didn't like it from the beginning

Regards, Dima

http://seminihin.narod.ru

 

[sem]

I may suggest to combine both of my advices and pass parent process id to sql*plus to use it as file name. Though again it depends of how the whole process is organized.

Regards, Dima

http://seminihin.narod.ru

 

[PTameris]

Hi..

Yep it's still a problem....
i can direct it to a $PPID.sql file, but how to catch that number to start.....

SQL> !echo define a=$PPID >$PPID.sql

SQL> @$PPID
SP2-0310: unable to open file "$PPID"

And yes there is now a file with 12345.sql but now i must start that sql with @12345.sql....

but how?
:-(