POST TO FBR ( DB Level for POS Invoicing Using PRAL Service )

 CREATE TABLE SGI_MACHINES

(

  PC_NAME          VARCHAR2(50 BYTE),

  TILL_NO          VARCHAR2(50 BYTE),

  STATUS           VARCHAR2(50 BYTE),

  UNIT_ID          NUMBER,

  UNIT_NAME        VARCHAR2(100 BYTE),

  POS_ID           NUMBER,

  TILLE_STATUS_NO  NUMBER,

  IP_ADR           VARCHAR2(100 BYTE)

);

/

CREATE OR REPLACE PROCEDURE post_to_fbr (

  p_salref IN NUMBER

) IS

  l_json      CLOB;

  l_request   UTL_HTTP.req;

  l_response  UTL_HTTP.resp;

  l_buffer    VARCHAR2(32767);

  l_response_text CLOB;

  response_str varchar2(2000);

  ip_location varchar2(100)  ; --:= '192.168.50.74:8524' ;

BEGIN

  -- Build JSON

  SELECT IP_ADR||':8524' INTO IP_LOCATION FROM SGI_MACHINES WHERE PC_NAME = GET_MACHINE_NAME;

SELECT

  '{' ||

  '"InvoiceNumber":"' || '",'||

  '"POSID":"' || m.posid || '",' ||

  '"USIN":"' || m.inv_num || '",' ||

  '"DateTime":"' ||TO_CHAR(m.sal_dat,'YYYY-MM-DD HH:MI:SS') || '",' ||

  '"BuyerNTN":"' || '12345678' || '",' ||

  '"BuyerCNIC":"' || '1111111111111' || '",' ||

  '"BuyerName":"' || 'CUSTOMER NAME' || '",' ||

  '"BuyerPhoneNumber":' ||'123456789' || ',' ||

  '"PaymentMode":"' || 1 || '",' ||

  '"TotalSaleValue":"' || m.gst_sale_amount || '",' ||  

  '"TotalQuantity":"' || m.gst_qty || '",' ||

  '"TotalBillAmount":"' ||  m.gst_bill_amount || '",' ||   

  '"TotalTaxCharged":' || m.gst_stax_amount || ',' ||

  '"Discount":"' || 0 || '",' ||

  '"FurtherTax":"' || 0 || '",' ||    

  '"InvoiceType":"' || 1|| '",' ||  

  '"items":[' ||

     LISTAGG(

       '{' ||

       '"ItemCode":"' || d.icode || '",' ||

       '"ItemName":"' || d.iname || '",' ||

       '"Quantity":"' || d.qty   || '",' ||

       '"PCTCode":"'  || substr(d.gst_pct_code,1,4)||substr(d.gst_pct_code,6,9) || '",' ||

       '"TaxRate":"' || 18 || '",' ||

       '"SaleValue":"' || (d.qty * d.tp_rate)|| '",' ||

       '"TotalAmount":"' || (((d.tp_rate*18) / 100 ) * d.qty ) + (d.qty * d.tp_rate) || '",' ||

       '"TaxCharged":"' || (((d.tp_rate*18) / 100 ) * d.qty ) || '",' ||

       '"Discount":"' || 10 || '",' ||

       '"FurtherTax":"' || 0 || '",' ||

       '"InvoiceType":"' || 1 || '",' ||

       '"RefUSIN":"' || '"' ||

       '}', ','

     ) WITHIN GROUP (ORDER BY d.icode)

  || ']}' AS json_data into l_json

FROM sgi_sal_00 m

JOIN sgi_sal_01 d ON d.sal_ref = m.sal_ref

WHERE m.sal_ref = P_SALREF

GROUP BY

  m.inv_num,

  m.sal_dat,

  m.pty_cod,

  m.iamount,

  m.posid 

  ; 

  -- HTTP Request

  l_request := UTL_HTTP.begin_request(

                  'http://'||ip_location||'/api/IMSFiscal/GetInvoiceNumberByModel',

                  'POST',

                  'HTTP/1.1'

               );

  UTL_HTTP.set_header(l_request, 'Content-Type', 'application/json');

  UTL_HTTP.set_header(l_request, 'Content-Length', LENGTH(l_json));

  UTL_HTTP.write_text(l_request, l_json);

  l_response := UTL_HTTP.get_response(l_request);

  -- Read Response

  LOOP

    UTL_HTTP.read_text(l_response, l_buffer);

    l_response_text := l_response_text || l_buffer;

  END LOOP;

EXCEPTION

  WHEN UTL_HTTP.end_of_body THEN

    UTL_HTTP.end_response(l_response);

    -- Save response number if needed

   -- DBMS_OUTPUT.put_line(l_response_text);

    --response_str := l_response_text;

    --DBMS_OUTPUT.put_line(response_str);

   -- DBMS_OUTPUT.put_line(REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1));    

    update sgi_sal_00 set gov_inv_no = REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1) where sal_ref =  P_SALREF; commit;

    UPDATE SGI_SAL_00 SET QRCODE = MAKE_QR.QR_BIN(REGEXP_SUBSTR(REGEXP_SUBSTR(l_response_text, '"InvoiceNumber":"([^"]+)"', 1, 1, NULL, 1),'^\d+',1, 1)) WHERE SAL_REF = P_SALREF; commit;

END;

/

Create Window Service for ORDS

Mon, Aug 24 at 2:18 PM

I want to Create a Window Service That Can Run Oracle Apex ORDS Automatically Instead of Standalone or Tomcat

Yes. The cleanest approach is to run Oracle ORDS as a native Windows Service, rather than using standalone mode or deploying it inside Tomcat.

The architecture would be:

Windows Service → ORDS → Oracle Database

A practical implementation is to create a small Windows service wrapper that:

1. Starts ORDS automatically when Windows boots.
2. Runs ords serve in the background.
3. Restarts ORDS if it crashes.
4. Writes stdout/stderr to log files.
5. Stops ORDS cleanly when Windows shuts down.
6. Supports Start / Stop / Restart / Status.
7. Runs under a dedicated Windows service account.
8. Optionally waits for Oracle/database/network availability before starting.

Recommended approach

For a production Windows server, I'd use a native Windows Service wrapper around ORDS, rather than trying to make ORDS itself implement the Windows Service API.

For example:

C:\ords\
    bin\
        ords.exe
    config\
    logs\
        ords-service.log
        ords-error.log

C:\OracleORDSService\
    OracleORDSService.exe
    OracleORDSService.config

The service would effectively execute:

ords --config C:\ords\config serve

and Windows would manage the process as a service.

Important detail

The exact command depends on your ORDS version. Modern ORDS versions use the ords serve model, while older installations may use different startup commands.

If you tell me:

• Windows Server version (2019 / 2022 / 2025)
• ORDS version
• Java version
• whether you're using Oracle Database 19c / 21c / 23ai
• your current ORDS startup command

I can give you a complete implementation, including the Windows Service source code, installer, configuration, logging, automatic restart, and commands to install/uninstall the service.

Windows Serer 2016 Ords 24.2 Java 17 Database 19c

Perfect. With Windows Server 2016 + ORDS 24.2 + Java 17 + Oracle Database 19c, I would build this as a native Windows Service wrapper around ORDS 24.2.

The service should launch:

ords --config C:\ords\config serve

rather than Tomcat or ORDS standalone.

Recommended layout

C:\ords\
├── bin\
│   └── ords.exe
├── config\
│   └── ...
└── logs\
    ├── ords-service.log
    └── ords-error.log

C:\OracleORDSService\
├── OracleORDSService.exe
└── OracleORDSService.json

The Windows service can provide:

OracleORDSService
    |
    +-- Starts automatically with Windows
    |
    +-- Starts Java / ORDS 24.2
    |
    +-- Monitors ORDS process
    |
    +-- Restarts ORDS after failure
    |
    +-- Captures stdout/stderr
    |
    +-- Stops ORDS gracefully
    |
    +-- Reports service status to Windows

