Jasper Report Calling From 11 Form

 Make Sure Tomcat is Already Configured and JasperReportsIntegration Utility is Configured Also

DECLARE

   v_url VARCHAR2(4000);

   CMPNANE VARCHAR2(200);

   CMP_ADR VARCHAR2(200);

   CMP_PHN VARCHAR2(50);

   

BEGIN

SELECT COMPANYNAME INTO CMPNANE FROM COMPANY WHERE COMPANYID = :PARAMETER.CMPID;

SELECT ADDRESS INTO CMP_ADR FROM COMPANY WHERE COMPANYID = :PARAMETER.CMPID;

SELECT PHONE1 INTO CMP_PHN FROM COMPANY WHERE COMPANYID = :PARAMETER.CMPID;

   v_url :=

      'http://192.168.50.15:8282/JasperReportsIntegration/report?'||

      '_repName=ZSinvoice11'||

      '&_repFormat=pdf'||

      '&_dataSource='||'STAX'||

      '&mtit='||CMPNANE||

      '&subtitle=SALES TAX INVOICE'||

      '&madd='||CMP_ADR||

      '&mph='||CMP_PHN||

      '&invno='||:SALE1.INVNO;

   WEB.SHOW_DOCUMENT(v_url,'_blank');

END;

Update Data to Table Using DB links for Multiple Schemas

 CREATE OR REPLACE PROCEDURE PORTAL.UPDATE_FBRNO (

  p_inv  IN VARCHAR2,

  p_comp IN VARCHAR2

) IS

  resp     VARCHAR2(500);

  db_link  VARCHAR2(50);

  v_sql    VARCHAR2(1000);

  v_rows   PLS_INTEGER;

BEGIN

  -- 1) get db link for company (will raise NO_DATA_FOUND if company missing)

  SELECT TRIM(DBLINK) INTO db_link    FROM COMPANY_PROFILE   WHERE COMPANY_NAME = p_comp;

  -- 2) get value to push (will raise NO_DATA_FOUND if no local row)

  SELECT FBRINVNO INTO resp    FROM SALM   WHERE INV_NO = p_inv     AND SCHEMA_NAME = p_comp;

  IF resp IS NULL THEN

    DBMS_OUTPUT.PUT_LINE('Nothing to update: FBRINVNO is NULL for INV=' || p_inv);

    RETURN;

  END IF;

  -- 3) build dynamic update against remote DB link

  v_sql := 'UPDATE salm@' || db_link || ' SET FBRINVNO = :1 WHERE inv_no = :2';

  EXECUTE IMMEDIATE v_sql USING resp, p_inv;

  v_rows := SQL%ROWCOUNT;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('UPDATE executed on ' || db_link || ' - INV=' || p_inv ||

                       ' RESP=' || NVL(resp,'<NULL>') || ' rows_updated=' || v_rows);

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('No matching company or local inv row found. COMPANY=' || p_comp || ' INV=' || p_inv);

  WHEN OTHERS THEN

    -- Rollback in case of remote errors, and print the error

    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM || ' (DBLINK=' || NVL(db_link,'<null>') || ')');

END UPDATE_FBRNO;

/