• RELEVANCY SCORE 3.67

    DB:3.67:Re: Case Statement 9a




    How to ask question
    SQL and PL/SQL FAQ

  • RELEVANCY SCORE 3.52

    DB:3.52:Re: Sql Statement Execution 3x




    SQL Processing

    DB:3.52:Re: Sql Statement Execution 3x


    AnarGodjaev wrote:

    Hi,

    Below link:
    SQL Limitations For Oracle Database Lite
    http://awads.net/wp/2010/02/15/oracle-database-limits-you-may-not-know-about/
    More Information: PL/SQL Program Limits

    Thank you
    Why are you posting all these odd links?First you post a link to ancient and out of date oracle 7 documentation.Now you're posting a link to Oracle Database Lite.And a link to someone's blog about the database limits that is just a duplication of what's in the documentation.None of these were specifically mentioned by the OP as a requirement for the question.The forum does not need things sharing just because you feel like it, as that can amount to spamming. If you have specific information relevant to the question then please post it, but otherwise don't just post loads of links for no good reason; and any links you do post, make sure they relate to currently supported versions of the database unless the OP specifies otherwise.Aside from that, if you're just googling for links to post, in the hope of giving an quick answer and getting points on the forum... please don't bother. This sort of behaviour is noticed and will be reported to the admin who may terminate your account for such things.

  • RELEVANCY SCORE 3.46

    DB:3.46:Data Cannot Be Found. 8x




    Hi,

    I am doing my school project and need some urgent help.
    I am using my sql*plus to write sql statement (e.g. insert , select and etc). E.g. After doing a insert statement in sql*plus, it would show that 1 row created. And when i do the select statement, I am able to retrieve the data. But the problem is that in my Database control Tables Student View Data, there is no Data to be found And when i close my sql*plus and re open it again, the data is lost, i am unable to retrieve the data that i inserted earlier.

    May i know why I am unable to view data? Is it that my sql*plus and my Database is not connected? I only configured and created 1 database (Mydbconn) so how come my sql*plus and my database is not connected?

    Thanks!

  • RELEVANCY SCORE 3.45

    DB:3.45:Re: To Get An Execution Time Of A Sql Statement 13


    Are there any database specific command to get the execution time of the sql statement and return it as result or result meta data.

    DB:3.45:Re: To Get An Execution Time Of A Sql Statement 13

    Hi I am writing a java program in which i am going to give an sql statement.I need to find the execution time of the sql statement.Can anyone pls suggest a solution. Regards Phani

  • RELEVANCY SCORE 3.38

    DB:3.38:Re: Another Way To Write This Sql Statement ps


    What are you trying to accomplish?

    DB:3.38:Re: Another Way To Write This Sql Statement ps

    This may work well for you.

    select *
    from reg_products
    where address_id IN (
    SELECT p2.address_id
    FROM reg_products p2
    GROUP BY p2.address_id
    HAVING COUNT(DISTINCT p2.title_id) 1
    /

  • RELEVANCY SCORE 3.31

    DB:3.31:Re: Ora-00060 1k


    Hi,

    I can see one insert statement in the trace file. How can I find other sql statement?

    Regards,
    Mathew.

    DB:3.31:Re: Ora-00060 1k

    This is interesting.....it's a self-deadlock....i.e. the waiter and the blocker are the same session.....
    It's quite rare...

    What's the type of the table being inserted into? HEAP? IOT? etc...
    What, if any indexes, and their types are on the table? B-tree? Bitmap? Unique?
    What constraints are on the table? Primary key? Unique? etc...
    Are there any triggers on the table?

    I'm trying to understand how it could be a self-deadlock.

    -Mark

    PS It's possible that it could be a bug, but I want to rule out other possible causes before I go there.

    Message was edited by:
    Mark J. Bobak

  • RELEVANCY SCORE 3.29

    DB:3.29:Question About Session Trace Option (Level) f8


    Hi all,

    I have a question about sql_trace option

    I connected to SQL*PLUS and I want to check EXPLAIN PLAN FOR a SQL STATEMENT , so I found following statement to EXPLAIN PLAN :

    SQL alter session set events '10053 trace name context forever, LEVEL 1' ;

    What is meaning LEVEL in alter session statement , and what different 1 through 12 ?

    Please tell me ASAP.

    Thank you.

    DB:3.29:Question About Session Trace Option (Level) f8

    And Ishtiyaq got that quote from [url http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf]This excellent article from Wolfgang Breitling that tells you a lot about the 10053 event.

    Regards,
    Rob.

  • RELEVANCY SCORE 3.29

    DB:3.29:Re: Dynamicaly Change Table Name In Sql Statement 18


    I'm working in forms

  • RELEVANCY SCORE 3.29

    DB:3.29:Sql Question pf


    I am using jdbc to get record from access. When I do a select statement and found none matching, do I get 'invalid cursor state' sql exception?

    Thanks

    DB:3.29:Sql Question pf

    Right. I'm doing the same thing near enough. I've encountered this error in the following situations:

    - you try navigating beyond the resultset (using next when at the end of the resultset)
    - if you try to navigate when the cursor is set 'beforeFirst' (in which case use next() to move the cursor to first record)
    - if the resultset is set to travel one-direction. I use:
    connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY) to define my ResultSets.
    - If you try to pass over the resultset more then once. As a lot of other people have pointed out. The only reason for this I can see is a cheap way to update the resultset. I'm busy figuring out an alternative method.

    Hope that helps.

  • RELEVANCY SCORE 3.26

    DB:3.26:Re: Update Statement Causes Sql Server To Hang..... km


    I actually tried to use the code format tags, but it wasn't working correctly...

    Thanks, Ill try the prepared statement and let you know what happens!

    DB:3.26:Re: Update Statement Causes Sql Server To Hang..... km

    Hello fellow java developers! The goal that I have is the following: 1. Get a list of all entries in the database 2. Prompt the user for a user action 3. Upon user action, update the record in the database and goto #2... until dataset completed I have done this successfully with an odbc connection to an access database, however access does not suit my needs. What I have is a single open resultset rs1 - that contains all of the database entries. Then I prompt the user for input based on a row of data I then, open a new connection object, and a new statement object in order to update the database with the value it hangs infinitely when it hits the "executUpdate" line in the code. I have try-catches all around it but no go.... any ideas? below is my update code, it hangs on the executeUpdate line.... --- try { //connect to database String URL = "jdbc:odbc:" + m_strODBC; Connection theConn3 = DriverManager.getConnection(URL, "", ""); Statement stmt3 = theConn3.createStatement(); //stmt3.setQueryTimeout(3); stmt3.executeUpdate("Update " + m_strTableName + " set myVal=1 where Num=" + m_strMessageNum); stmt3.close(); theConn3.close(); } catch (SQLException ex) { ex.printStackTrace(); }

  • RELEVANCY SCORE 3.23

    DB:3.23:Re: Selecting And Updating In Sql 2005 cd


    Couldn't you just use an UPDATE statement with the same selection criteria which the original query used?

    DB:3.23:Re: Selecting And Updating In Sql 2005 cd

    I have a situation where I need to select some records from a table. This select query will run again after a certain amount of time. I dont want the second execution of the query to select the records that were selected in the first run. So, what I want is that as soon as I run the first run (or the subsequent runs) and I get the resultset I need to update a flag (which is a column in that table) for all the entries selected in the result set so that next time I run that query, I wont get the same records again.

  • RELEVANCY SCORE 3.23

    DB:3.23:Re: Error In If Statement 8s


    What is this line doing in there?

    variable declaration That's not valid PL/SQL code.

    Please also consider posting your code using {noformat}{noformat} tags as desribed in the SQL and PL/SQL FAQ: SQL and PL/SQL FAQ

    DB:3.23:Re: Error In If Statement 8s

    user21354 wrote:
    thanks you so much i got my error i am using if statement like this is this ok

    v_cnt INTEGER;
    v_AppDetailsId number(10,0):=0;

    if v_cnt0 then

    Select v_AppDetailsId from dual;----------------1
    return ;--------------------------------------------------2
    else
    /*some code*/

    end if;Well, it's.... ok ... but we don't know what you're trying to achieve ultimately, so we can't say if it's right or not.

    my requirement is that when v_cnt variable value is greater 0 then it should select v_AppDetailsId from dual table and then make return
    otherwise go in else condition

    actually i have googled a lot to know that can we use return statement inside the if block but could not get any
    useful result You are writing a procedure, so it won't return any result. Only functions return values.

    my motive is that if value of v_cnt is greater than 0 then i want to return some arbitrary value it is 0 otherwise
    i perform operation and return some value which is other than 0You need to write a function then.

  • RELEVANCY SCORE 3.23

    DB:3.23:Re: How To Log All Table/Row Lock Activities And The Corresponding Sql, Sid... 31


    Hi Tomasz,

    There's no current SQL are executing by session 17. The previous SQL was a select statement!

    Thanks,
    Frank

    DB:3.23:Re: How To Log All Table/Row Lock Activities And The Corresponding Sql, Sid... 31

    Hi Tomasz,

    I (as well as others) really appreciate you put up a very detailed/clear steps to debug the contention.

    It's all my fault to claim your next to last query does not return anything, it was because it's inactive! Now I've been analyze the transaction SQL and trace of my app's activities trying to figure out what my ORM library did (generated SQL).

    Thank you and Justin again for your helps.
    Frank

  • RELEVANCY SCORE 3.22

    DB:3.22:Re: Java And Oracle Sql 7f


    I can't use pipelined function because in this function I have "execute immediate ' explain plan set statement for .... ' :(

    DB:3.22:Re: Java And Oracle Sql 7f

    I have types: create or replace PACKAGE EXPLAIN_PLAN_SQL AS type explain_plan is record( operation plan_table.operation%type, object_name plan_table.object_name%type, options plan_table.options%type, cost plan_table.cost%type, cardinality plan_table.cardinality%type, bytes plan_table.bytes%type, cpu_cost plan_table.cpu_cost%type, io_cost plan_table.io_cost%type, depth plan_table.depth%type ); type explain_plan_collection is table of explain_plan; end; and function: FUNCTION GET_EXPLAIN_PLAN(select_plan in varchar2) RETURN explain_plan_sql.explain_plan_collection; How can I execute this function and get this collection in java. I am trying to do something like that: CallableStatement cstmt = null; double monthlySalary; try { cstmt = con.prepareCall("{? = call EXPLAIN_PLAN_SQL.GET_EXPLAIN_PLAN(select * from dual)}"); cstmt.registerOutParameter(1, java.sql.Types.ARRAY); cstmt.execute(); } but its return me exception ;(. Any ideas? PS. Sorry for my english ;)

  • RELEVANCY SCORE 3.19

    DB:3.19:Re: How To Get Sql Statement Of Any View ? df


    "DESCRIBE VIEW view-name" doesn't work.

    DB:3.19:Re: How To Get Sql Statement Of Any View ? df

    Besting wpafbuser1 is like shooting dead fish in a barrel. You make it too easy.

    %

  • RELEVANCY SCORE 3.19

    DB:3.19:Re: Something Wrong With Preparedstatement fx


    Hi.....

    The execution time depends on the DB you use.
    For example ,SQL Server caches the statement others not.

    In the first case, prepared Statement case , first of all the statement is prepared and that's why it takes that time to retrive date.

    The best approach Connection pooling with Poolable prepared statement.

    LOOP

    DB:3.19:Re: Something Wrong With Preparedstatement fx


    I believe the OP is using Oracle. I suspectsomeone
    before now would have noticed if the Oracle driver
    was four times slower when using preparedstatements.

    Not prepared statements. But lots and lots of people
    have noticed that Oracle can (rarely) be slower when
    using bind variables within prepared statements.

    There are different ways issues can manifest, but the
    most common way is with data skew. Sounds reasonable.

    However how does that fit into the 30 seconds for the first non-bind example?

    Your suggestion is predicated on that indexes do exist and that indexes are used in one case (non-bind) and not used in the other (bind).

    But then why would the given query require 30 seconds for the index case? I note that the column names returned do not suggest that a lot of data is being returned, so precluding an ill measurement (high network use for one and non use for other), then the first query is spending all of it time doing something besides using indexes, even though bind variables are not involved at all.

  • RELEVANCY SCORE 3.19

    DB:3.19:2.1 Ea1: Error Executing Drop Statement Againts Db 9i 64 Bit 9a


    Hi,

    This does not happen againts DB 10g 64 bit.

    Executed DROP statement (DROP TABLE, DROP VIEW, DROP PACKAGE) in SQL Worksheet againts DB 9.2.0.6.0 64 bit :
    1. on sql worksheet - Statement Output showed
    Error starting at line 1 in command:
    drop table mytable
    Error report:
    SQL Error: No more data to read from socket

    2. drop from context menu
    An error was encountered performing the requested operation:
    No more data to read from socket
    Vendor code 17410

    Any ideas?

    Regards,

    Buntoro

    Edited by: user457519 on Oct 13, 2009 8:47 PM

    Sorry, I would like to drop this thread.
    It might be my mistake.
    I tried to re-execute DROP statement in the next day and found that SQL Dev works just fine.
    It probably due to DB connections or LAN connections.

    Sorry.

    DB:3.19:2.1 Ea1: Error Executing Drop Statement Againts Db 9i 64 Bit 9a

    the DROP statement on SQL Dev 2.1 EA1 are working fine the day after I created this thread.
    It probably due to the DB or LAN connections.

  • RELEVANCY SCORE 3.11

    DB:3.11:Re: Transfering Data To Another Table 9p


    Are you suggesting to use single insert statement and single delete statement?Which part of single SQL SELECT is ambiguous or confusing?

    You wanted the most efficient solution that is done by single SQL statement

    DB:3.11:Re: Transfering Data To Another Table 9p

    See http://richardfoote.wordpress.com/2008/02/08/index-rebuild-vs-coalesce-vs-shrink-space-pigs-3-different-ones/ and note the "Rebuilding the Truth" presentation.

  • RELEVANCY SCORE 3.11

    DB:3.11:Re: Execute 1 Among Many Sql Statements On The Sql Worksheet? zf


    I have also frequently been getting ORA-00900 (invalid statement) and ORA-00911 (invalid character) errors.

    I assume that the statement selection will improve, but highlighting the individual statements in the SQL Worksheet similar to the code block "collapse" in the PL/SQL editor (not sure of the official term) would be good. For example, a [-] next to the start of each statement, with a vertical line that highlights the statement when hovering, that you can then collapse to have just the first line of the statement displayed.

  • RELEVANCY SCORE 3.11

    DB:3.11:Re: Sql On Sybase m9


    Do not use GO in the java statement it is not required.

    DB:3.11:Re: Sql On Sybase m9

    Well the problem is not with the view creation statement but the one after that. My next step in debugging would be to make sure that the nested query "SELECT SHORTDESC FROM Virtual WHERE Virtual.CATEGORYID = 1" works on its own also check that the view is created succesfully.

  • RELEVANCY SCORE 3.11

    DB:3.11:Re: Hint Ordered And Cartesian Product sx


    Please read these:

    When your query takes too long
    When your query takes too long ...

    How to Post a SQL statement tuning request
    HOW TO: Post a SQL statement tuning request - template posting

    DB:3.11:Re: Hint Ordered And Cartesian Product sx

    The explain plan result no more gives Merge Cartesian Product also the Cost is reduce by 318.

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: Sql Query Slow Due To Case Statement On Joins dj


    Thread: HOW TO: Post a SQL statement tuning request - template posting
    HOW TO: Post a SQL statement tuning request - template posting

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: Found And Sql Statement p9


    thanks a lot.
    but... there is another view or table, not dynamic.
    do you know what is that?Yes, this is your third time posting this.

    But it's still not helping.

    Oh well, best of luck.

    DB:3.10:Re: Found And Sql Statement p9

    sorry it seem contagious also. for 'hand' - head and 'struct' - struck

    dangerous robot if not working properly can effect the other person online

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: Parsing From An Sql Statement 7m


    I'd use a Map (from date to count).

    DB:3.10:Re: Parsing From An Sql Statement 7m

    id prefer if there was a way to do it by just using arrays and avoiding data structures all together

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: Update Sql Statement After Clone ac


    Your select statement returns more than one record and that is why you get that error.

    Thanks,
    Hussein

    DB:3.10:Re: Update Sql Statement After Clone ac

    Hussien:
    I am going to use "update... set ...=(select...) where..." for next clone. I am going to close this thread.
    Thank you very much for your help.

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Pl/Sql And Case Statement ≪Href≫ ka


    Thank you will edit the post

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Date Between 8j


    Strange ?

    Can you also post the source data and complete sql statement ?

    What makes you feel this is strange?

    SS

  • RELEVANCY SCORE 3.09

    DB:3.09:Write Delete Statement j9


    I want to delete the table2 records that are found in the following sql statement. How do I write my delete statement.

    select *
    from table1 a, table2 b
    where a.admission_wh_id = b.admission_wh_id
    and a.fiscal_year = b.fiscal_year

    thanks

    Brendon

    DB:3.09:Write Delete Statement j9

    or you may directly delete from the inline view (given your tables are key preserved):
    SQL SELECT emp.empno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10

    EMPNO
    ----------
    7782
    7839
    7934

    3 rows selected.
    SQL DELETE FROM (SELECT emp.*
    FROM emp, dept
    WHERE emp.deptno = dept.deptno AND emp.deptno = 10)
    3 rows deleted.

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Howto Create 'Select Statement' That Returns First Row? (Simple Table) fz


    BalusC , I hope you're not being sarcastic; this isn't a trivial sql statement obviously.You're here in a JDBC forum, not a SQL forum.

    I'll help you a little step further: two key points to focus you on in a SQL tutorial are GROUP BY and HAVING.

    DB:3.09:Re: Howto Create 'Select Statement' That Returns First Row? (Simple Table) fz

    My concern is that you're trying to solve the wrong problem. Without a high level statement of what the real problem actually is, I cannot or will not help.

    I'm not interested in helping you to solve a problem that's an incorrect contrived technical description of a slightly different problem.

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Error In Sql Statement m9


    Can you give please us the exact Oracle message (number + text) and the full query (again) ?

    DB:3.09:Re: Error In Sql Statement m9

    I can't see an "absence_reason_code" in your ST_ENROLLMENT table. In the other table there is, but not in that one.

  • RELEVANCY SCORE 3.09

    DB:3.09:Imp-00032: Sql Statement Exceeded Buffer Length Imp-00008: Unrecognized Sta 3d


    Hi,

    I have found this error at the time of import.
    Even
    1) my export and dump file absolutely fine. and
    2) Buffer cache have enough size.

    But i was doing import i was found this error. please help

    $ cat full_exp.log

    Import: Release 10.2.0.1.0 - Production on Mon May 7 19:54:20 2012

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

    Export file created by EXPORT:V10.02.01 via conventional path
    import done in US7ASCII character set and AL16UTF16 NCHAR character set
    import server uses WE8ISO8859P1 character set (possible charset conversion)
    . importing EBU's objects into EBU
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00008: unrecognized statement in the export file:
    *1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS1" LOGGING NOCOMPRESS, PARTITION "P_APP_912" VALUES LESS THAN (912) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE...*
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00032: SQL statement exceeded buffer length
    IMP-00008: unrecognized statement in the export file:
    LESS THAN (870) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS2" LOGGING NOCOMPRESS, PARTITION "P_APP_WITH_MSG_871" VALUES LESS THAN (871) PCTFREE 10 PCTUSED 40 INI...

    Import terminated successfully with warnings.

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Simple Sql Query Statement Is Needed 9s


    Sample tables, sample data, expected results...

    (and Oracle version)

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Library Cache Miss Rate c1


    That's why I confused actually. I used Quest software spotlight to testing purposes. The program showes all time %35-45 utilization for shared pool usage with flashing red error screen. When I click the explanation of error screen it gives the message at below.

    Library Cache Miss Rate alarm
    The Library Cache Miss Rate alarm occurs when the library cache hit ratio falls below a given threshold.

    The library cache hit ratio describes the frequency with which a matching SQL statement is found in the Shared pool when a SQL parse request is issued by a session. If a matching SQL statement is not found in the library cache, the SQL statement must be parsed and loaded into the library cache. Low hit rates therefore result in high CPU consumption (from parsing), and possible contention for library cache latches (when the new SQL is loaded into the library cache). An acceptable rate for the library cache get hit rate is 90-95% or higher.

    The most frequent cause of high miss rates in the library cache is the use of literals rather than bind variables in SQL statements. Bind variables reduce parse overhead by allowing otherwise identical SQL statements with different query parameters to be matched in the shared pool. Bind variables, however, preclude the use of column histograms and are therefore not suitable in all circumstances.

    DB:3.09:Re: Library Cache Miss Rate c1

    Hi Jgarry, its for your information. The system based on OLTP

    SQL Show parameter SGA

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 13008M
    sga_target big integer 11200M

    SQL Show parameter SGA

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 13008M
    sga_target big integer 11200M
    SQL Show parameter size

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    bitmap_merge_area_size integer 1048576
    create_bitmap_area_size integer 8388608
    db_16k_cache_size big integer 0
    db_2k_cache_size big integer 0
    db_32k_cache_size big integer 0
    db_4k_cache_size big integer 0
    db_8k_cache_size big integer 0
    db_block_size integer 8192
    db_cache_size big integer 0
    db_keep_cache_size big integer 0
    db_recovery_file_dest_size big integer 160G

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_recycle_cache_size big integer 0
    global_context_pool_size string
    hash_area_size integer 131072
    java_max_sessionspace_size integer 0
    java_pool_size big integer 0
    large_pool_size big integer 0
    max_dump_file_size string UNLIMITED
    object_cache_max_size_percent integer 10
    object_cache_optimal_size integer 102400
    olap_page_pool_size big integer 0
    parallel_execution_message_size integer 2152

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    sga_max_size big integer 13008M
    shared_pool_reserved_size big integer 16M
    shared_pool_size big integer 0
    sort_area_retained_size integer 0
    sort_area_size integer 65536
    streams_pool_size big integer 0
    workarea_size_policy string AUTO

    SQL select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE" from v$sga_dynamic_components;

    COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE
    ---------------------------------------------------------------- ------------ ---------- -------------------
    shared pool 784 768 0
    large pool 16 16 0
    java pool 16 16 0
    streams pool 0 0 0
    DEFAULT buffer cache 10368 9616 0
    KEEP buffer cache 0 0 0
    RECYCLE buffer cache 0 0 0
    DEFAULT 2K buffer cache 0 0 0
    DEFAULT 4K buffer cache 0 0 0
    DEFAULT 8K buffer cache 0 0 0
    DEFAULT 16K buffer cache 0 0 0
    DEFAULT 32K buffer cache 0 0 0
    ASM Buffer Cache 0 0 0

    13 rows selected.

    SQL select component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", TARGET_SIZE/1024/1024
    *2 "TARGET", FINAL_SIZE/1024/1024 "FINAL", status from v$sga_resize_ops;*

    COMPONENT OPER_TYPE OPER_MODE INITIAL TARGET FINAL STATUS
    ---------------------------------------------------------------- ------------- --------- ---------- ---------- ---------- ---------
    shared pool STATIC 0 1536 1536 COMPLETE
    large pool STATIC 0 16 16 COMPLETE
    DEFAULT buffer cache INITIALIZING 9616 9616 9616 COMPLETE
    ASM Buffer Cache STATIC 0 0 0 COMPLETE
    DEFAULT 32K buffer cache STATIC 0 0 0 COMPLETE
    DEFAULT 16K buffer cache STATIC 0 0 0 COMPLETE
    DEFAULT 8K buffer cache STATIC 0 0 0 COMPLETE
    java pool STATIC 0 16 16 COMPLETE
    streams pool STATIC 0 0 0 COMPLETE
    DEFAULT buffer cache STATIC 0 9616 9616 COMPLETE
    KEEP buffer cache STATIC 0 0 0 COMPLETE
    RECYCLE buffer cache STATIC 0 0 0 COMPLETE
    DEFAULT 2K buffer cache STATIC 0 0 0 COMPLETE
    DEFAULT 4K buffer cache STATIC 0 0 0 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9616 9680 9680 COMPLETE
    shared pool SHRINK DEFERRED 1536 1472 1472 COMPLETE
    shared pool SHRINK DEFERRED 1472 1408 1408 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9680 9744 9744 COMPLETE
    shared pool SHRINK DEFERRED 1408 1344 1344 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9744 9808 9808 COMPLETE
    shared pool SHRINK DEFERRED 1344 1280 1280 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9808 9872 9872 COMPLETE
    shared pool SHRINK DEFERRED 1280 1216 1216 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9872 9936 9936 COMPLETE
    shared pool SHRINK DEFERRED 1216 1168 1168 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9936 9984 9984 COMPLETE
    shared pool SHRINK DEFERRED 1168 1120 1120 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 9984 10032 10032 COMPLETE
    shared pool SHRINK DEFERRED 1120 1072 1072 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10032 10080 10080 COMPLETE
    shared pool SHRINK DEFERRED 1072 1024 1024 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10080 10128 10128 COMPLETE
    shared pool SHRINK DEFERRED 1024 976 976 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10128 10176 10176 COMPLETE
    shared pool SHRINK DEFERRED 976 928 928 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10176 10224 10224 COMPLETE
    shared pool SHRINK DEFERRED 928 896 896 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10224 10256 10256 COMPLETE
    shared pool SHRINK DEFERRED 896 864 864 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10256 10288 10288 COMPLETE
    shared pool SHRINK DEFERRED 864 832 832 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10288 10320 10320 COMPLETE
    shared pool SHRINK DEFERRED 832 800 800 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10320 10352 10352 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    shared pool SHRINK DEFERRED 768 736 736 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10384 10416 10416 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10432 10416 10416 COMPLETE
    shared pool GROW IMMEDIATE 720 736 736 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10416 10400 10400 COMPLETE
    shared pool GROW IMMEDIATE 736 752 752 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10400 10384 10384 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10416 10448 10432 ERROR
    shared pool SHRINK DEFERRED 736 704 720 ERROR
    shared pool GROW IMMEDIATE 752 768 768 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10368 10352 10352 COMPLETE
    shared pool GROW IMMEDIATE 784 800 800 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10368 10352 10352 COMPLETE
    shared pool GROW IMMEDIATE 784 800 800 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10368 10352 10352 COMPLETE
    shared pool GROW IMMEDIATE 784 800 800 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10368 10352 10352 COMPLETE
    shared pool GROW IMMEDIATE 784 800 800 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10368 10352 10352 COMPLETE
    shared pool GROW IMMEDIATE 784 800 800 COMPLETE
    shared pool SHRINK DEFERRED 800 768 768 COMPLETE
    DEFAULT buffer cache GROW DEFERRED 10352 10384 10384 COMPLETE
    DEFAULT buffer cache SHRINK IMMEDIATE 10384 10368 10368 COMPLETE
    shared pool GROW IMMEDIATE 768 784 784 COMPLETE

    88 rows selected.

    SQL

  • RELEVANCY SCORE 3.08

    DB:3.08:Re: Export The Result Of Sql Statement In Xml Format fp


    Make sure you have proper privileges to access UTL_FILE and DBMS_XMLQUERY.

    DB:3.08:Re: Export The Result Of Sql Statement In Xml Format fp

    Well, I don't have 11g XE to test on, but can you show us exactly what code you are now trying to run and exactly what error message you are getting.This code works for me...
    SQL edWrote file afiedt.buf
    1 begin 2 dbms_xslprocessor.clob2file(dbms_xmlgen.getXML('select * from emp'), 'TEST_DIR', 'test.xml'); 3* end;SQL /

    PL/SQL procedure successfully completed.

    And TEST_DIR is an existing Oracle directory object I've created.

  • RELEVANCY SCORE 3.08

    DB:3.08:Re: Decrement Field Value By 1 89


    Check out your SQL Statement,

    I suspect, there's an unclosed Opening Parenthesis in your statement, is it?

    DB:3.08:Re: Decrement Field Value By 1 89

    Hi Everyone I'm a little stuck here. I need to decrement the value of a field by one but for some reason my code is not working properly and I am getting an error message. Here is the Codetry { String sql = "( UPDATE Item SET Stock_Level = Stock_level-1 Where Item_ID = " + itemId +";"; Statement stmt = con.createStatement(); stmt.executeUpdate(sql); con.close(); } catch(Exception e) { System.out.println(e); }And the error is: java.sql.SQLException: General error Can anybody help me please??

  • RELEVANCY SCORE 3.06

    DB:3.06:Re: Sql Merge Statement And Versioned Tables 78


    Thanks,
    So it was as suspected !

    DB:3.06:Re: Sql Merge Statement And Versioned Tables 78

    Thanks for replying Ben.

    I need to update 80 columns. I have learnt that you can set multiple columns in the update SET clause as (c1, c2, c3...) = (select c1, c2, c3... ) e.g.
    update suppliers d
    set (postcode, v1, v2, v3) = (select s.new_postcode , s.nv1, s.nv2, s.nv3 from sup_data s where d.supplier = s.supplier)
    where exists (select * from sup_data s where d.supplier = s.supplier)That does the update I want, without being too horrible. It also is doing full scans of the suppliers_LT table in my tests, which seems inefficient when I am only updating 3 rows out of 12,000.

  • RELEVANCY SCORE 3.06

    DB:3.06:Re: Dml Attributes sx


    No, only SELECT statements (well, actually only cursors) would set SQL%Found. You can find the number of rows affected by an insert statement using SQL%RowCount.

    HTH
    John

    DB:3.06:Re: Dml Attributes sx

    I save all my enterprisiness for paying customers.

    Cheers, APC

  • RELEVANCY SCORE 3.06

    DB:3.06:Re: Oracle Optimize Insert Statement dx


    Please read "How do I ask a question on the forums?" SQL and PL/SQL FAQ
    and "How to improve the performance of my query? / My query is running slow." SQL and PL/SQL FAQ

    and follow the advice there so that we can help you better.

    DB:3.06:Re: Oracle Optimize Insert Statement dx


    The asof_date is correct as it is required by our application kind of data loading from a feed file with same timestamp.

    But your query has NO append hint because, as John said you did not specify it correctly.

  • RELEVANCY SCORE 3.06

    DB:3.06:Re: Ora-13780: Sql Statement Does Not Exist cx


    The sql and execution plan of that sql_id was flushed off from shared pool , So oracle couldn't find it.

    -Thanks

  • RELEVANCY SCORE 3.05

    DB:3.05:Re: Execute Immediate -- Udpate Statement cm


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

    DB:3.05:Re: Execute Immediate -- Udpate Statement cm

    982806 wrote:

    I am fairly new to scripting sql..pl/sql.. any help will be really appreciated!!Well, PL/SQL is not a scripting language. Just like a Kawasaki ZX-14R is not a Vespa.

    PL/SQL is a formal, declarative, procedural language with object-orientation support. It is as much a programming language as C/C++, Pascal and Java.

    I have a table where i am trying to update one of the column with table name i am passing dynamically. Wrong. This means a fundamental flaw in data model. And invariable, shoddy dynamic SQL written to damage the Oracle Shared Pool, burn additional CPU cycles, and fragment memory.

    Any help please.. where i am doing a mistake..The mistake is treating PL/SQL as if it is akin to Unix bash scripting - and not solving the problem, now requiring dynamic code, at the root. With a sound and robust data model.

    In my experience, 99% of dynamic SQL code posted in this forum is the wrong solution. And yours would seem to fall in that 99% category.

    My suggestion is to take a step back - describe the problem (not your broken dynamic SQL solution), so that forum members can provide you with a meaningful solution, instead of comments on how to make a hack compile and run without the compiler complaining.

  • RELEVANCY SCORE 3.04

    DB:3.04:Re: Problem Connect Dg Oracle 11gr2 - Sql Server 9x


    Hello Mike,

    I have no issues with with SQL*Plus, only with SQL Developer, but I have not issues with Toad nor with Pl/SQL Developer.

    On the other hand, it looks as though I might be having problems selecting from schema tables, even when I connect as sa.

    All 3 statements fails with with table does not exist, however the statement runs with out an error in MSSQL Studio.

    select "AddressLine1" from Person.Address@MSSQL2008;
    select "AddressLine1" from "Person.Address"@MSSQL2008;
    select "AddressLine1" from "Person"."Address"@MSSQL2008;I' was looking through the documentation and I only found a small statement concerning case sensitivity, but no mention of schema table translation.

    Thanks

    Jan

    DB:3.04:Re: Problem Connect Dg Oracle 11gr2 - Sql Server 9x

    Hello Mike,

    I did as suggested and looks like there are still some problems. Even when i add the property in the initsid.ora to ignore the statistics

    HS_FDS_SUPPORT_STATISTICS=FALSEOracle Corporation --- WEDNESDAY MAR 20 2013 10:05:08.697

    Heterogeneous Agent Release
    11.2.0.1.0

    Oracle Corporation --- WEDNESDAY MAR 20 2013 10:05:08.696

    Version 11.2.0.1.0

    Entered hgogprd
    HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
    Entered hgosdip
    setting HS_OPEN_CURSORS to default of 50
    HOSGIP returned value of "RECOVER" for HS_FDS_RECOVERY_ACCOUNT
    HOSGIP returned a value for HS_FDS_RECOVERY_PWD
    setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
    setting HS_IDLE_TIMEOUT to default of 0
    setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
    setting HS_NLS_NCHAR to default of "UCS2"
    setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
    setting HS_FDS_DATE_MAPPING to default of "DATE"
    setting HS_RPC_FETCH_REBLOCKING to default of "ON"
    setting HS_FDS_FETCH_ROWS to default of "100"
    setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
    setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
    setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
    setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
    setting HS_FDS_MAP_NCHAR to default of "TRUE"
    setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
    setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
    setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
    setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
    setting HS_FDS_QUERY_DRIVER to default of "FALSE"
    HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
    Parameter HS_FDS_QUOTE_IDENTIFIER is not set
    setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
    setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
    setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
    setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
    setting HS_FDS_DELAYED_OPEN to default of "TRUE"
    setting HS_FDS_WORKAROUNDS to default of "0"
    Exiting hgosdip, rc=0
    ORACLE_SID is "ms2008"
    Product-Info:
    Port Rls/Upd:1/0 PrdStat:0
    Agent:Oracle Database Gateway for MSSQL
    Facility:hsa
    Class:MSSQL, ClassVsn:11.2.0.1.0_0008, Instance:ms2008
    Exiting hgogprd, rc=0
    hostmstr: 8795972120576: HOA After hoagprd
    hostmstr: 8795972120576: HOA Before hoainit
    Entered hgoinit
    HOCXU_COMP_CSET=1
    HOCXU_DRV_CSET=178
    HOCXU_DRV_NCHAR=1000
    HOCXU_DB_CSET=178
    HOCXU_SEM_VER=112000
    Entered hgolofn at 2013/03/20-10:05:08
    RC=-1 from HOSGIP for "PATH"
    PATH from environment is "C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
    Setting PATH to "C:\oracle\product\11.2.0\gtwhome_1\dg4msql\driver\lib;C:\oracle\product\11.2.0\gtwhome_1\bin;C:\oracle\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE;C:\Program Files (x86)\MySQL\MySQL Workbench 5.2 CE\utilities;C:\oracle\product\11.2.0\client_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\WiFi\bin\;C:\Program Files\Common Files\Intel\WirelessCommon\;"
    Exiting hgolofn, rc=0 at 2013/03/20-10:05:08
    HOSGIP for "HS_OPEN_CURSORS" returned "50"
    HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
    HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
    HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
    HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
    HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
    HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
    HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
    HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
    treat_SQLLEN_as_compiled = 1
    Exiting hgoinit, rc=0 at 2013/03/20-10:05:08
    hostmstr: 8795972120576: HOA After hoainit
    hostmstr: 8795972120576: HOA Before hoalgon
    Entered hgolgon at 2013/03/20-10:05:08
    reco:0, name:OracleGateway, tflag:0
    Entered hgosuec at 2013/03/20-10:05:08
    uencoding=UTF16
    Entered shgosuec at 2013/03/20-10:05:08
    Exiting shgosuec, rc=0 at 2013/03/20-10:05:08
    shgosuec() returned rc=0
    Exiting hgosuec, rc=0 at 2013/03/20-10:05:08
    HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
    HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
    HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
    HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
    HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
    HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
    HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
    HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
    using OracleGateway as default value for "HS_FDS_DEFAULT_OWNER"
    HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
    Entered hgocont at 2013/03/20-10:05:08
    HS_FDS_CONNECT_INFO = "[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2"
    RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
    Entered hgogenconstr at 2013/03/20-10:05:08
    dsn:[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2, name:OracleGateway
    optn:
    Entered hgocip at 2013/03/20-10:05:08
    dsn:[SQL2008.dbs.local]/SQL2008/AdventureWorks2008R2
    Exiting hgocip, rc=0 at 2013/03/20-10:05:08
    Entered shgogohn at 2013/03/20-10:05:08
    ohn is 'OraGtw11g_home1'
    Exiting shgogohn, rc=0 at 2013/03/20-10:05:08
    Entered hgocont_OracleCsidToIANA at 2013/03/20-10:05:08
    Returning 2252
    Exiting hgocont_OracleCsidToIANA at 2013/03/20-10:05:08
    ##Connect Parameters (len=277)##
    ## DRIVER=Oracle 11g dg4msql-OraGtw11g_home1;
    ## SERVER=SQL2008.dbs.local\SQL2008;
    ## Database=AdventureWorks2008R2;
    #! UID=OracleGateway;
    #! PWD=*
    ## AnsiNPW=Yes;
    ## QuotedId=Yes;
    ## IANAAppCodePage=2252;
    ## OctetSizeCalculation=1;
    ## ArraySize=100;
    ## PadVarbinary=0;
    ## SupportNumericPrecisionGreaterThan38=1;
    Exiting hgogenconstr, rc=0 at 2013/03/20-10:05:08
    Entered hgopoer at 2013/03/20-10:05:08
    hgopoer, line 233: got native error 5701 and sqlstate 01000; message follows...
    [Oracle][ODBC SQL Server Driver][SQL Server]Changed database context to 'AdventureWorks2008R2'. {01000,NativeErr = 5701}[Oracle][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. {01000,NativeErr = 5703}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
    Exiting hgopoer, rc=0 at 2013/03/20-10:05:08
    hgocont, line 2686: calling SqlDriverConnect got sqlstate 01000
    Entered hgolosf at 2013/03/20-10:05:08
    ODBC Function-Available-Array 0xFFFE 0x01FF 0xFF00 0xFFFF 0x03FF 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
    0x0000 0x0000 0xFE00 0x3F5F
    Exiting hgolosf, rc=0 at 2013/03/20-10:05:09
    DriverName:HGmsss23.dll, DriverVer:06.00.0073 (b0059, U0054)
    DBMS Name:Microsoft SQL Server, DBMS Version:10.50.4000
    Exiting hgocont, rc=0 at 2013/03/20-10:05:09 with error ptr FILE:hgocont.c LINE:2686 ID:SQLDriverConnect
    SQLGetInfo returns Y for SQL_CATALOG_NAME
    SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
    Exiting hgolgon, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795947909120: HOA After hoalgon
    RPC Calling nscontrol(0), rc=0
    hostmstr: 8795947909120: RPC Before Upload Caps
    hostmstr: 8795947909120: HOA Before hoaulcp
    Entered hgoulcp at 2013/03/20-10:05:09
    Entered hgowlst at 2013/03/20-10:05:09
    Exiting hgowlst, rc=1 at 2013/03/20-10:05:09
    SQLGetInfo returns Y for SQL_PROCEDURES
    SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
    TXN Capable:2, Isolation Option:0x2f
    SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
    SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
    SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN
    0 instance capabilities will be uploaded
    Exiting hgoulcp, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoaulcp
    hostmstr: 8795946860544: RPC After Upload Caps
    hostmstr: 8795946860544: RPC Before Upload DDTR
    hostmstr: 8795946860544: HOA Before hoauldt
    Entered hgouldt at 2013/03/20-10:05:09
    NO instance DD translations were uploaded
    Exiting hgouldt, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoauldt
    hostmstr: 8795946860544: RPC After Upload DDTR
    hostmstr: 8795946860544: RPC Before Begin Trans
    hostmstr: 8795946860544: HOA Before hoabegn
    Entered hgobegn at 2013/03/20-10:05:09
    tflag:0 , initial:1
    hoi:0x12ee28, ttid (len 23) is ...
    00: 4F52434C 2E336539 32343765 342E312E [ORCL.3e9247e4.1.]
    10: 32302E35 343238 [20.5428]
    tbid (len 20) is ...
    00: 4F52434C 5B312E32 302E3534 32385D5B [ORCL[1.20.5428][]
    10: 312E345D [1.4]]
    Exiting hgobegn, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoabegn
    hostmstr: 8795946860544: RPC After Begin Trans
    hostmstr: 8795946860544: RPC Before Describe Table
    hostmstr: 8795946860544: HOA Before hoadtab
    Entered hgodtab at 2013/03/20-10:05:09
    count:1
    table: Person.Address
    Allocate hoada[0] @ 000000000552FF40
    Entered hgopcda at 2013/03/20-10:05:09
    Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:0, octet:120, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:120/0, nullbl:1, octet:120, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:4(City): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:60, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:30, sign:1, radix:10
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopoer at 2013/03/20-10:05:09
    hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
    [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
    Exiting hgopoer, rc=0 at 2013/03/20-10:05:09
    hgodtab, line 585: calling SQLFetch got sqlstate 22002
    Entered hgodafr, cursor id 0 at 2013/03/20-10:05:09
    Free hoada @ 000000000552FF40
    Exiting hgodafr, rc=0 at 2013/03/20-10:05:09
    The hoada for table Person.Address follows...
    hgodtab, line 904: NO hoada to print
    Exiting hgodtab, rc=28500 at 2013/03/20-10:05:09 with error ptr FILE:hgodtab.c LINE:585 ID:Fetching result set for tble descr
    hostmstr: 8795946860544: HOA After hoadtab
    hostmstr: 8795946860544: RPC After Describe Table
    hostmstr: 8795946860544: RPC Before SQL Bundling
    hostmstr: 8795946860544: HOA Before hoxpars
    Entered hgopars, cursor id 1 at 2013/03/20-10:05:09
    type:0
    SQL text from hgopars, id=1, len=32 ...
    00: 53454C45 4354202A 2046524F 4D202250 [SELECT * FROM "P]
    10: 6572736F 6E222E22 41646472 65737322 [erson"."Address"]
    Exiting hgopars, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoxpars
    hostmstr: 8795946860544: RPC After SQL Bundling
    hostmstr: 8795946860544: RPC Before SQL Bundling
    hostmstr: 8795946860544: HOA Before hoxopen
    Entered hgoopen, cursor id 1 at 2013/03/20-10:05:09
    hgoopen, line 87: NO hoada to print
    Deferred open until first fetch.
    Exiting hgoopen, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoxopen
    hostmstr: 8795946860544: HOA Before hoxdscr
    Entered hgodscr, cursor id 1 at 2013/03/20-10:05:09
    Allocate hoada @ 000000000552FF40
    Entered hgopcda at 2013/03/20-10:05:09
    Column:1(AddressID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:2(AddressLine1): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:0, octet:120, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:3(AddressLine2): dtype:-9 (WVARCHAR), prc/scl:60/0, nullbl:1, octet:120, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:4(City): dtype:-9 (WVARCHAR), prc/scl:30/0, nullbl:0, octet:60, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:5(StateProvinceID): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:60, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:6(PostalCode): dtype:-9 (WVARCHAR), prc/scl:15/0, nullbl:0, octet:30, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:7(SpatialLocation): dtype:-4 (LONGVARBINARY), prc/scl:2147483647/0, nullbl:1, octet:30, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:8(rowguid): dtype:-11 (GUID), prc/scl:36/0, nullbl:0, octet:30, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    Entered hgopcda at 2013/03/20-10:05:09
    Column:9(ModifiedDate): dtype:93 (TIMESTAMP), prc/scl:23/3, nullbl:0, octet:30, sign:1, radix:0
    Exiting hgopcda, rc=0 at 2013/03/20-10:05:09
    hgodscr, line 910: Printing hoada @ 000000000552FF40
    MAX:9, ACTUAL:9, BRC:100, WHT=5 (SELECT_LIST)
    hoadaMOD bit-values found (0x40:TREAT_AS_NCHAR,0x20:NEGATIVE_HOADADTY)
    DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME
    4 INTEGER N 4 4 0/ 0 0 0 0 AddressID
    12 VARCHAR N 120 120 128/ 60 1000 0 40 AddressLine1
    12 VARCHAR Y 120 120 128/ 60 1000 0 40 AddressLine2
    12 VARCHAR N 60 60 128/ 30 1000 0 40 City
    4 INTEGER N 4 4 0/ 0 0 0 0 StateProvinceID
    12 VARCHAR N 30 30 128/ 15 1000 0 40 PostalCode
    -4 LONGVARBINARY Y 0 0 0/ 0 0 0 20 SpatialLocation
    1 CHAR N 36 36 0/ 0 0 0 0 rowguid
    91 DATE N 16 16 0/ 0 0 0 0 ModifiedDate
    Exiting hgodscr, rc=0 at 2013/03/20-10:05:09
    hostmstr: 8795946860544: HOA After hoxdscr
    hostmstr: 8795946860544: RPC After SQL Bundling
    hostmstr: 8795946860544: RPC Before Get Statistics
    hostmstr: 8795946860544: HOA Before hoxtcis
    Entered hgotcis at 2013/03/20-10:05:09
    Calling SQLStatistics for Person.Address
    IndexType=SQL_TABLE_STAT: cardinality=19614
    New Index:PK_Address_AddressID, type=1, ASCENDING, UNIQUE, cardinality=19614
    ordinal position = 1
    New Index:AK_Address_rowguid, type=3, ASCENDING, UNIQUE, cardinality=19614
    ordinal position = 1
    New Index:IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode, type=3, ASCENDING, UNIQUE, cardinality=19614
    ordinal position = 1
    ordinal position = 2
    ordinal position = 3
    ordinal position = 4
    ordinal position = 5
    New Index:IX_Address_StateProvinceID, type=3, ASCENDING, NON-UNIQUE, cardinality=19614
    ordinal position = 1
    Calling SQLColumns for Person.Address
    Column "AddressID": dtype=4, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=4
    Column "AddressLine1": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=94
    Column "AddressLine2": dtype=-9, colsize=120, decdig=0, char_octet_length=120, cumulative avg row len=184
    Column "City": dtype=-9, colsize=60, decdig=0, char_octet_length=60, cumulative avg row len=229
    Column "StateProvinceID": dtype=4, colsize=10, decdig=0, char_octet_length=60, cumulative avg row len=233
    Column "PostalCode": dtype=-9, colsize=30, decdig=0, char_octet_length=30, cumulative avg row len=255
    Entered hgopoer at 2013/03/20-10:05:10
    hgopoer, line 233: got native error 0 and sqlstate 22002; message follows...
    [Oracle][ODBC SQL Server Driver]Indicator variable required but not supplied {22002}
    Exiting hgopoer, rc=0 at 2013/03/20-10:05:10
    hgotcis, line 806: calling SQLFetch got sqlstate 22002
    Exiting hgotcis, rc=28500 at 2013/03/20-10:05:10 with error ptr FILE:hgotcis.c LINE:806 ID:Fetch from SQLColumns resultset
    hostmstr: 8795946860544: HOA After hoxtcis
    hostmstr: 8795946860544: RPC After Get StatisticsI will try this on some other tables, just to make sure its to this single table that has this effect.

    Jan S.

    Edited by: jstem1177 on Mar 20, 2013 10:13 AM

  • RELEVANCY SCORE 3.04

    DB:3.04:Exception:Null Error 11


    My apologies for the long post. We are getting No Success (Exception:Null) on one report while the other is successful. They are not using the same RTF template. The No Success only happens when running both XML Publisher reports at the same time. If we run them at different times they run fine. They used to run fine at the same time and only recently has this problem started.

    The trace file and debug log from the No Success are below. I have edited the trace and log as they are too large to post here. Any comments/suggestions are greatly appreciated.

    TRACE FILE:
    PeopleTools(8.49.18) AE SQL/PeopleCode Trace - 2011-09-08
    92 12.03.31 6.717483 xmlpublisher PTFOProcessor.generateOutput error. XSL
    Exception:null

    DEBUG LOG:
    PeopleTools 8.49.18 - Application Engine Server
    Copyright (c) 1988-2011 PeopleSoft, Inc.
    All Rights Reserved

    PSAESRV started service request at 11.30.48 2011-09-08

    [090811_113049058][][STATEMENT] Logger.init(): *** DEBUG MODE IS ON. ***
    [090811_113049058][][STATEMENT] Logger.init(): LogDir=/tmp/xdolog
    [090811_113049097][][STATEMENT] [ PDF GENERATOR ]---------------------------------------------
    [090811_113049098][][STATEMENT] XDO version = Oracle XML Publisher 5.6.3
    [090811_113049098][][STATEMENT] java.home = /u128/app/psoft/ccrmqa/jre
    [090811_113049098][][STATEMENT] XDO_TOP = null
    [090811_113049098][][STATEMENT] Config Path = /u128/app/psoft/ccrmqa/appserv/xdo.cfg
    [090811_113049098][][STATEMENT] Debug Cfg Path= /u128/app/psoft/ccrmqa/jre/lib/xdodebug.cfg
    [090811_113049098][][STATEMENT] Font dir = /u128/app/psoft/ccrmqa/fonts/truetype/
    [090811_113049098][][STATEMENT] Locale = en
    [090811_113049098][][STATEMENT] Fallback font = type1.Helvetica
    [090811_113049098][][STATEMENT] [ PDF GENERATOR PROPERTIES ]----------------------------------
    [090811_113049099][][STATEMENT] digit-substitution=null(not set)

    [090811_113049103][][STATEMENT] pdf-security=false

    [090811_113049108][][STATEMENT] WARNING: Old RTF version detected, nested table disabled

    [090811_113049532][][ERROR] [Line 2131.139] Missing body begin XSL context for: ?end for-each-group?

    [090811_113049655][][STATEMENT] Untranslatable string - Against Rule:

    [090811_113056528][oracle.apps.xdo.template.FOProcessor][STATEMENT] FOProcessor constructor is called.
    [090811_113056528][oracle.apps.xdo.template.FOProcessor][STATEMENT] FOProcessor has been initialized without default config.
    [090811_113056529][oracle.apps.xdo.template.FOProcessor][STATEMENT] FOProcessor.setLocale is called with 'en'.

    [090811_113056641][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xdofo:in-table

    [090811_113105452][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xmlns:xlink

    [090811_113105453][][STATEMENT] [ PDF GENERATOR ]---------------------------------------------
    [090811_113105453][][STATEMENT] XDO version = Oracle XML Publisher 5.6.3
    [090811_113105453][][STATEMENT] java.home = /u128/app/psoft/ccrmqa/jre
    [090811_113105453][][STATEMENT] XDO_TOP = null
    [090811_113105453][][STATEMENT] Config Path = /u128/app/psoft/ccrmqa/appserv/xdo.cfg
    [090811_113105453][][STATEMENT] Debug Cfg Path= /u128/app/psoft/ccrmqa/jre/lib/xdodebug.cfg
    [090811_113105453][][STATEMENT] Font dir = /u128/app/psoft/ccrmqa/fonts/truetype/
    [090811_113105453][][STATEMENT] Locale = en
    [090811_113105453][][STATEMENT] Fallback font = type1.Helvetica
    [090811_113105453][][STATEMENT] [ PDF GENERATOR PROPERTIES ]----------------------------------

    [090811_113105461][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xdofo:w

    [090811_113106181][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xdofo:w
    [090811_113106181][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xdofo:h
    [090811_113112499][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: master-reference
    [090811_113112499][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: initial-page-number
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: xml:space
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: height
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: font-family
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: white-space-collapse
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: font-size
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: color
    [090811_113112502][oracle.apps.xdo.template.fo.datatype.AttrKey][STATEMENT] WARNING: Found undetermined AttrKey: font-weight

    [090811_113112505][][STATEMENT] FO parsing error occurred at 'fo:block' near line 2, column 1501566

    Error generating report output: (235,2309) PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:ProcessReport PCPC:51640 Statement:1154
    Called from:XML_NCCPEL01.MAIN.GBL.default.1900-01-01.ExecRpt.OnExecute Statement:12

    Process 249029 ABENDED at Step XML_NCCPEL01.MAIN.ExecRpt (PeopleCode) -- RC = 24 (108,524)

    Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

    PSAESRV completed service request at 11.31.13 2011-09-08

    DB:3.04:Exception:Null Error 11

    Has anyone ever see the 'Exception:Null' error while runing an XML Publisher report?

  • RELEVANCY SCORE 3.03

    DB:3.03:Re: Sql More Time f7


    That's a new one on me.

    "Please tune my SQL statement without my showing you my SQL statement.

    DB:3.03:Re: Sql More Time f7

    There are only two valid settings for CURSOR_SHARING in my (ideal) world:

    EXACT
    and
    YUCK

    :)

  • RELEVANCY SCORE 3.02

    DB:3.02:Re: Sql Advisory z3


    hi,

    I dropped the old sql profile and it showed

    1- SQL Profile Finding (see explain plans section below)
    --------------------------------------------------------
    A potentially better execution plan was found for this statement.

    Recommendation (estimated benefit: 95.98%)
    ------------------------------------------
    - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name = 'TASK_144550',
    replace = TRUE);Accepted the profile

    execute dbms_sqltune.accept_sql_profile(task_name = 'TASK_144550',replace = TRUE);

    PL/SQL procedure successfully completed.Again ran the advisory for the sql it showed again

    1- SQL Profile Finding (see explain plans section below)
    --------------------------------------------------------
    A potentially better execution plan was found for this statement.

    Recommendation (estimated benefit: 95.98%)
    ------------------------------------------
    - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name = 'TASK_144551',
    replace = TRUE);

    -------------------------------------------------------------------------------
    ADDITIONAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    - SQL Profile "SYS_SQLPROF_024bc086e7634001" exists for this statement and
    was ignored during the tuning process.Why is that ignoring again??

    baskar.l

    DB:3.02:Re: Sql Advisory z3

    You need to check the EXECUTION plan: V$SQL_PLAN
    :p

  • RELEVANCY SCORE 3.02

    DB:3.02:Re-Compilation Error 8k


    Hi,

    I use below script to re-compilation of invalid objects and logon as sysdba but I found the error msg after run the script. the oracle version is 8.1.7. pls help

    Script
    ***
    set serveroutput on size 1000000

    declare
    sql_statement varchar2(200);
    cursor_id number;
    ret_val number;
    begin

    dbms_output.put_line(chr(0));
    dbms_output.put_line('Re-compilation of Invalid Objects');
    dbms_output.put_line('---------------------------------');
    dbms_output.put_line(chr(0));

    for invalid in (select object_type, owner, object_name
    from sys.dba_objects o,
    sys.order_object_by_dependency d
    where o.object_id = d.object_id(+)
    and o.status = 'INVALID'
    and o.object_type in ('PACKAGE', 'PACKAGE BODY',
    'FUNCTION',
    'PROCEDURE', 'TRIGGER',
    'VIEW')
    order by d.dlevel desc, o.object_type) loop

    if invalid.object_type = 'PACKAGE BODY' then
    sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
    ' compile body';
    else
    sql_statement := 'alter '||invalid.object_type||' '||invalid.owner||'.'||
    invalid.object_name||' compile';
    end if;

    /* now parse and execute the alter table statement */
    cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
    ret_val := dbms_sql.execute(cursor_id);
    dbms_sql.close_cursor(cursor_id);

    dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
    invalid.object_name, 32)||' : compiled');
    end loop;

    end;
    /
    ****

    Error msg
    ***
    ERROR at line 14:
    ORA-06550: line 14, column 27:
    PLS-00201: identifier 'SYS.ORDER_OBJECT_BY_DEPENDENCY' must be declared
    ORA-06550: line 12, column 20:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 23, column 10:
    PLS-00364: loop index variable 'INVALID' use is invalid
    ORA-06550: line 23, column 7:
    PL/SQL: Statement ignored
    ORA-06550: line 37, column 41:
    PLS-00364: loop index variable 'INVALID' use is invalid
    ORA-06550: line 37, column 7:
    PL/SQL: Statement ignored
    ***

    Thanks

    DB:3.02:Re-Compilation Error 8k

    You will have to create SYS.ORDER_OBJECT_BY_DEPENDENCY
    as
    CREATE OR REPLACE VIEW SYS.ORDER_OBJECT_BY_DEPENDENCY
    (
    DLEVEL,
    OBJECT_ID
    )
    AS
    select max(level), object_id from public_dependency
    connect by object_id = prior referenced_object_id
    group by object_id;

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Query Tunning a9


    LRAJESH wrote:
    Hi All,

    SELECT *
    FROM dummy
    WHERE
    (role IN ('CPA','TPA','BROKER','ATTORNEY','OTHER') OR (role='TRUST' AND code!=3))

    How to tune above query,Please help me on this.

    Thanks,
    L.RajeshThread: HOW TO: Post a SQL statement tuning request - template posting
    HOW TO: Post a SQL statement tuning request - template posting

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Oracle 10g Audit Function On Views ? kx


    @ DK2010 You beat me to it :)

    Here's my example proving that it works

    C:\sqlplus

    SQL*Plus: Release 11.2.0.3.0 Production on Fri May 3 12:31:13 2013

    Copyright (c) 1982, 2011, Oracle. All rights reserved.

    Enter user-name: / as sysdba

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL delete sys.aud$;

    2 rows deleted.

    SQL commit;

    Commit complete.

    SQL conn scott/tiger
    Connected.
    SQL create view x as select * from emp;

    View created.

    SQL audit all on x by access;

    Audit succeeded.

    SQL select statement, userid from sys.aud$;

    STATEMENT USERID
    ---------- ------------------------------
    12 SCOTT

    SQL select * from x where 1=0;

    no rows selected

    SQL select statement, userid from sys.aud$;

    STATEMENT USERID
    ---------- ------------------------------
    12 SCOTT
    22 SCOTT

    SQL Where's your example?

    DB:3.01:Re: Oracle 10g Audit Function On Views ? kx

    So hopefully the system is built like this :)

    Thanks you again !

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Aggregation Of Query Output - Select Statement Help 38


    Right forum : SQL and PL/SQL

    DB:3.01:Re: Aggregation Of Query Output - Select Statement Help 38

    Dear User,

    The suggested URL is "sql/plsql forum" where you get better response. and as per your requirement search on the forum. the same question asked thousand times.

    Regards,
    Taj

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Prevent Sql Injection Attack dc


    I think the select statement is just an example.

    DB:3.01:Re: Prevent Sql Injection Attack dc

    If 'sequenceName' is user-controlled input, then yes, that's a SQL injection risk. If it is not (e.g. backing map, propertiesfile, at least server-controlled, etc), then I shouldn't care less or more about it.

    If it is actually user-controlled input, I should just create an utility method using String#matches() which validates the input string. E.g. only allow a-z, A-Z, 0-9, _, ., etcetera, otherwise return false or throw an Exception.

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Continue Statement In Pl/Sql am


    Thanks. I was not very sure of that.

    DB:3.01:Re: Continue Statement In Pl/Sql am

    Sorry Billy, my early days in ITL Mini Cobol have innured me to the GOTO. It was the only mechanism we had for controlling program flow. So I feel quite confident in asserting that there is nothing inherently wrong with GOTO statements in a well-structured program. GOTOs are considered harmful simply because their potential for abuse is so vast and so easy, and modern computer languages have safer and neater alternatives.

    I have never coded a GOTO in a piece of production PL/SQL. I sincerely hope I never have to. I certainly would not encourage anybody else to use them. But the syntax exists.

    Cheers, APC

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Help Understanding Cursors, Scope, Variables, And Looping. z1


    You have coded a nested loop join in PL/SQL. The outer cursor is iterated through and for each row found, a nested cursor is opened and executed.

    Why?

    SQL is very capable of doing nested loop joins.. and hash joins, merge joins and a bunch of other join methods. Best of all, it will decide which one is the best to use to achieve the desired results, and it will outperform any PL/SQL code that attempts to join manually.

    The Very 1st Rule of PL/SQL Programming is:
    Maximize SQL. Minimize PL/SQL.

    In other words, if you can do it in SQL, then do it in SQL. Not PL/SQL. Use PL/SQL only where SQL is truly incapable and PL/SQL's formal programming logic and process conditional controls are needed.

    I would not be surprised if this entire PL/SQL procedure can be replaced by a single SQL statement.

    A comment on your PL/SQL coding style. Why do you try to make all reserved words uppercase? This is not done in Pascal, Java, Delphi, Visual Basic, etc. etc.

    Why treat PL/SQL differently? It is just another programming language. Nothing more and nothing less.

    Whoever the idiot is that originally started this twisted standard of using uppercase in PL/SQL to "highlight" reserved words deserve a good talking to... with a lead pipe.

    DB:3.01:Re: Help Understanding Cursors, Scope, Variables, And Looping. z1

    It seems as there is no end to variety in these solutions and yet they seem amazingly elusive at times. Thanks for the help. Everything I hear on this forum I store up for later. If I have any problems with this logic, that will be my next step. But if it works, I will leave it. I already have too much time in it.

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Return To Vendor - Sql mc


    Could you re-past the SQL statement and put the [ code ] sql statement [ /code ] tags around it so the formatting is preserved. It will make it much easier to read...

    DB:3.01:Re: Return To Vendor - Sql mc

    NA

    Edited by: user10590173 on Jul 11, 2011 2:38 PM

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Sql Statement General Enquiry pc


    so i cant reference tables off of combo boxes

    DB:3.01:Re: Sql Statement General Enquiry pc

    I guess that's what makes VB so popular: the wiring is
    done for you.

    It does lock you into two-tier client/server mode.
    How does that scale?I remember being frustrated because it was easy to access 4 serial ports in VB where in C++ only 2 were accessible. Of course once I wrote the code I could access 16 via C++, and there was absolutely no way to do that in VB.

  • RELEVANCY SCORE 3.00

    DB:3.00:Re: Procedure Help cf


    CURSOR C_BLOB_COLS IS
    SELECT COLUMN_NAME --- Which is of VARCHAR2 Type
    FROM USER_TAB_COLUMNS
    ....

    LB_COLUMN_NAME BLOB; --- local variable of BLOB datatypeFETCH C_BLOB_COLS --- Fetches a VARCHAR2 value
    INTO LB_COLUMN_NAME; --- Into A BLOB variable??and thats why you get this error
    Error(34,1): PL/SQL: SQL Statement ignored
    Error(34,24): PLS-00386: type mismatch found at 'LB_COLUMN_NAME' between FETCH cursor and INTO variables
    Error(37,1): PL/SQL: Statement ignored
    Error(37,11): PLS-00306: wrong number or types of arguments in call to '||'

    DB:3.00:Re: Procedure Help cf


    Sorry I am still unable to figure out what you are looking for
    if some one give table name and image value give in front end(.NET),Understood that table name is being defined on front end but what about image? Is your front end reading the content of image and populating it in a BLOB type IN Parameter?
    The way you are using execute immediate leaves a serious security flaw in your application.
    i need update the image in the relevent table.Again, what sense does does it make to append a blank space to a BLOB??

    As already said, your approach is prone to SQL injection,
    However if you still want to continue with this bug in your code, use bind for blob values.
    Something like this:
    LC_SQL := 'UPDATE '||P_TABLE_NAME||' SET '||LB_COLUMN_NAME||' = ':l_blb WHERE '||LC_COLUMN_NAME||' = '||P_PK_COL_VAL;
    execute immediate lc_sql using P_BLOB_COL_VAL;

  • RELEVANCY SCORE 3.00

    DB:3.00:Difference Between Sas Libname Statement And Sas Sql Pass Through Statement z7



    Please explaine me how sas libname statement is defferent from SAS SQL passthrough statement and why sas sql pass through statement is more efficient than libname statement

    For what reason we use sql pass through statement.

  • RELEVANCY SCORE 3.00

    DB:3.00:Re: Jbo-27122: Sql Error During Statement Preparation On Vo pz


    Hi Ram,
    I found the problem in the VO.xml. There is another problem when extending the VO. The original VO is not showing the correct SQL when tried to open in JDdeveloper compared to it's VO.xml. I got and error message(oracle.xml.parser.v2).

    Please help

    Sam

    DB:3.00:Re: Jbo-27122: Sql Error During Statement Preparation On Vo pz

    With JDeveloper it happens most of the time.

    whenever get any error and it looks that it is something different, try to restart the JDeveloper. Most of the time it solves the error.

  • RELEVANCY SCORE 2.99

    DB:2.99:Re: Pl/Sql Problem With Select Statement x1


    Could you post the sample data and the output from the SQL prompt for this query.

    Do you need to consider the NULL values also in these fields?

    DB:2.99:Re: Pl/Sql Problem With Select Statement x1

    Hello John,

    I've only copied the part of the procedure, which causes the problem. In the remaining code of the procedure the parameters were manipulated and mustn't be null, therefore there is a raise_application_error between it and the select-statement, which is invoked if Anfang or Ende is null. Out of this reason the remaining part of the procedure is irrelevant.

  • RELEVANCY SCORE 2.98

    DB:2.98:Sql Statement Error zd


    Hi, All

    I execute sql statement "select distinct a.name from student a, score b;" in the sql*plus.
    Found the following error:
    ORA-08103 object no longer exists.
    but student and score table exist in database. I can get data from the two tables separately.

    Thanks any help in advance!

    DB:2.98:Sql Statement Error zd

    uh-oh! One cause of this error, and a likely one in the circumstances you describe (you're not doing exports or DDL), is data corruption. I understand this is pretty nasty to recover from (I've been lucky enough never to suffer from it myself).

    Do you have a support contract i.e. can you get on to Metalink? Can you restore your database i.e. do you have a recent back-up? If the answer to both these questions is 'No' then you're probably up a gum tree.

    Good luck, APC

  • RELEVANCY SCORE 2.98

    DB:2.98:Re: Insert Statement As Parameter To A Procedure z3


    Search for Dynamic SQL

    DB:2.98:Re: Insert Statement As Parameter To A Procedure z3

    Hi jeneesh,

    I was doing a bit different, Your code helped me to correct my mistakes.

  • RELEVANCY SCORE 2.97

    DB:2.97:Re: Is "Dual" Table Locked During Transaction If Sql Statement Uses "Dual" 8m


    Why are you asking??

    DB:2.97:Re: Is "Dual" Table Locked During Transaction If Sql Statement Uses "Dual" 8m

    I think you are looking at wrong places for your deadlock problems.
    You need to explain in a bit more detail what each of your X, Y, Z and W statements look like and what they really are doing (updating tables involved in a master-detail relation?).

    Maybe unindexed foreign keys is your problem.

    When you run your individual SQL statements, what kind of locks (and on what objects) do you see on the system?

  • RELEVANCY SCORE 2.97

    DB:2.97:Re: Sql Script as


    Give this a try -- From your original posting, change your sql statement to

    SELECT * FROM
    TABLE_NAME
    WHERE COLUMN_NAME=VALUE

    and try it out

    DB:2.97:Re: Sql Script as

    Hi,
    Can anybody please give me some clues on this issue?
    It is very urgent for me ?
    Thanks for your help,
    Radhika.

  • RELEVANCY SCORE 2.97

    DB:2.97:Problem With Sax Sample. d3


    Hello.
    As nobody has answered my question in the sample code forum, I'll re-post it here...

    I'm learning to use SAX API in order to insert large XML files into database. I'm trying to run sample xdksample_040602.zip (data stream sample using pl/sql and
    SAX). Everything runs fine until I run "ExDocumentTest.sql" file. I get this error:
    Errors for PROCEDURE DOCEXPAND:
    PL/SQL:SQL Statement ignored
    PLS-00385: type mismatch found at 'P_XML' in SELECT...INTO statement

    PL/SQL:SQL Statement ignored
    PLS-00385:type mismatch found at 'P_DTD' in SELECT...INTO statement

    Please I need to solve this problem. Or... where can I find another sample to do what I want? this seems to be the only sample for inserting XML into the database with SAX. I already know how to do it with DOM.

    Eric.

    DB:2.97:Problem With Sax Sample. d3

    I forgot to mention. My database is 8.1.7 on linux redhat 7.1. I'm using XDK for plsql (9.2.0.5.0).

  • RELEVANCY SCORE 2.97

    DB:2.97:Re: Trigger Update aj


    Hi,

    Execute the SELECT statement at SQL Prompt and examine the output and let me know returned value.

    Regards,
    Sailaja

    DB:2.97:Re: Trigger Update aj

    Alright, everything is working beautifully now. The final product is as below. I left the null exception handler for the NO_DATA_FOUND, since I just want to leave the field null if the query returns nothing.
    create or replace
    trigger tr_item_master_hazmat
    before insert or update on t_item_master
    for each row
    declare
    isHazmat char(1);
    begin
    isHazmat := '0';

    select '1'
    into isHazmat
    from v_item_dcl_supplier ora
    inner join spwh_hazmat_dcl haz
    on haz.dept = ora.dept
    and haz.class = ora.class
    and haz.line = ora.line
    where ora.inventory_item_id = :new.oracle_inventory_item_id

    if isHazmat = '1'
    then
    :new.haz_material := '1';
    end if;
    exception
    when no_data_found
    then
    null;
    end tr_item_master_hazmat;Thanks for your help. I've learned something; it makes sense and in retrospect it seems obvious. I've also learned not to discard exceptions, hah.

    Thanks again!

  • RELEVANCY SCORE 2.96

    DB:2.96:Re: Strange Sql Statement Result jk


    Can you provide some sample data ???

    DB:2.96:Re: Strange Sql Statement Result jk

    Hi,

    here is an example with 5 rows, as you don't tell us what you have neither what you want (except the rowcount) there is not much we can do:

    drop table a;
    drop table aa;

    create table a (a number,b varchar2(1));
    create table aa (aa number,bb date);

    insert into a values (722,'A');
    insert into a values (722,'B');
    insert into a values (722,'C');
    insert into a values (722,'D');
    insert into a values (722,'E');

    insert into aa values (722,sysdate);
    insert into aa values (722,sysdate-1);
    insert into aa values (722,sysdate-2);
    insert into aa values (722,sysdate-3);
    insert into aa values (722,sysdate-4);

    select * from
    (select a.*
    ,row_number () over (partition by a order by b) rna
    from a)
    ,(select aa.*
    ,row_number () over (partition by aa order by bb) rnaa
    from aa)
    where a=aa -- your btch_id
    and rna = rnaa -- constructed so the both make a primary key
    /

    regards,. martina

  • RELEVANCY SCORE 2.96

    DB:2.96:Re: Explain Plan For A Sql Statement 93


    You said: "Same sql statement written in another way", it means they are already not the same statement, so you cannot compare explain plan anymore.Nicolas.

    DB:2.96:Re: Explain Plan For A Sql Statement 93

    I have a simple sql statement which has a cost of 122, cardinality 59 and CPU cost 1966777I don't know how the figures are presented to you (what tool / api / query you used) .... but in general when Oracle's standard methods (eg. DBMS_XPLAN.DISPLAY) is used, the CPU Cost is included in the COST (i.e. COST is "I/O Cost" + "CPU Cost").Hemant K Chitale

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Sql Statement Tuning Request ma


    Yes, your DBA should make it possible for you to measure and test how your code performs.

    DB:2.95:Re: Sql Statement Tuning Request ma

    As a first quick test, I often add a ttdynamic_sampling(9)/tt hint just to see what the optimizer will come up with. If it gives a better plan I then try to work out why it's better so that I can either change the query, fix the stats or (last resort) add a hint. The final hint might not need to be ttdynamic_sampling(9)/tt.

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Trace Is Still Getting Generated Even After Disabling The Profile Option Initialization Sql Statement - Custom fc


    Hi Srini and Hussein,Thanks for the reply.Issue is with specific user only. User is logged out of the application. Before logging out we suspect that he closed the browser instead of logout link.This is in Production instance. We have'nt bounced any services.We have verified the profile options for this user and found that value is cleared for profile Initialization SQL Statement - Custom.Please help.ThanksRaghava

    DB:2.95:Re: Trace Is Still Getting Generated Even After Disabling The Profile Option Initialization Sql Statement - Custom fc

    HiThanks for update and sharingPlease dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding*RegardHelios

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Sql Statement za


    Hi,

    Please, format your sql using tags.

    What values takes parameter :p_trans_date?
    :p_bucket_type takes values 1,2 and so on? This parameter is count of months?

    DB:2.95:Re: Sql Statement za

    Maybe the below gives correct date periods for the five buckets of yours ?
    select level bucket,
    add_months(trunc(sysdate),1 - level * :month_span) low_date,
    add_months(trunc(sysdate),1 - (level - 1) * :month_span) - 1 high_date
    from dual
    connect by level = 5Regards

    Etbin

    Edited by: Etbin on 3.10.2011 9:29
    it does not ! Sorry, my bad :(
    select level bucket,
    add_months(trunc(sysdate,'month'),1 - level * :month_span) low_date,
    add_months(trunc(sysdate,'month'),1 - (level - 1) * :month_span) - 1 high_date
    from dual
    connect by level = 5;)

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Selete Statmet Problem as


    No, the semicolon is not a terminator of sql but SQL*Plus.
    I suppose you ran the statement in a third-party tool like TOAD?
    Try to run this select statement in SQL*Plus or remove the semicolon

    Read here what Steven Feuerstein and Bryn Llewellyn write about
    Semi-colons and SQL
    http://apex.oracle.com/pls/otn/f?p=2853:4:3047498772120370::NO::P4_QA_ID:1622

    DB:2.95:Re: Selete Statmet Problem as

    Ah, see that now. There was both a semicolon and a slash character.

  • RELEVANCY SCORE 2.95

    DB:2.95:Re: Inline Select Statement And Sql Optimizatino m8


    You should execute your statements in SQL*Plus and gather the information required as described in the following links...

    When your query takes too long ...
    HOW TO: Post a SQL statement tuning request - template posting

    This is required if you want to determine performance differences.

    DB:2.95:Re: Inline Select Statement And Sql Optimizatino m8


    starts calling his coffee 'optimizatino' from now on and receives wary/suspicious looks from collegues

    I'm with you there! Every Oracle developer should start their day with a warm mug of Optimizatino! :D

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: Implementing Recommendations For Tuning Sql Problem 9j


    select statement only.below one
    And i'm trying in below path oracle Enterprise Manager

    Home-Advisor Central-SQL Advisors-SQL Tuning Advisor-to actvity-actions SQl tunung advisors-Go-submiit
    then i twill give some recommendation -click on implement button there error occuered

    DB:2.94:Re: Implementing Recommendations For Tuning Sql Problem 9j

    OK, because you receive an error while trying to perform a OEM function, it normally makes worthwhile to check for any errors in the oms log files. That's why I asked you to check the log files on the OMS server.

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: Printing Values For Sql Attribues And Cursor Attributes ... xf


    annu_rajput wrote:
    How to print values for following SQL attributes or Cursor attributes ....

    %FOUND
    %NOTFOUND
    %ISOPENThey return BOOLEAN value so you need to use it in a condition statement like

    IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE("FOUND");
    END IF;

    DB:2.94:Re: Printing Values For Sql Attribues And Cursor Attributes ... xf

    annu_rajput wrote:
    How to print values for following SQL attributes or Cursor attributes ....The actual question seems to be, how to convert a Boolean to a String?

    The correct answer is via a modularisation - e.g.
    create or replace function BoolToChar( b boolean ) return varchar2 is
    begin
    return(
    case b
    when TRUE then 'TRUE'
    when FALSE then 'FALSE'
    else
    null
    end
    );
    end;To use it for "printing":
    DBMS_OUTPUT.put_line( 'Cursor Opened: '||BoolToChar(c%IsOpen) );

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: Urgent - How We Call Function From Bi Publisher Sql 8s


    have you tried to call a function through a select statement and it works ??

    DB:2.94:Re: Urgent - How We Call Function From Bi Publisher Sql 8s

    Then it's no surprise that the query don't work. It's possible inside BI Publisher to use a different source for the LOV ... or just rewrite the query against another source like
    select to_char(add_months(trunc(sysdate,'MONTH'),-rownum),'Month - YYYY') display_value, last_day(add_months(trunc(sysdate,'MONTH'),-rownum)) return_value from your_table
    where rownum =12
    ... or build a now object mit values you like as source for your LOV

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: Suggestions For Tuning... 8z


    HOW TO: Post a SQL statement tuning request - template posting

    DB:2.94:Re: Suggestions For Tuning... 8z

    I think there is something wrong from a logical perspective with this query.
    And because of those highly informative table and column names {irony} it is difficult to get a decent understanding of its purpose.

    What bothers me is that the base table a and b are connected to this DISTINCT view on table d only though this one line
    and a.SWM051_M_YR_Y = d.swm045_col0_x And a.SWM051_M_YR_Y is set to 2010. Depending on the differnt values in d.SWM045_COL4_X, maybe you could simply build some different query. Btw. most of the columns including those instr functions are not used. And therefore are just a waste of CPU ressources.

    e.g.
    select
    /*+index(a TSWM051_BASE_NU03)*/
    --d.SWM045_COL4_X,
    MIN (select SWM045_COL4_X from TSWM045_DBSUM2 d
    where a.SWM051_M_YR_Y = d.swm045_col0_x /* = 2010 */
    and a.SWM051_CAUSL_PT_BASE_R = trim(substr(d.SWM045_COL4_X,0,(instr(d.SWM045_COL4_X,'-')-1)))
    )
    as col4_X_min,
    a.SWM051_CAUSL_PT_BASE_R as base,
    b.SWM050_CAUSL_PT_PREF_R as Prefix,
    b.SWM050_CAUSL_PT_SUFX_R as Suffix,
    count(a.SWM051_CLAIM_ID_R) as TOTAL_CLAIMS,
    min(a.SWM051_CLAIM_ID_R)as SAMPLE_CLAIM1,
    max(a.SWM051_CLAIM_ID_R)as SAMPLE_CLAIM2
    from
    TSWM051_C_CSD a,TSWM050_C_W b
    where
    a.SWM051_LAST_UPDATE_S = '01-apr-2010'
    and a.SWM051_LAST_UPDATE_S = '30-apr-2010'
    --and a.SWM051_V_LINE_C = 'BB'
    --and a.SWM051_CAUSL_PT_BASE_R =
    and a.SWM051_M_YR_Y= 2010
    and a.SWM051_SLS_REGION_C = 'EU'
    and a.SWM051_COUNTRY_C = 'BEL'
    and a.SWM060_PROC_STAT_R= 2
    and a.SWM051_CLAIM_ID_R = b.SWM050_CLAIM_ID_R
    and a.SWM051_CAUSL_PT_BASE_R = b.SWM050_CAUSL_PT_BASE_R
    and a.SWM051_M_YR_Y = b.SWM050_M_YR_Y
    group by
    a.SWM051_CAUSL_PT_BASE_R,
    b.SWM050_CAUSL_PT_PREF_R,
    b.SWM050_CAUSL_PT_SUFX_R
    ;Edited by: Sven W. on Jul 6, 2011 12:25 PM -- found another link

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: How-To Change "Case Statement In For Loop" To "Forall Statement" ? 19


    Well, I've read that post and it gives no information on the merge used or the tables/indexes involved.So, essentially, it is meaningless.Also, it was 9i, so an old release.Pure SQL is always faster and more scalable, when properly tuned, than PL/SQL loops/bulk collects.Use MERGE.

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Sql Performance 17


    HOW TO: Post a SQL statement tuning request

    HOW TO: Post a SQL statement tuning request - template posting

    DB:2.93:Re: Sql Performance 17

    Replace the below line
    from
    AND A.BAN_NO NOT IN (SELECT ban_no FROM TEMP_EXCLUSIVE_BANK_LOAD )
    to
    AND NOT EXISTS(SELECT /*+ UNNEST */ NULL FROM TEMP_EXCLUSIVE_BANK_LOAD B WHERE A.BAN_NO = B.BAN_NO) .

    If not it improve your query performance paste explain plan after replacement

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Update Too Slow 1j


    Thread: HOW TO: Post a SQL statement tuning request - template posting
    HOW TO: Post a SQL statement tuning request - template posting

    UPDATE cdm_install
    SET iinst_lot = (SELECT gen_bitvabledata.bi_c40_fld02
    FROM gen_bitvabledata,
    gen_bitinsttable
    WHERE gen_bitvabledata.bi_keys_code =
    gen_bitinsttable.bi_inst_code
    AND gen_bitvabledata.bi_type_code = 'CROWN'
    AND gen_bitvabledata.bi_keys_type = 'i'
    AND gen_bitinsttable.bi_activ_ind = 'y'
    AND gen_bitvabledata.bi_activ_ind = 'y'
    AND cdm_install.install =
    gen_bitinsttable.bi_inst_code)

    DB:2.93:Re: Update Too Slow 1j

    When i see results like this it makes me want to give up and take on needlepoint.

    My query on the full database took about 43 minutes.

    Your suggestion took less than 1 second.

    I've checked the results and it appears to do the job.

    I'm now off to study the code and figure out what is haoppening.

    I have many updates like this to do so this will prove very useful.

    I knew that what i wanted could be done very efficiently but was not able to sort it my self.

    For your interest i've attached the plan

    Plan hash value: 3135032585

    ---------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------------------------
    | 0 | MERGE STATEMENT | | 27994 | 2023K| 31413 (1)| 00:06:17 |
    | 1 | MERGE | CDM_INSTALL | | | | |
    | 2 | VIEW | | | | | |
    | 3 | NESTED LOOPS | | 27994 | 115M| 31413 (1)| 00:06:17 |
    |* 4 | HASH JOIN | | 27994 | 1530K| 3377 (2)| 00:00:41 |
    |* 5 | HASH JOIN | | 27994 | 984K| 2547 (2)| 00:00:31 |
    | 6 | TABLE ACCESS BY INDEX ROWID| GEN_BITVABLEDATA | 17866 | 436K| 1171 (1)| 00:00:15 |
    |* 7 | INDEX RANGE SCAN | GENVABLEDATACOMBINED | 17944 | | 59 (2)| 00:00:01 |
    |* 8 | TABLE ACCESS FULL | GEN_BITINSTTABLE | 177K| 1906K| 1372 (3)| 00:00:17 |
    | 9 | TABLE ACCESS FULL | CDM_INSTALL | 169K| 3316K| 826 (1)| 00:00:10 |
    | 10 | TABLE ACCESS BY USER ROWID | CDM_INSTALL | 1 | 4282 | 1 (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    4 - access("X"."INSTALL"=TO_NUMBER("GEN_BITINSTTABLE"."BI_INST_CODE"))
    5 - access("GEN_BITVABLEDATA"."BI_KEYS_CODE"="GEN_BITINSTTABLE"."BI_INST_CODE")
    7 - access("GEN_BITVABLEDATA"."BI_ACTIV_IND"='y' AND "GEN_BITVABLEDATA"."BI_KEYS_TYPE"='i'
    AND "GEN_BITVABLEDATA"."BI_TYPE_CODE"='CROWN')
    8 - filter("GEN_BITINSTTABLE"."BI_ACTIV_IND"='y')

    Note
    -----
    - dynamic sampling used for this statement

    Many thanks.

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Sql Update Troubles W/ Boolean pp


    Jamie,

    In our case, this error happened in SQL Update statement
    where Forte boolean attribute was mapped to Informix integer column.
    (Forte 2.0.H.0 with Informix 7.10 on HP)

    Forte is able to compile Insert statement with boolean to integer mapping.
    When you run the insert statement, Forte internally translates Boolean
    ('TRUE/FALSE') to integer (1/0).
    But in update statement it gives compile error.

    I had logged the error with forte tech support.

    Workaround : Translate boolean to an integer local variable and
    use that variable with integer column in update statement.
    If forte class is directly mapped to a table, define integer virtual
    attribute for boolean attribute in the class
    and use it in update statement.

    Hope this helps.

    Shirish Kulkarni
    Sage Solutions, Inc.
    Phone: (510) 208-1220
    Email: shirish@sagesoln.com

    ----------
    From: Jamie Anstice j.anstice@regy.canterbury.ac.nz
    To: 'kamranamin@yahoo.com'
    Subject: SQL Update troubles w/ Boolean
    Date: Wednesday, August 06, 1997 10:32 PM

    I'm having trouble with Forte not compiling an SQL UPDATE statement
    containing some Boolean data. SQL INSERTing works fine, but when I
    compile the code below, I get a whole bunch of errors that look like
    "Unable to bind to TOOL variable 'TypeSet'. This usually means a syntaxerror.
    Make sure you have sepcified a column list for the SELECT." This isfollowed by
    a "No match found for invocation of overloaded method SetValue(blah,blah) x lots"

    This makes me wonder if the compiler is getting confused with something
    else (version 2.0.F.2, on NT 4.0).

    Has anyone seen this before, and has a workaround?

    sql update JobDetails
    set JobID = :JobID, type = :t, BothSides = :BothSides, Collate =:Collate,
    Drilling = :Drilling, Fold = :Fold, MachineRequest = :MachineRequest,
    Pages = :Pages, PlusCover = :PlusCover, ProofReq = :ProofReq, Size =:Size,
    Staple = :Staple, Trim = :Trim, TypeSet = :TypeSet
    where JobDetailID = :ID
    on session PrintDB;

    Thanks

    Jamie Anstice
    Programmer/Analyst
    University of Canterbury
    j.anstice@regy.canterbury.ac.nz

    DB:2.93:Re: Sql Update Troubles W/ Boolean pp

    I'm having trouble with Forte not compiling an SQL UPDATE statement
    containing some Boolean data. SQL INSERTing works fine, but when I
    compile the code below, I get a whole bunch of errors that look like
    "Unable to bind to TOOL variable 'TypeSet'. This usually means a syntax error.
    Make sure you have sepcified a column list for the SELECT." This is followed by
    a "No match found for invocation of overloaded method SetValue(blah, blah) x lots"

    This makes me wonder if the compiler is getting confused with something
    else (version 2.0.F.2, on NT 4.0).

    Has anyone seen this before, and has a workaround?

    sql update JobDetails
    set JobID = :JobID, type = :t, BothSides = :BothSides, Collate = :Collate,
    Drilling = :Drilling, Fold = :Fold, MachineRequest = :MachineRequest,
    Pages = :Pages, PlusCover = :PlusCover, ProofReq = :ProofReq, Size = :Size,
    Staple = :Staple, Trim = :Trim, TypeSet = :TypeSet
    where JobDetailID = :ID
    on session PrintDB;

    Thanks

    Jamie Anstice
    Programmer/Analyst
    University of Canterbury
    j.anstice@regy.canterbury.ac.nz

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Write A Query / Pl/Sql Statement For Desired Results ak


    Three strikes you are OUT!

    DB:2.93:Re: Write A Query / Pl/Sql Statement For Desired Results ak

    Hi Skyfox,

    Below is the query you might be looking into

    SQL
    SQL WITH test AS(
    2 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0030A' FSOBNM, 'N' FSA, 'N' FSCHNG, 'N' FSDLT, 'Y' FSIOK, 'N' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    3 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0030A' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    4 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01012' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    5 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01012' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    6 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01013' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    7 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01013' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    8 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    9 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    10 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    11 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01111' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    12 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0115' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    13 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0115' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    14 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P01BDWRD' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    15 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P01BDWRD' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    16 SELECT 1 FSSETY, 'FINAB001' FSUSER, 'P0411' FSOBNM, 'Y' FSA, 'Y' FSCHNG, 'Y' FSDLT, 'Y' FSIOK, 'Y' FSICPY, 'Y' FSATN1 FROM DUAL UNION ALL
    17 SELECT 1 FSSETY, 'DSTEIN' FSUSER, 'P0411' FSOBNM, 'N' FSA, 'N' FSCHNG, 'N' FSDLT, 'Y' FSIOK, 'N' FSICPY, 'Y' FSATN1 FROM DUAL)
    18 SELECT *
    19 FROM test a
    20 WHERE EXISTS (SELECT 1 FROM test
    21 WHERE fsobnm = a.fsobnm
    22 AND fsuser != a.fsuser
    23 AND (fsa != a.fsa OR fschng != a.fschng OR fsdlt != a.fsdlt OR fsicpy != a.fsicpy))
    24 /

    FSSETY FSUSER FSOBNM FSA FSCHNG FSDLT FSIOK FSICPY FSATN1
    ---------- -------- -------- --- ------ ----- ----- ------ ------
    1 DSTEIN P0030A N N N Y N Y
    1 FINAB001 P0030A Y Y Y Y Y Y
    1 FINAB001 P0411 Y Y Y Y Y Y
    1 DSTEIN P0411 N N N Y N Y

    SQL

  • RELEVANCY SCORE 2.93

    DB:2.93:Wrong Information In V$Session_Wait kp


    I submitted a long-running SQL statement in session 1 (SQL*Plus session) and
    then selected V$SESSION_WAIT in session 2(SQL*PLus session). I found that
    V$SESSION_WAIT for session 1 returned "SQL*Net message to client" during the
    SQL execution and STATUS of V$SESSION for session 1 is ACTIVE.

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: What Is The Purpose Of Using In 'Execute Immediate' x7


    Two major benefits of USING in execute immediate i.e. using parameters1) SQL statement need not to hard parsed by the database every time [performance benefit].2) more important, to avoid "SQL injection" and ensuring that the variable data will behave correctly as part of the statement

    DB:2.93:Re: What Is The Purpose Of Using In 'Execute Immediate' x7

    Don't judge so easy when you don't have all the information. Your answer was NOT responsive to the question OP asked:Just want to understand what is the purpose of USING in 'EXECUTE IMMEDIATE'.The question is about the USING clause - not the general use of dynamic sql.

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Help Needed With Select Statement! 77


    Plain SQL please...

    Thanks,
    Rick.

    Message was edited by:
    Rick1971

    DB:2.93:Re: Help Needed With Select Statement! 77

    Hello,

    You can use the package DBMS_ROWID to get a range of ROWIDS.

    You can then use these ROWIDs to fetch 1000 rows.

    I learned this from here: http://asktom.oracle.com/pls/ask/f?p=4950:8:4163304088387031291::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:7638743750722.

    You can take a look there.

  • RELEVANCY SCORE 2.93

    DB:2.93:Sql Tunning On Em 1p


    when I looked at top activity, I found that there is a SQL statement that took a long time, I then use SQL tuning advisor to tune the SQL it found out three things: tables/indexes are stale, EM recommend to build profile and lastly it rebuild the SQL statement. I then click on "implement all" and I would assume that EM will update stats, build the profile and rewrite the SQL.

    My question is I want to see the profile and rewritten SQL statement that EM performed, how can I see that info.

    Thanks,

    DB:2.93:Sql Tunning On Em 1p

    If you select the database performance tab you will find the SQL Tuning Sets link under Additional Monitoring Links.

    Here you will find the created Tuning Sets.

    Regards
    Rob

  • RELEVANCY SCORE 2.93

    DB:2.93:Re: Defined Variable And Substitution Variables k1


    The database never sees substitution variables. That's something that happens entirely within SQL Plus.

    SQL Plus resolves them before sending the sql statement (or the pl/sql block) to the server.

    (a bind variable however, is another matter)

    DB:2.93:Re: Defined Variable And Substitution Variables k1

    Hi,

    What you're calling a "defined variable" is nothing but a substitution variable.
    DEFINE p_grade = ais just one way of setting the value of the substitution variable p_grade.
    When you later use that variable, for example
    WHERE job_id = p_gradethere is no way of telling how p_grade got its value (if it has one).

    Avoid using the same name for two different types of variables.
    For example:
    DECLARE
    p_grade VARCHAR2(10); -- Local variable declared
    v_name VARCHAR2(10);
    BEGIN
    SELECT last_name
    INTO v_name
    FROM employees
    WHERE job_id = p_grade; -- Substitution variable used
    END;In the second line, you are creating a local VARCHAR2 variable called p_grade that is never used. It has nothing to do with the substitution variable p_grade (unless the value of that substitution variable happens to be p_grade,
    DEFINE p_grade = p_grade)
    If you do use the same name for differnt types of variables, SQL*Plus may keep them straight, but no one else (including you) will.

  • RELEVANCY SCORE 2.92

    DB:2.92:Re: Execute Stored Procedure Cause Invalid Sql Statement Error p1


    use call instead.

    Eric

    DB:2.92:Re: Execute Stored Procedure Cause Invalid Sql Statement Error p1

    Hence you're not working in PL/SQL context (that would be BEGIN/END blocks).
    Call is not officially supported SQL*Plus in sqldev, but Exec is. That would explain the unexpected results.
    Nevertheless, it would be nice if someone logged this issue. Alternatively you could request it at the Exchange.

    Regards,
    K.

  • RELEVANCY SCORE 2.92

    DB:2.92:Re: Ora-00911: Invalid Character In Dbms_Sqltune.Report_Tuning_Task 11.2.0.1 jc



    'from mips_mdm.mop_lookup l order by l.id;';

    Try removing the ";" semi-colon from the sql statement (quoted string) and re-execute it.

    Thanks

    Raj

  • RELEVANCY SCORE 2.92

    DB:2.92:Re: Bind Variables In Pl/Sql aj


    No, they are not "the same" the execute immediate statement will require more parsing,

    Why? How can an execute immediate, using the same bind variables and same SQL statement as an implicit or explicit PL/SQL cursor, require more parsing?

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Loading A Blob Record zf


    That was an SQL SELECT statement. To insert data, use an SQL INSERT statement.

    DB:2.91:Re: Loading A Blob Record zf

    PreparedStatement pstmt = con.prepareStatement("insert into tablename (columnname) values ( ? )");
    pstmt.setBinaryStream(1, in, length);
    pstmt.executeUpdate();Again, you should get in the habit of using only PreparedStatements.
    They have many advantages over plain Statements.

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Sql Query? m1


    sorry,I can't understand this statement and 1,2,3,4,5, is the column's sequence in the table?

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Tigger Issue pc


    Can someone plesae advise. open up sqlplus experiment until you produce a valid correct SELECT statement.
    The 1 you post has invalid syntax.

    SQL Reference manual can be found at http://tahiti.oracle.com

    DB:2.91:Re: Tigger Issue pc

    Thanks everyone... and Apologies for asking again and again..

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Create Function With Substr.... s9


    Hi Bjoern Rost,

    Thanks for your reply.

    I am getting this error. Please correct the code and post here.

    LINE/COL ERROR
    -------- ----------------------------------------------------------
    4/1 PL/SQL: SQL Statement ignored
    6/10 PL/SQL: ORA-00933: SQL command not properly ended

    Thanks and Regards,
    Muthu

    DB:2.91:Re: Create Function With Substr.... s9

    Hi Muthu,
    It's simply: inside your function you use following query:

    select FILE_NAME
    into inv_no
    from fnd_lobs If table fnd_lobs have more than one row you always get ORA-01422. Probably you miss some where clause here. When you use Select ... into clause you have to be sure that your query return exactly 1 row, otherwise you get ORA-01422 if there is more rows or ORA-01403 if no rows matching your criteria. You can prevent this catching such exception like

    begin
    select FILE_NAME
    into inv_no
    from fnd_lobs
    exception
    When TOO_MANY_ROWS then /*ORA-01422*/
    null; --some code here
    When NO_DATA_FOUND then /*ORA-01403*/
    null; --some code here
    end; If you want to read more rows at once you have to use bulk collect into clause and some kind of collection as variable
    Regards
    kikolus

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Sql Statement sp


    Start with "how do I write" first, please.
    {message:id=9360002}

    DB:2.91:Re: Sql Statement sp

    Wow!!! thanks guys, I see the secret.

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Why Decode Can Only Be Used In Sql Statement cj


    Because that's just the way it is (for now).PLS-00204: function or pseudo-column 'string' may be used inside a SQL statement only Cause: A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (LEVEL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.Action: Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace bonus := DECODE(rating, 1, 5000, 2, 2500, ...); with the following statement: SELECT DECODE(rating, 1, 5000, 2, 2500, ...) INTO bonus FROM dual;http://docs.oracle.com/cd/E11882_01/server.112/e17766/pcmus.htm#sthref18297

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Balanced Insert Statement fm


    You can do it all in one SQL insert statement.

    DB:2.91:Re: Balanced Insert Statement fm

    such questions should be posted in SQL and PL/SQL forum.

    You need rank persons by count of assignments then assign tickets according to rank.
    in my example tickets also ranked, but it is only to assign row number.
    There also analytic functions dense_rank and row_number that you can use here, you will have to experiment what will work better in your case.

    create table person(personid number primary key, name varchar2(30));
    create table ticket(ticketid number primary key, name varchar2(30));
    create table assignment(ticketid number, personid number, primary key (ticketid, personid) );

    insert into person values (1,'A');
    insert into person values (2,'B');
    insert into person values (3,'C');

    insert into ticket values (1,'a');
    insert into ticket values (2,'b');
    insert into ticket values (3,'c');

    insert into assignment values (1,1);
    insert into assignment values (2,1);

    commit;

    with
    P as (
    select p.personid, count(a.personid) cnt from person p
    left outer join assignment a on a.personid=p.personid
    where a.personid is null
    group by p.personid),
    PR as (select personid, rank() over (order by cnt,personid) R from P),
    T as (
    select t.ticketid, count(a.ticketid) cnt from ticket t
    left outer join assignment a on a.ticketid=t.ticketid
    where a.ticketid is null
    group by t.ticketid),
    TR as (select ticketid, rank() over (order by cnt,ticketid) R from T)
    select * from PR join TR on PR.R=TR.R;

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Union And Union All Hangs The Session 7a


    and make the choice clear in a pl/sql code.Yes, because pl/sql code is always so much clearer than a select statement.

    ;-)

    DB:2.91:Re: Union And Union All Hangs The Session 7a

    Are you sure that pl/sql will be harder to maintain?

    In this case, yes.

    Did you even read the query of the opening post?

    This query is nothing compared to many of the queries developers bring to me to fix and tune and streamline. Wait till you see a query that covers 4 pages of an A4 printout, littered with in-line selects and joins... :-)

    I have yet to find a compelling reason to move such a query into a pipelined table function to make it "better".

    use pl/sql to avoid redundant calls of query

    How do we know who or what the caller is in this case? You go and handle and suppress SQL exceptions (no data found, etc) and you may just break the caller as it is making an SQL call (not PL/SQL!!) to the pipeline. It is expecting SQL behaviour - which has now been modded in the PL/SQL pipeline function.

    and also returning a little number of records (200)

    That is no consideration at all. None. Designing code around how many rows are expected to be returned, is very dangerous and very risky. There is nothing sensible about such an approach.

    i say: try to write the logic in pl/sql.. is so a blasphemy ?

    Of course not. But the rule is
    1. Do it in SQL
    2. Only when SQL truly cannot do it, do it in PL/SQL

    And it is not just my rule - it is a general rule first coined by Oracle experts such as Tom Kyte (asktom.oracle.com). And yes, it is a rule if one intends to write effective and efficient Oracle applications.

    Show me where SQL fails in this specific UNION case. I do not see a SQL failure. I see a CBO issue - and that can be solved with numerous standard Oracle tools, before one has to resort to a drastic measure of using a pipelined table to fix the problem.

    I'm not against PL/SQL (heck, I likely write more PL/SQL a day and have more PL/SQL code implemented in production instances here than most). But I'm against
    - solutions where the problem is not known and identified
    - solutions that do not conform to fundamental software engineering principles

    Why? Because I have burnt myself more than once by not adhering to these through the last 2 to 3 decades. Failure is the greatest teacher of experience. :-)

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Using Sequences With Record Type In Plsql dd


    Your code is an example of slow by slow processing as you have replaced pure SQL by PL/SQL

    Drop the cursor and replace the statement
    by insert ... select

    IN that statement you can use a sequence.
    In the current drama code you can't.

    -----------
    Sybrand Bakker
    Senior Oracle DBA

    DB:2.91:Re: Using Sequences With Record Type In Plsql dd

    Thanks for the information i will read up further and investigate. But, much appreciated for the helpful information.

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Execute Sql Files With Sqlplus In Program, How I Know Whether Error Hap ms


    whenever a sql statement failed, sqlplus will exit and return errors

    DB:2.91:Re: Execute Sql Files With Sqlplus In Program, How I Know Whether Error Hap ms

    interesting, when starting a script that have a $ in it and when the xx variable cannot resolve, there is no OS error. Maybe worth opening a bug if you have a business case that requires that functionality

    $ set | grep xxx
    $ sqlplus /nolog
    SQL whenever oserror exit -1
    SQL start $xxx
    SP2-0310: unable to open file "$xxx"
    SQLNote the .sql suffix is not added!

    However, if the xxx variable resolves, it generates an os error

    $ export xxx=yyy
    $ sqlplus /nolog
    SQL whenever oserror exit -1
    SQL start $xxx
    O/S Message: No such file or directory
    $ if you are looking for a filename with a dollar in it, you can you ESCCHAR in 11g

    $ sqlplus /nolog
    SQL whenever oserror exit -1
    SQL set escchar $
    SQL start $xxx
    O/S Message: No such file or directory
    $

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Import Problem sa


    I generate the indexfile whit the imp application and found my indexOk, so now if you take that SQL statement and run it at SQL*Plus prompt on the target database, does it execute successfully? did the index got creted?

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Pls-00103: Encountered The Symbol "Alter" kc


    Frank Kulash wrote:
    Hi,

    ALTER TABLE is not a PL/SQL statement; it's a SQL statement.
    Use EXECUTE IMMEDIATE to run SQL statements in PL/SQL.{noformat} winces {noformat} .... wouldn't it be better to differentiate between DML and DDL, rather than a blanket "Use EXECUTE IMMEDIATE to run SQL statements in PL/SQL"? {noformat}*foresees a lot of DML statements run dynamically and winces again*{noformat}

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Need A Help On Update Statement dk


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ

    DB:2.91:Re: Need A Help On Update Statement dk

    Hi Arild,
    Thank you very much it helped me a lot.

    Thanks Regards,
    Rakesh

  • RELEVANCY SCORE 2.91

    DB:2.91:Re: Sql*Plus Vs. Pl/Sql 3c


    -a,

    I see, your mistaken SQL*PLUS with SQL.
    Like said, SQL*PLUS is a tool used to connect to database so you can run SQL statement or call PL/SQL blocks or procedures/functions etc.

    The key difference between SQL and PL/SQL is the PL. PL/SQL is procedural language. The PL/SQL language is tightly integrated with SQL, it's an extension of SQL. Because with SQL statement you can only do one task at a time, either SELECT or INSERT or UPDATE etc.

    Check more information here,
    http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref72

    DB:2.91:Re: Sql*Plus Vs. Pl/Sql 3c

    Hello pahlavan tafti , ahmad. Thanks a lot buddy. You folks have now taught me almost everything i wanted to learn about SQL+, PL/SQL :-)
    good day,
    -a