One thing I'd change from a simple wrapper

I would not hard-code your Java path or ORDS path into the executable.

Instead, use a configuration file such as:

{
    "javaHome": "C:\\Program Files\\Java\\jdk-17",
    "ordsHome": "C:\\ords",
    "ordsConfig": "C:\\ords\\config",
    "javaOptions": "-Xms512m -Xmx2048m",
    "restartOnFailure": true,
    "restartDelaySeconds": 10
}

That makes upgrades from ORDS 24.2 to a later version much easier.

Windows Service behavior

I'd configure Windows recovery as well:

First failure:   Restart Service
Second failure:  Restart Service
Subsequent:      Restart Service
Reset period:    1 day
Restart delay:   10 seconds

So if ORDS dies at 3 AM, Windows brings it back without anyone logging in.

One important consideration

Before I give you the actual service implementation, I would verify the ORDS 24.2 startup/installation requirements against Oracle's current documentation, particularly because you're on Windows Server 2016 and Java 17.

I can then give you a ready-to-build Windows Service project (C#/.NET) with:

• Install
• Uninstall
• Start
• Stop
• Restart
• Status
• automatic ORDS restart
• Java 17 support
• ORDS 24.2 support
• configurable paths
• log rotation
• Windows Event Log integration
• graceful shutdown
• service recovery
• optional delayed startup until the database/network is available

That would let you replace your current standalone/Tomcat setup with a proper Windows service.

I could not under satnd Please Guide me step by step I downloaded the winSw now tell me what to do next step by step

Yes. Since your ORDS is already working, we can now configure WinSW directly. We'll use your actual paths and your existing ORDS 24.1.1 installation.

Your paths are:

ORDS:
E:\DATA\ords

ORDS executable:
E:\DATA\ords\bin\ords.exe

ORDS config:
E:\DATA\ords\config

WinSW service folder:
C:\OracleORDSService

WinSW's configuration file should sit beside the renamed WinSW executable and have the same base name.

Step 1 — Prepare the WinSW folder

Create:

C:\OracleORDSService

Copy your downloaded:

WinSW-x64.exe

into that folder.

Rename it:

OracleORDSService.exe

So you have:

C:\OracleORDSService\
    OracleORDSService.exe

---

Step 2 — Create the WinSW XML file

Inside:

C:\OracleORDSService

create a new text file.

Important: make sure Windows doesn't create OracleORDSService.xml.txt.

The filename must be exactly:

OracleORDSService.xml

Your folder should now contain:

C:\OracleORDSService\
    OracleORDSService.exe
    OracleORDSService.xml

---

Step 3 — Put this configuration in the XML

Open OracleORDSService.xml with Notepad and paste this:

<service>

    <id>OracleORDSService</id>

    <name>Oracle ORDS Service</name>

    <description>
        Oracle REST Data Services 24.1.1 Windows Service
    </description>

    <displayname>Oracle REST Data Services</displayname>

    <executable>E:\DATA\ords\bin\ords.exe</executable>

    <arguments>--config "E:\DATA\ords\config" serve</arguments>

    <workingdirectory>E:\DATA\ords</workingdirectory>

    <env name="JAVA_TOOL_OPTIONS" value="-Dconfig.url=E:\DATA\ords\config -Xms1024M -Xmx1024M"/>

    <startmode>Automatic</startmode>

    <delayedAutoStart>true</delayedAutoStart>

    <stoptimeout>30 sec</stoptimeout>

    <logpath>C:\OracleORDSService\logs</logpath>

    <log mode="roll">
        <sizeThreshold>10485760</sizeThreshold>
        <keepFiles>10</keepFiles>
    </log>

    <onfailure action="restart" delay="10 sec"/>

    <resetfailure>1 day</resetfailure>

</service>

What this does

The important line is:

<executable>E:\DATA\ords\bin\ords.exe</executable>

WinSW will launch your actual ORDS executable.

Then:

<arguments>--config "E:\DATA\ords\config" serve</arguments>

means WinSW effectively runs:

E:\DATA\ords\bin\ords.exe --config "E:\DATA\ords\config" serve

That is the ORDS 24.1 serve command documented by Oracle.

Your existing JAVA_TOOL_OPTIONS is also explicitly passed to the service because your command prompt showed that ORDS is already using it.

The onfailure setting tells Windows/WinSW to restart ORDS if the ORDS process fails.

The log configuration creates rolling logs instead of allowing one log file to grow forever.

---

Step 4 — Create the logs folder

Create:

C:\OracleORDSService\logs

So now:

C:\OracleORDSService\
│
├── OracleORDSService.exe
├── OracleORDSService.xml
└── logs\

---

Step 5 — Very important: stop your manually running ORDS

If you currently have this running:

ords --config E:\DATA\ords\config serve

go to that Command Prompt and press:

Ctrl + C

We don't want the manually started ORDS using the port when the Windows service starts.

---

Step 6 — Open Administrator Command Prompt

Press:

Windows + R

Type:

cmd

Then press:

Ctrl + Shift + Enter

You should have an Administrator: Command Prompt.

---

Step 7 — Go to the WinSW directory

Run:

cd /d C:\OracleORDSService

Then:

dir

You should see:

OracleORDSService.exe
OracleORDSService.xml
logs

---

Step 8 — Install the service

Now run:

OracleORDSService.exe install

WinSW's install command installs the service using the XML beside the executable.

If everything is correct, you should get a successful installation message.

---

Step 9 — Start ORDS

Run:

OracleORDSService.exe start

Then:

OracleORDSService.exe status

You want to see that the service is Running.

---

Step 10 — Check Windows Services

Press:

Windows + R

Type:

services.msc

Press Enter.

Find:

Oracle REST Data Services

It should show:

Running
Automatic

That's it — ORDS is now a Windows Service.

---

Step 11 — Test APEX

Open your normal APEX URL.

For example, if you previously used:

http://SERVERNAME:8080/ords/

use the same URL.

You should get your ORDS/APEX application.

---

If the service does NOT start

Don't start changing things randomly.

Run:

cd /d C:\OracleORDSService
OracleORDSService.exe status

Then look inside:

C:\OracleORDSService\logs

WinSW recommends checking the stdout/stderr logs, the WinSW wrapper log, and Windows Event Log when a service fails to start.

Send me a screenshot of the error and I'll tell you exactly what to change.

One thing I would NOT do yet

Don't change your ORDS version or reinstall ORDS. Your existing ORDS 24.1.1 is working, so let's first get that exact installation running successfully as a Windows Service. After that, we can deal with upgrading to 24.2 separately.

Yeah HOOOOOOO Its Working Bundle of Thanks Dear

🎉 YEAHHHH! 🎉

That’s awesome! 😄

You now have:

Windows Server 2016
        │
        ▼
Oracle ORDS Windows Service
        │
        ▼
ORDS 24.1.1
        │
        ▼
Oracle Database 19c
        │
        ▼
APEX

And the best part: no Tomcat and no manually starting ORDS anymore. 💪

After a server reboot, Windows should automatically start:

Oracle REST Data Services

and WinSW will restart ORDS if the process unexpectedly crashes.

A few useful commands to keep

Start:

C:\OracleORDSService\OracleORDSService.exe start

Stop:

C:\OracleORDSService\OracleORDSService.exe stop

Restart:

C:\OracleORDSService\OracleORDSService.exe restart

Status:

C:\OracleORDSService\OracleORDSService.exe status

Logs:

C:\OracleORDSService\logs

🔥 You successfully converted ORDS into a proper Windows Service!

Nice work. Now you can reboot the server and verify that ORDS + APEX comes back automatically — that's the final confidence test.