Home -> Mailing Lists -> Oracle-L -> How to pass arguments in postgres to sql scripts compared when moving to Oracle sql

How to pass arguments in postgres to sql scripts compared when moving to Oracle sql

From: kunwar singh <krishsingh.111_at_gmail.com>
Date: Sat, 27 Apr 2024 09:06:41 -0400
Message-ID: <CAJSrDUp5JXzgZ14Zt5MGsg9+0qstQAytVp5WYRRhKqFj8R6KQg_at_mail.gmail.com> 





Hello Listers,


Hope everyone is doing well.



*Question*: How to pass arguments in postgres to sql scripts.


*Details:* When I am monitoring my production Oracle databases I have a lot


of simple sql scripts (which require one or more arguments)  which make my
monitoring/troubleshooting life simpler. How can I achieve the same in
postgres? We are migrating our Oracle databases to Postgres  and I am
modifying my scripts to do the same in Postgres.



Oracle




cat appjobcheck.sql



SELECT



    SID,



    SYS_CONTEXT.GET_CURRENT_SCHEMA() AS "SCHEMA", -- Similar to datname
    USERNAME,



    PROGRAM AS "APPLICATION_NAME",



    STATUS,



    SQL_ID, -- Use V$SQL to get full query text based on SQL_ID
    LOGON_TIME AS "BACKEND_START",



    SQL_EXEC_START AS "QUERY_START",



FROM



    V$SESSION



WHERE



    STATUS = 'ACTIVE' -- Filter to active sessions
    AND TYPE != 'BACKGROUND'; -- Exclude background processes
    AND program='&1';



The way to invoke from sqlplus is .



SQL> _at_appjobcheck batchprocessapp1  ---- batchprocessapp1 is what I want to
monitor




Postgres




cat appjobcheck.sql



SELECT



    pid,


    datname,


    usename,


    application_name,


    state,


    query,


    backend_start,


    query_start


FROM pg_stat_activity where application_name='&1';



The way I am trying the script from postgres is .




postgres=> \i appjobcheck.sql batchprocessapp1  ---- batchprocessapp1 is
what I want to monitor


 pid | datname | usename | application_name | state | query | backend_start
| query_start

-----+---------+---------+------------------+-------+-------+---------------+-------------


(0 rows)



\i: extra argument "batchprocessapp1" ignored


-- 
Cheers,
Kunwar

--
http://www.freelists.org/webpage/oracle-l


Received on Sat Apr 27 2024 - 15:06:41 CEST












Original text of this message