Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Yokohama
    Posts
    3

    Unanswered: PL/SQL? Call it P/K(P?/?Q?)!

    What I want to say for PL/SQL is thus:

    1) Functions or procedure of PL/SQL with many parameters are bullshit. Parameter count of normal function should be less than eight.

    2) Performance of PL/SQL is said a little better than simple SQL statement of JDBC, but its difficulty of debug is much worse. I don't like blackbox modules.

    3) I have seen hundreds of "?" as following in S?NY import/export management system project.

    /**
    * Update Exec
    * @param edits DomainWrap
    * @exception IPOException
    */
    public void updateExec( DomainWrap edits ) throws IPOException{
    Connection con = null;
    OracleCallableStatement cstmt = null;
    try {
    con = getConnection();
    String stmt_str = "{? = call PK_M0902_W.F_M0902(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?) }";
    if( ((SM0900_App)getAppContext()).isHandlingOldCompany Code()) {
    stmt_str = "{? = call PK_M0902_W.F_M0902(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ,?,?) }";
    }
    _log.info("CallableStatement:" + stmt_str );
    if( !((SM0900_App)getAppContext()).isHandlingOldCompan yCode()) {
    _log.info("COMPANY_CD = "+getAppContext().getCompanyCd());
    }

    DomainWrap dm = getDomainDetail();

    _log.info("SHIPPER_CD = "+ dm.getString(SHIPPER_CD));
    int arraySize = edits.size();

    _log.info("ARRAY_SIZE = "+arraySize);

    String[] ArrPoNo = new String[arraySize];
    String[] ArrPartsCd = new String[arraySize];
    String[] ArrIvQty = new String[arraySize];
    String[] ArrIvPrice = new String[arraySize];
    String[] ArrCartonToNo = new String[arraySize];
    .
    .
    .
    String[] ArrGroupNo = new String[arraySize];
    String[] ArrSeq = new String[arraySize];
    String[] ArrAa105TukaKbn = new String[arraySize];
    String[] ArrAa105JyuchuYmd = new String[arraySize];
    String[] ArrDvaiJusinBUpdateDatec14 = new String[arraySize];

    for(int i=0; i<arraySize; i++){
    DomainWrap edit = edits.getDomainRecord(i);

    ArrPoNo[i] = edit.getString(PO_NO_DVAI);
    ArrPartsCd[i] = edit.getString(PARTS_CD_DVAI);
    ArrIvQty[i] = edit.getString(IV_QTY);
    .
    .
    .
    ArrTaniKbn[i] = getNullToSpace(edit,TANI_KBN);//CT0198
    ArrNetWeight[i] = edit.getString(NET_WEIGHT_2);
    ArrGrossWeight[i] = edit.getString(GROSS_WEIGHT_2);
    ArrIvM3[i] = edit.getString(IV_M3_2);
    //ArrCartonShu[i] = edit.getString(CARTON_SHU);
    ArrCartonShu[i] = getNullToSpace(edit,CARTON_SHU);//CT0198
    ArrCartonDepth[i] = edit.getString(CARTON_DEPTH);
    ArrCartonWidth[i] = edit.getString(CARTON_WIDTH);
    ArrCartonHeight[i] = edit.getString(CARTON_HEIGHT);
    ArrHacOlUpdateDateIn[i] = edit.getString(OL_UPDATE_DATE_4);
    ArrSoNo[i] = getNullToSpace(edit,PO_NO);//CT0198
    ArrGroupNo[i] = getNullToSpace(edit,GROUP_NO);//CT0198
    ArrSeq[i] = getNullToSpace(edit,EDA_NO);//CT0198

    ArrAa105TukaKbn[i] = edit.getString(TUKA_KBN_2);
    ArrAa105JyuchuYmd[i] = edit.getString(JYUCHU_YMD);
    ArrDvaiJusinBUpdateDatec14[i] = edit.getString(OL_UPDATE_DATE_3);

    _log.info("PO_NO["+i+"] = "+ArrPoNo[i]);
    }

    int colcnt = 1;
    int COMPANY_CD_COL = 0;
    int RET_COL = colcnt++;
    if ( !((SM0900_App)getAppContext()).isHandlingOldCompan yCode()) {
    COMPANY_CD_COL = colcnt++;
    }
    int SHIPPER_CD_COL = colcnt++;
    int SHIPPER_CD_F_COL = colcnt++;
    int REIV_CD_COL = colcnt++;
    int COMP_CD_COL = colcnt++;
    int MASTER_BL_COL = colcnt++;
    int MASTER_BL_F_COL = colcnt++;
    int HOUSE_BL_COL = colcnt++;
    .
    .
    .
    int ETA_YMD_COL = colcnt++;
    int ETD_YMD_COL = colcnt++;
    int FLIGHT_NO1_COL = colcnt++;
    int FLIGHT_ETA1_COL = colcnt++;
    int FLIGHT_ETD1_COL = colcnt++;
    int FLIGHT_NO2_COL = colcnt++;
    int FLIGHT_ETA2_COL = colcnt++;
    .
    .
    .
    int GRAND_TOTAL_COL = colcnt++;
    int PO_NO_COL = colcnt++;
    int PARTS_CD_COL = colcnt++;
    int IV_QTY_COL = colcnt++;
    .
    .
    .
    int SYORI_YMD_COL = colcnt++;
    int JOB_ID_COL = colcnt++;
    int USER_ID_COL = colcnt++;
    int TERMINAL_ID_COL = colcnt++;
    int OB_KBN_COL = colcnt++;
    int HAC_OL_UPDATE_DATE_IN_COL = colcnt++;
    int SO_NO_COL = colcnt++;
    .
    .
    .
    int DVAI_JUSIN_H_UPDATE_DATE_COL = colcnt++;
    int DVAI_JUSIN_B_UPDATE_DATE_COL = colcnt++;

    cstmt = ( OracleCallableStatement )(con.prepareCall(stmt_str));
    Connection oraconn = cstmt.getConnection();

    ArrayDescriptor descVc01 = ArrayDescriptor.createDescriptor("WRP_VC01", oraconn);
    .
    .
    .
    ArrayDescriptor descVc20 = ArrayDescriptor.createDescriptor("WRP_VC20", oraconn);

    cstmt.registerOutParameter( 1, OracleTypes.NUMBER );
    if (! ((SM0900_App)getAppContext()).isHandlingOldCompany Code() ) {
    cstmt.setString( COMPANY_CD_COL, getAppContext().getCompanyCd() );
    }
    cstmt.setString( SHIPPER_CD_COL, dm.getString(SHIPPER_CD) );
    //cstmt.setString( SHIPPER_CD_F_COL, dm.getString(SHIPPER_CD) );
    cstmt.setString( SHIPPER_CD_F_COL, getNullToSpace(dm,SHIPPER_CD));//CT0198
    cstmt.setString( REIV_CD_COL, dm.getString(REIV_CODE) );
    cstmt.setString( COMP_CD_COL, dm.getString(COMPANY_CODE) );
    cstmt.setString( MASTER_BL_COL, dm.getString(MASTER_BL_NO) );
    //cstmt.setString( MASTER_BL_F_COL, dm.getString(MASTER_BL_NO) );
    cstmt.setString( MASTER_BL_F_COL, getNullToSpace(dm,MASTER_BL_NO));//CT0198
    cstmt.setString( KEIJO_YMD_COL, dm.getString(KEIJYO_YMD) );
    cstmt.setString( LCH_OL_UPDATE_DATE_COL, dm.getString(OL_UPDATE_DATE_2) );
    //cstmt.setString( MEDIATION_COL, dm.getString(CHUKAI_KBN) );
    cstmt.setString( MEDIATION_COL, getNullToSpace(dm,CHUKAI_KBN));//CT0198
    cstmt.setString( PRICE_BASIS_COL, dm.getString(TATENE_KBN) );
    //cstmt.setString( POL_COL, dm.getString(FNZM_PORT_CD_2) );
    //cstmt.setString( POL_EDI_COL, dm.getString(FNZM_PORT_CD) );
    //cstmt.setString( POD_COL, dm.getString(NIAGE_PORT_CD_2) );
    //cstmt.setString( POD_EDI_COL, dm.getString(NIAGE_PORT_CD) );
    .
    .
    .
    cstmt.setString( CNTN2_SYUBETU_COL, getNullToSpace(dm,CNTN_SYUBETU_2));//CT0198
    cstmt.setBigDecimal( CNTN2_HONSU_COL, getBigDecimalZero(dm, CNTN_NO_2) );
    cstmt.setString( CNTN3_SYUBETU_COL, getNullToSpace(dm,CNTN_SYUBETU_3));//CT0198
    cstmt.setBigDecimal( CNTN3_HONSU_COL, getBigDecimalZero(dm, CNTN_NO_3) );
    cstmt.setString( TUKA_KBN_COL, dm.getString(TUKA_KBN) );
    cstmt.setBigDecimal( GRAND_TOTAL_COL, getBigDecimalZero(dm, TOTAL_AMT) );

    //set Arrays
    ARRAY poNoArray = new ARRAY(descVc16, oraconn, ArrPoNo);
    ARRAY partsCdArray = new ARRAY(descVc11, oraconn, ArrPartsCd);
    ARRAY ivQtyArray = new ARRAY(descVc09, oraconn, ArrIvQty);
    .
    .
    .
    ARRAY seqArray = new ARRAY(descVc02, oraconn, ArrSeq);
    ARRAY aa105TukaKbnArray = new ARRAY(descVc02, oraconn, ArrAa105TukaKbn);
    ARRAY aa105JyuchuYmdArray = new ARRAY(descVc08, oraconn, ArrAa105JyuchuYmd);
    ARRAY dvaiJusinBUpdateDatec14Array = new ARRAY(descVc14, oraconn, ArrDvaiJusinBUpdateDatec14);

    cstmt.setArray( PO_NO_COL, poNoArray );
    cstmt.setArray( PARTS_CD_COL, partsCdArray );
    .
    .
    .
    cstmt.setArray( CARTON_HEIGHT_COL, cartonHeightArray );

    cstmt.setInt( ARRAY_SIZE_COL, arraySize );
    cstmt.setString( SYORI_YMD_COL, getAppContext().getOnlineDate() );
    cstmt.setString( JOB_ID_COL, SM0900_Const.PGID );
    cstmt.setString( USER_ID_COL, getAppContext().getUserId() );
    cstmt.setString( TERMINAL_ID_COL, getAppContext().getTerminalId());
    cstmt.setString( OB_KBN_COL, IPOConst.OLKBN );

    cstmt.setArray( HAC_OL_UPDATE_DATE_IN_COL, hacOlUpdateDateInArray );
    cstmt.setArray( SO_NO_COL, soNoArray );
    cstmt.setArray( GROUP_NO_COL, groupNoArray );
    cstmt.setArray( SEQ_COL, seqArray );
    .
    .
    .
    cstmt.registerOutParameter( GRD_ROW_CNT_COL, OracleTypes.NUMBER );
    cstmt.registerOutParameter( ERR_IDX_COL, OracleTypes.NUMBER );
    cstmt.registerOutParameter( ERR_MSGID_COL, OracleTypes.CHAR );

    cstmt.setString( DVAI_JUSIN_H_UPDATE_DATE_COL, dm.getString(OL_UPDATE_DATE) );

    cstmt.setArray( DVAI_JUSIN_B_UPDATE_DATE_COL, dvaiJusinBUpdateDatec14Array );

    cstmt.execute();
    String ret_col = cstmt.getString( RET_COL );
    }
    }


    Those are extremely ugly and they resemble as alien's home. (Oracle PL/SQL forced such shit-of-fish codes. It doesn't receive any class nor a structure which bundles several simple data types from Java. Parameters must be completely separated and scattered to integer or character.)
    Debugging the system is playing a chess or othello game with blindfold.
    Can you trust on them?

    4) Without invention of way of passing any Java class directly as (in and out)parameter, and being away from debugging tool which is (as using Eclipse for regular Java codes) on up-to-date source code level and executable from web application server (I mean that module unit test tool is not enough. Whole application test is needed), then PL/SQL will be exactly no use for rapid and secure development in few years.

    5) PL/SQL is just a burden for syetems. It will be defeated by Java servlet, JDBC and Eclipse. In fact, such whole absurd steps mean breaking off of data between Java and P?/?Q? modules.

    6) Oracle should support Java directly and you have to wipe the poor garbage legacy of PL/SQL out.

    I'm a programmer in Japan.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PL/SQL? Call it P/K(P?/?Q?)!

    Not a happy bunny, are you?

    1) I agree, I would rarely want to write procedures with dozens of parameters. Nor do I.

    2) Don't know what you mean by "black box modules" here?

    3) Yes, Java is ugly, isn't it? Of course PL/SQL doesn't handle complex Java classes - it isn't Java, is it? Does Java handle complex PL/SQL classes? When you combine 2 different languages there has to be some compromise.

    4) You are writing an Oracle database application. Perhaps you should write it all in PL/SQL rather than half in PL/SQL, half in Java?

    5) Believe that if you like!

    6) Nonsense. PL/SQL is the best language for working with the Oracle database because it integrates so tightly with it.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: PL/SQL? Call it P/K(P?/?Q?)!


    1) Functions or procedure of PL/SQL with many parameters are bullshit. Parameter count of normal function should be less than eight.
    Absolutely, studies have found that the maximum parameter count which a programmer can remember short term is seven. This is neither an Oracle nor Java issue.


    2) Performance of PL/SQL is said a little better than simple SQL statement of JDBC, but its difficulty of debug is much worse. I don't like blackbox modules.
    My PL/SQL development tool includes typical debug functionality (step over, run to cursor, watches, breakpoints etc).


    3) I have seen hundreds of "?" as following in S?NY import/export management system project.
    This is a problem of your development language, not Oracle. My chosen language uses named parameters, not the positional '?' syntax you complain of.


    Oracle PL/SQL forced such shit-of-fish codes. It doesn't receive any class nor a structure which bundles several simple data types from Java
    Not true, this seems to be a enforced by Java, not Oracle. There are possibly hundreds of different languages being used to connect to Oracle, even ODBC supports named parameters. My chosen development language/connectivity also includes support for Oracle object tables etc.


    4) Without invention of way of passing any Java class directly as (in and out)parameter, and being away from debugging tool which is (as using Eclipse for regular Java codes) on up-to-date source code level and executable from web application server (I mean that module unit test tool is not enough. Whole application test is needed), then PL/SQL will be exactly no use for rapid and secure development in few years.
    That's a brave statement. I do wonder why you choose to continue using PL/SQL - you have the choice to develop everything in your Java and use no PL/SQL at all?


    5) PL/SQL is just a burden for syetems. It will be defeated by Java servlet, JDBC and Eclipse. In fact, such whole absurd steps mean breaking off of data between Java and P?/?Q? modules.
    Again, why continue using PL/SQL if it is such a burden?


    6) Oracle should support Java directly and you have to wipe the poor garbage legacy of PL/SQL out.
    Oracle includes it's own server side Java runtime and class libraries, most of the Oracle tools are Java based. They seem to have managed.

    It appears to me, that your frustration is with your tools, rather than PL/SQL. The tools I choose to develop with do not have the problems you describe. Saying that, the tools I use are mature, robust and expensive - oh and yes, I forgot, not fashionable.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Feb 2004
    Location
    Yokohama
    Posts
    3

    P/K is P/K

    Thanks for your reply!

    Your agreement about parameter count is the truth in which everyone shoud be honest.

    'Blackbox modules' means a module that cannot be watched inside.

    I didn't programmed the garbage codes. The former one did.

    I'm vexed that I don't own executive position in selection of architechture and I had to program in my situation. It was the authorised way. The system is very huge and million step scale. I'll change it if I had 10 fellows.

    I think that PL/SQL is not able to handle HTML. Then every web application cannot be wirrten by P/K.

    I don't hear about news of advanced tools you posted. What is the name of it? It should be announced world wide.

    Although you think it is reasonable, I'm still on opposite opinion.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697

    Re: P/K is P/K

    It's unfortunate that you are having to work on existing code. I offer this anecdote, I once took over responsibility for an application where the previous programmer had deemed to use var1, var2, var3 for all variable naming! Was this a limitation of the language we used, or of the programmer who wrote it?

    I would recommend the book "Oracle Web Applications 101" by Sten E Vesterli. It covers - designing HTML Apps, using Java, handwritten PL/SQL, Designer, Portal and Developer all in the context of building web applications.

    One of the tools I refer to (particularly for PL/SQL debugging) is TOAD. Check out the quest software website http://www.quest.com/toad/ and look for details on the PL/SQL debugger. There are other tools about, but I do not have much experience of them - SQLTools (free), PL/SQL Developer from Allround Automations etc.

    From a client development language perspective, there are many which do not have the positional syntax - C, ProC, VB, Delphi, C++ etc etc.

    Regards
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Feb 2004
    Location
    Yokohama
    Posts
    3

    PL2JSQL

    I found a URL which tells about a tool which translates PL/SQL to Java.

    http://www.vembu.com/pl2jsql.html

    It seems very cool!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •