• RELEVANCY SCORE 4.45

    DB:4.45:Dynamic Sql - Urgent s7




    Can Dynamic SQL be built into Discoverer? If so, which versions and how or where can it be built.

    DB:4.45:Dynamic Sql - Urgent s7

    Can Dynamic SQL be built into Discoverer? If so, which versions and how or where can it be built.

  • RELEVANCY SCORE 3.76

    DB:3.76:Re: Gathering Schema Statistics From Form Builder sp




    Hello,

    Ther FORMS_DDL() built-in has been created for this purpose

    FORMS_DDL Built-in
    Description : Issues dynamic SQL statements at runtime, including server-side PL/SQL and DDL.

    Francois

    DB:3.76:Re: Gathering Schema Statistics From Form Builder sp

    And in Forms/DB 10g you can use the q notation and you will only write single quotes not double quotes.

    using double quotes

    DECLARE

    V_STRING VARCHAR2(30) := 'I''m a string, you''re a string.';

    BEGIN

    MESSAGE(V_STRING);
    MESSAGE(' ', NO_ACKNOWLEDGE);

    END;using q notation

    DECLARE

    V_STRING VARCHAR2(30) := q'!I'm a string, you're a string.!';

    BEGIN

    MESSAGE(V_STRING);
    MESSAGE(' ', NO_ACKNOWLEDGE);

    END;Tony

  • RELEVANCY SCORE 3.75

    DB:3.75:What Is Dynamic Sql In Oracle? k7




    hi ,

    What is dynamic sql in oracle?

    regards
    P Prakash

    DB:3.75:What Is Dynamic Sql In Oracle? k7

    What is dynamic sql in oracle?Intro:
    http://www.oracle-base.com/articles/8i/NativeDynamicSQL.php

    And it is documented, for what it is worth:

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#sthref1056
    http://www.oracle.com/pls/db102/ranked?word=dynamic+SQLintro=1

    links found by doing a simple quick search from: http://www.oracle.com/pls/db102/homepage

  • RELEVANCY SCORE 3.70

    DB:3.70:Dynamic Sql 7x


    Can anybody tell me everything about dynamic sql....

    Thanks a lot...............

  • RELEVANCY SCORE 3.66

    DB:3.66:Samples For Dynamic Java Connectors 9k


    Do anyone have hands on lab for designing dynamic java connectors?

    Regards,

    Antony
    E900/SQL 2008

    DB:3.66:Samples For Dynamic Java Connectors 9k

    Do anyone have hands on lab for designing dynamic java connectors?

    Regards,

    Antony
    E900/SQL 2008

  • RELEVANCY SCORE 3.38

    DB:3.38:Re: Help With Dynamic Sql Cursor Loop j9


    don't commit inside the loop

    DB:3.38:Re: Help With Dynamic Sql Cursor Loop j9

    Here's an expanded view of the code. Sorry I'm having trouble with this forum server, it keeps wanting to freeze in the middle of posting.

    Wes

    ------------------------

    generic_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(generic_cursor, 'SELECT t$cuno, t$nama, t$seak, t$namc, t$namd, t$pstc, t$telp, t$tefx, ' ||
    't$ccty, t$sndr, t$clan, t$creg, t$fovn, ' ||
    't$cpay, t$cdec, t$back, t$scom, t$cfrw, t$prio, t$ccur, t$geoc$l, t$cbrn ' ||
    'FROM ttccom010030 ' ||
    'WHERE RTRIM(t$cuno) = ' || custno,dbms_sql.native);

    -- Define the columns in generic_cursor
    dbms_sql.define_column(generic_cursor,1,ocustomer_id,6);
    dbms_sql.define_column(generic_cursor,2,ocust_name,35);
    dbms_sql.define_column(generic_cursor,3,ocust_short_name,16);
    dbms_sql.define_column(generic_cursor,4,oaddress_1,30);
    dbms_sql.define_column(generic_cursor,5,oaddress_2,30);
    dbms_sql.define_column(generic_cursor,6,ozip_code,10);
    dbms_sql.define_column(generic_cursor,7,ophone,15);
    dbms_sql.define_column(generic_cursor,8,ofax, 15);
    dbms_sql.define_column(generic_cursor,9,ovat_country_cd,3);
    dbms_sql.define_column(generic_cursor,10,ocust_type);
    dbms_sql.define_column(generic_cursor,11,olanguage_id,3);
    dbms_sql.define_column(generic_cursor,12,otcreg,3);
    dbms_sql.define_column(generic_cursor,13,otax_no,20);
    dbms_sql.define_column(generic_cursor,14,opayment_terms_cd,3);
    dbms_sql.define_column(generic_cursor,15,ofreight_type_cd,3);
    dbms_sql.define_column(generic_cursor,16,obackorder_accepted);
    dbms_sql.define_column(generic_cursor,17,oship_complete);
    dbms_sql.define_column(generic_cursor,18,ofreight_carrier1_cd,4);
    dbms_sql.define_column(generic_cursor,19,opriority_cd);
    dbms_sql.define_column(generic_cursor,20,ocurrency_lookup_id,3);
    dbms_sql.define_column(generic_cursor,21,ogeo_cd,10);
    dbms_sql.define_column(generic_cursor,22,otcbrn,6);

    k := dbms_sql.EXECUTE(generic_cursor);

    LOOP
    EXIT WHEN dbms_sql.fetch_rows(generic_cursor) = '0';

    -- Bind values to the variables
    dbms_sql.column_value(generic_cursor,1,ocustomer_id);
    dbms_sql.column_value(generic_cursor,2,ocust_name);
    dbms_sql.column_value(generic_cursor,3,ocust_short_name);
    dbms_sql.column_value(generic_cursor,4,oaddress_1);
    dbms_sql.column_value(generic_cursor,5,oaddress_2);
    dbms_sql.column_value(generic_cursor,6,ozip_code);
    dbms_sql.column_value(generic_cursor,7,ophone);
    dbms_sql.column_value(generic_cursor,8,ofax);
    dbms_sql.column_value(generic_cursor,9,ovat_country_cd);
    dbms_sql.column_value(generic_cursor,10,ocust_type);
    dbms_sql.column_value(generic_cursor,11,olanguage_id);
    dbms_sql.column_value(generic_cursor,12,otcreg);
    dbms_sql.column_value(generic_cursor,13,otax_no);
    dbms_sql.column_value(generic_cursor,14,opayment_terms_cd);
    dbms_sql.column_value(generic_cursor,15,ofreight_type_cd);
    dbms_sql.column_value(generic_cursor,16,obackorder_accepted);
    dbms_sql.column_value(generic_cursor,17,oship_complete);
    dbms_sql.column_value(generic_cursor,18,ofreight_carrier1_cd);
    dbms_sql.column_value(generic_cursor,19,opriority_cd);
    dbms_sql.column_value(generic_cursor,20,ocurrency_lookup_id);
    dbms_sql.column_value(generic_cursor,21,ogeo_cd);
    dbms_sql.column_value(generic_cursor,22,otcbrn);

    -- Process the data to fit the customer master formats
    v_customer_id := trim(ocustomer_id);
    v_cust_name := RTRIM(ocust_name);
    v_cust_short_name := RTRIM(ocust_short_name);
    v_address_1 := RTRIM(oaddress_1);
    v_address_2 := RTRIM(oaddress_2);
    v_zip_code := RTRIM(ozip_code);
    v_phone := RTRIM(ophone);
    v_fax := RTRIM(ofax);
    v_vat_country_cd := RTRIM(ovat_country_cd);
    v_cust_type := ocust_type;
    v_language_id := RTRIM(olanguage_id);
    -- Check for correct industry lookup field and pull correct field (WDB)
    IF industry_lookup_field = 't$creg' THEN
    v_industry_lookup_id := SUBSTR(RTRIM(otcreg),1,2);
    ELSE
    v_industry_lookup_id := SUBSTR(RTRIM(otcbrn),1,2);
    END IF;

    v_tax_no := RTRIM(otax_no);
    v_payment_terms_cd := RTRIM(opayment_terms_cd);
    v_freight_type_cd := RTRIM(ofreight_type_cd);
    v_backorder_accepted := RTRIM(obackorder_accepted);
    v_ship_complete := oship_complete;
    v_freight_carrier1_cd := RTRIM(ofreight_carrier1_cd);
    v_priority_cd := opriority_cd;
    v_currency_lookup_id := ocurrency_lookup_id;
    v_geo_cd := RTRIM(ogeo_cd);

    -- Get customer master data (WDB)

    OPEN cm_data;
    FETCH cm_data INTO cmr;
    IF cm_data%NOTFOUND THEN
    j:= Daily_Order_Extract_Cust(baan_customer_company, v_customer_id);
    ELSE
    IF (v_cust_name cmr.cust_name OR v_cust_short_name cmr.cust_short_name OR
    v_address_1 cmr.address_1 OR v_address_2 cmr.address_2 OR
    v_zip_code cmr.zip_code OR v_phone cmr.phone OR v_fax cmr.fax OR
    v_vat_country_cd cmr.vat_country_cd OR v_cust_type cmr.cust_type OR
    v_language_id cmr.language_id OR v_industry_lookup_id cmr.industry_lookup_id OR
    v_tax_no cmr.tax_no OR v_payment_terms_cd cmr.payment_terms_cd OR
    v_freight_type_cd cmr.freight_type_cd OR v_backorder_accepted cmr.backorder_accepted OR
    v_ship_complete cmr.ship_complete OR v_freight_carrier1_cd cmr.freight_carrier1_cd OR
    v_priority_cd cmr.priority_cd OR v_currency_lookup_id cmr.currency_lookup_id OR
    v_geo_cd cmr.geo_cd) THEN
    j:= Daily_Order_Extract_Cust(baan_customer_company,v_customer_id);
    END IF;

    END IF;

    CLOSE cm_data;

    -- Commit every 500 records
    i:= i+1;
    --IF MOD(i, 500) = 0 THEN
    --COMMIT;
    --END IF;
    -- k := dbms_sql.fetch_rows(generic_cursor);
    END LOOP;
    COMMIT;
    dbms_sql.close_cursor(generic_cursor);
    END;

  • RELEVANCY SCORE 3.37

    DB:3.37:Pl/Sql Xml Architecture 8a


    Hi,
    I am involved in archiecting a system with heavy xml which need to be parsed and update/insert 100-200 tables. The database is preset. So we do not have flexibility there. What would be a best architechture.

    My thoughts

    -- create custom tables to store xpaths- dbcolumn references so pl/sql can be reused and more dynamic sql can be built.
    -- Exclusive use of xmltype.extract function to get the data out.
    -- a screen to mainain this table and accomodate any changes in the future.

    Any thoughts are welcome.

    DB:3.37:Pl/Sql Xml Architecture 8a

    Hi,
    I am involved in archiecting a system with heavy xml which need to be parsed and update/insert 100-200 tables. The database is preset. So we do not have flexibility there. What would be a best architechture.

    My thoughts

    -- create custom tables to store xpaths- dbcolumn references so pl/sql can be reused and more dynamic sql can be built.
    -- Exclusive use of xmltype.extract function to get the data out.
    -- a screen to mainain this table and accomodate any changes in the future.

    Any thoughts are welcome.

  • RELEVANCY SCORE 3.30

    DB:3.30:Native Dynamic Sql 79


    can any one give me links to study dynamic sql and native dynamic sql
    would be more help if you put some brief information also

    Thanks,
    aak

  • RELEVANCY SCORE 3.25

    DB:3.25:After Parameter Form Issue - Oracle Reports 10g j3


    Hi All,

    I have compiled an .rdf report to an .rep file and when
    I run this file I get the "REP-1247: Report contains uncompiled PL/SQL" message.

    The issue is I have built the report with dynamic XML customizations in the after parameter form trigger. When I remove this piece of code from the trigger both files .rdf and .rep works fine.
    Notice that the .rdf file works fine with the after parameter form XML customization.

    Please, can enyone advice me how can I have a .rep file that contains dynamic XML customization (using the srw.add_definition).

    Thank you in advance.

    DB:3.25:After Parameter Form Issue - Oracle Reports 10g j3

    Hi All,

    I have compiled an .rdf report to an .rep file and when
    I run this file I get the "REP-1247: Report contains uncompiled PL/SQL" message.

    The issue is I have built the report with dynamic XML customizations in the after parameter form trigger. When I remove this piece of code from the trigger both files .rdf and .rep works fine.
    Notice that the .rdf file works fine with the after parameter form XML customization.

    Please, can enyone advice me how can I have a .rep file that contains dynamic XML customization (using the srw.add_definition).

    Thank you in advance.

  • RELEVANCY SCORE 3.25

    DB:3.25:Dynamic Sql 1c



    Hi,

    I am new to dynamic SQL. I want to know the concepts of Dynamic SQL. Can anyone explain me abt it in detail.

    Please do the needful,

    Regards,

    Arunsri

    DB:3.25:Dynamic Sql 1c


    Hi,

    data:flds(30) value 'MATNR',
    table(30) value 'MARA',
    condition(90) value 'ERNAM = ''ABC'''.
    data:value(30).
    select single (flds) from (table) into value where (condition).
    write:/ value.

  • RELEVANCY SCORE 3.22

    DB:3.22:Re: Why Dynamic Sql Is Slower Than Static Sql px


    Dynamic sql won't be slower. If you can do without a dynamic sql, you can still use a static one instead.

    DB:3.22:Re: Why Dynamic Sql Is Slower Than Static Sql px

    Because when you are building the query using dynamic sql additional processing is required.I think it's too general comment.

    What do you mean by additional processing?

    It could mean
    - Oracle implemented dynamic sql in inefficient way. lengthy code path, more memory, inefficient code, ...
    - Or, Oracle should do something more to process dynamic sql(more latch and pin acquisitions in my simple test case).

    Both would be correct, but the first thing you should do is to analyze the information like stats, waits, latches, mutex, .....

    Just saying it's heavy by nature isn't enough.

    ================================
    Dion Cho - Oracle Performance Storyteller

    http://dioncho.wordpress.com (english)
    http://ukja.tistory.com (korean)
    ================================

    Edited by: Dion_Cho on Feb 26, 2009 8:10 PM
    Typo...

  • RELEVANCY SCORE 3.21

    DB:3.21:Stored Procedure Using Output Variable To Run A Query Made With Dynamic Sql mx


    I'm trying to create understand the output parameter syntax of SQL Server but I find it very confusing, and reviewing some of the examples that I've seen online haven't done a whole lot to ameliorate that situation, so instead of banging my head against
    the wall I've decided to ask the experts.
    The dynamic SQL in my query does a variable number of joins on the same table to see if the number of person IDs that have code a, b, c and d etc. Because the number of joins is based on a variable I built the string using dynamic SQL. Eventually I
    would like to have each of the where conditions variable too, but that's a little much for the moment.
    I would like to create a stored procedure that runs the query that it produced by the dynamic SQL. I've tested the dynamic part and it works, but what I can't get it the query to run. In playing around I made the stored procedure print out the
    query, but not run it. Am I anywhere in the ballpark?

    alter proc GetMultipleComorbidQuery
    (
    @sqlquery nvarchar(1000) output
    )
    as

    declare
    @x int
    ,@y int,
    @sql nvarchar(1000)

    select
    @x=1
    ,@y=5
    ,@sql =
    'select distinct x.patid
    from
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like ''123%''
    ) as x'CAST(@x as CHAR(1))
    while @x @y
    begin;
    set @sql=@sql
    '
    inner join
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like ''456%''
    ) x'CAST(@x1 as CHAR(1))' on x'CAST(@x1 as CHAR(1))'.patid=x1.patid'
    set @x=@x1
    end;
    set @sql = @sqlquery
    print @sql
    go
    exec sp_executesql GetMultipleComorbidQuery @sqlquery out

    The error that I get is:
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '@sqlquery'.

    I got frustrated at the end and started trying everything if syntax looks like it's nowhere in the same universe

    DB:3.21:Stored Procedure Using Output Variable To Run A Query Made With Dynamic Sql mx

    Hello,
    Following example show @Cnt
    @YearMonth are output variable in store procedure . We are used result of this two variable in other query.
    ALTER procedure [dbo].[sp_UpdLoanData3]
    (
    @_DealName varchar(4000),

    @_yearmo int
    )
    as
    begin
    DECLARE @Cnt int
    declare @temptbl table ([deal Name] varchar(50), [Trustee Loan Id] varchar(50),[collateral Balance] varchar(50),[year mo] int)

    --Split , Separated Value Insert Into table @temptable
    declare @temptable table (DealName varchar(20),YMonth int)
    Declare @Xml AS XML
    SET @Xml = cast(('A'replace(@_DealName,',','/AA')'/A') AS XML)
    INSERT INTO @temptable SELECT A.value('.', 'varchar(max)') as [Column],@_yearmo FROM @Xml.nodes('A') AS FN(A)
    --End Of Separated Value

    DECLARE @DealName Varchar(20)
    DECLARE @YearMonth Varchar(6)
    DECLARE CursorName CURSOR FAST_FORWARD FOR SELECT DealName,YMonth FROM @temptable
    OPEN CursorName
    FETCH NEXT FROM CursorName INTO @DealName, @YearMonth
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --sp_UpdLoanData3 '''BACM 2000-1'',''BACM 2007-3''',201210
    declare @sql nvarchar(max)
    select @sql = N'SELECT @Cnt=Count([YEAR mo]) FROM UpdateLoanData WHERE [Year Mo] = ''' @YearMonth ''' AND [Deal Name] = ' @DealName ''
    exec sp_executesql @sql, N'@Cnt sysname OUTPUT', @Cnt OUTPUT
    if(@Cnt=0)

    BEGIN
    select @sql = N'SELECT @YearMonth=MAX([YEAR mo]) FROM UpdateLoanData WHERE [Deal Name] = ' @DealName ''
    exec sp_executesql @sql, N'@YearMonth sysname OUTPUT', @YearMonth OUTPUT
    insert into @temptbl
    EXEC(N'select [deal Name], [Trustee Loan Id],[collateral Balance],[year mo] from [Shobhit].[dbo].[UpdateLoanData] where [Deal Name] = ' @DealName ' and [Year Mo] = '
    @YearMonth '')
    END
    else
    BEGIN
    insert into @temptbl
    EXEC(N'select [deal Name], [Trustee Loan Id],[collateral Balance],[year mo] from [Shobhit].[dbo].[UpdateLoanData] where [Deal Name] = ' @DealName ' and [Year Mo] = ' @YearMonth '')
    END
    FETCH NEXT FROM CursorName
    INTO @DealName,@YearMonth
    END
    CLOSE CursorName
    DEALLOCATE CursorName
    SELECT * FROM @temptbl

    end

  • RELEVANCY SCORE 3.19

    DB:3.19:Forms_Ddl - Auto-Commit j1


    I need to use the forms_ddl built-in because of some dynamic sql that I have. The problem I am finding is with the commit that it issues after execution, is there a way to disable this commit? It is causing a procedure I wrote to work incorrectly..

    Thanks

    Sean

    DB:3.19:Forms_Ddl - Auto-Commit j1

    You may put your dynamic SQL in some stored proc, where you'll have to use dbms_sql.
    Still, how dynamic is the SQL you use? If it works on different tables, depending on certain conditions, then, use dbms_sql in some stored proc.
    If it works on the same table, then you may use plain SQL with bind variables, conveniently adapted to what you need.
    I sometimes use forms_ddl('commit;') or forms_ddl('rollback;') to commit or rollback some operations made behind the scenes (with insert, update or delete) without affecting the data on the form.
    null

  • RELEVANCY SCORE 3.19

    DB:3.19:Unable To Create Procedure With Dynamic Sql Statements 7z


    Hi,
    I am having problem in creating the procedure with dynamic sql statements. Here is the sample code.

    CREATE or replace PROCEDURE select_table (table_name IN VARCHAR2) AS
    cid INTEGER;
    BEGIN
    /* Open new cursor and return cursor ID. */
    cid := DBMS_SQL.OPEN_CURSOR;
    /* Parse and immediately execute dynamic SQL statement built by
    concatenating table name to SELECT TABLE command. */
    DBMS_SQL.PARSE(cid, 'SELECT lExceptionID FROM '#0124; #0124;table_name#0124; #0124;' where lExceptionID = 1', dbms_sql);
    /* Close cursor. */
    DBMS_SQL.CLOSE_CURSOR(cid);
    EXCEPTION
    /* If an exception is raised, close cursor before exiting. */
    WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cid);
    RAISE; -- reraise the exception
    END select_table;
    /
    SQL@dbmssql.sql
    Errors for PROCEDURE SELECT_TABLE:

    LINE/COL ERROR
    -------- --------------------------------------------------------
    8/4 PL/SQL: Statement ignored
    8/92 PLS-00226: package 'DBMS_SQL' used as variable reference

    I would appreciate if someone can help me.

    DB:3.19:Unable To Create Procedure With Dynamic Sql Statements 7z

    I think that the DBMS_SQL.PARSE line should be:
    DBMS_SQL.PARSE(cid, 'SELECT lExceptionID FROM '| |table_name| |' where lExceptionID = 1', dbms_sql.v7);
    or:
    DBMS_SQL.PARSE(cid, 'SELECT lExceptionID FROM '| |table_name| |' where lExceptionID = 1', dbms_sql.v8);

  • RELEVANCY SCORE 3.19

    DB:3.19:Dynamic View? f8


    Hi,

    I am new to Oracle PL/SQL part. i would like to get more information about Dynamic View.

    Thanks
    Mohammed

    DB:3.19:Dynamic View? f8

    Here are some links ....

    http://otn.oracle.com/pls/db92/db92.drilldown?remark=word=Dynamic+Viewsbook=preference=method=LIKEvbook=1#concept

    Check following link for some examples ....

    http://asktom.oracle.com/pls/ask/f?p=4950:8:4461005934264868480::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:10678084117056,

    Cheers,
    Suwarna

  • RELEVANCY SCORE 3.19

    DB:3.19:How Do You Include Static Text In Select Statement In Dynamic Pl/Sql c3


    I want to include some atatic text and get the output of 4 columns joined with a ":" delimiter within them in a select statement built using Dynamic PL/SQL. How do I build it.

    e.g.
    Normal select statement would be
    select 'MY SKU IS : ', col1||':'||col2||':'||col3||':'||col4 from table1 where ....where condition

    and output looks like :
    MY SKU IS A:B:C:D
    MY SKU IS a:b:c:d
    ....

    Dynamically I have -

    SQL_Stmt := 'select 'MY SKU IS : ', col1||':'||col2||':'||col3||':'||col4 from table1 where '|| wherecondition;

    I understand that this does not work because the single quote terminates the string. But my question is how do I achieve the same result with dynamic PL/SQL ? How do I include the static strings and the delimiters. I have tried using double quote, '\'....
    ????

    DB:3.19:How Do You Include Static Text In Select Statement In Dynamic Pl/Sql c3

    Correction:

    SQL_Stmt := 'select ''MY SKU is '' || col1 || '':'' || col2 || '':'' || col3 || '':'' || col4 from table1 where ' || wherecondition;

  • RELEVANCY SCORE 3.19

    DB:3.19:Mapping Parameters To Portal Pages 1m


    Can you map parameters from portal pages to another portal page that uses Portal DB Provider Dynamic Pages XML components in 9iAS R2.

    I have successfully mapped parameters from my portal pages to another portal page using Portal DB Provider - reports built from custom SQL query.

    DB:3.19:Mapping Parameters To Portal Pages 1m

    Can you map parameters from portal pages to another portal page that uses Portal DB Provider Dynamic Pages XML components in 9iAS R2.

    I have successfully mapped parameters from my portal pages to another portal page using Portal DB Provider - reports built from custom SQL query.

  • RELEVANCY SCORE 3.14

    DB:3.14:How To Avoid Dynamic Sql ? 77


    How to avoid dynamic sql ?arun

    DB:3.14:How To Avoid Dynamic Sql ? 77

    1. Use stored procedure/functions instead of Adhoc Queries
    2. Avoid using EXECUTE or sp_execute and instead use sp_executesql with @paramas defined.
    3. Mentor your developer to use Parameterized Queries into their application code. There are plenty of resources available on it.
    4. If you are using third party tool and have no control on their code, and your database is on SQL Server 2005 or later versions consider using Parameterization
    to Force at database level.
    There are many ways to check the load of Adhoc Queries on SQL Server 2005 and later versions. One of the simplest one would be to use DBCC MEMORYSTATUS.

    Find out
    CACHESTORE_SQLCP
    node and check the value of
    SinglePage Allocator
    value in it. If it is too high as compared to the value displayed under
    CACHESTORE_OBJCP
    and configured memory you are almost sure that there is a good amount of Adhoc Query load on your system.

    CACHESTORE_OBJCP
    represents memory occupied by stored procedures, functions, and triggers.
    Also you can take the help of
    sys.dm_exec_cached_plans DMV and look for value in column
    objtype.
    Give a good amount of thought before enabling Force Parameterization, because it has been set for some good reason. In SQL Server 2000, Force Parameterization was
    default behaviour.Warm Regards, Ajay

  • RELEVANCY SCORE 3.14

    DB:3.14:Dynamic Sql Mapper And Ldap 3c



    Is it possible to look up data from LDAP using the dynamic SQL mapper in FootPrints Service core 10.0.2?

    DB:3.14:Dynamic Sql Mapper And Ldap 3c


    samAccountName is what User ID is normally mapped to in Active Directory. If you take a look at your LDAP setup in FP you will see what attributes are mapped to what field, AD attributes are not always logically named.

    Thanks,

    Aaron Carey

  • RELEVANCY SCORE 3.14

    DB:3.14:What Is Dynamic Sql? c3


     
    Can someone explain what dynamic sql is?  When people refer to Dyanmic SQL, are they referring to LINQ...or something completely different? 
    Thanks

    DB:3.14:What Is Dynamic Sql? c3

    Here's the classic : The Curse and Blessings of Dynamic SQL by Erland Sommarskog

  • RELEVANCY SCORE 3.13

    DB:3.13:Re: Dynamic Sql Problem 98


    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || vTable INTO iCountJustin

    DB:3.13:Re: Dynamic Sql Problem 98

    Hi,
    Here you need to use ref cursor.
    Otherwise you can go for temporary table,
    where you can insert the data into temporary table using
    EXECUTE IMMEDIATE and
    fetching the data from temporary table.

  • RELEVANCY SCORE 3.11

    DB:3.11:Reference For Writing Dynamic Sql ms


    Hi,

    can anybody provide any good reference for writing dynamic sql. Any sort of book or article.

    Thanks

    DB:3.11:Reference For Writing Dynamic Sql ms

    As for books, check out his PL/SQL coding book, GREAT
    examples:

    http://www.rampant-books.com/book_2006_1_plsql_tune.ht
    m

    Hope this helps. . .

    Don Burleson
    Oracle Press authorOf course Don, you wouldn't be baised at all in recommending that book. (Is that your name I can see on the front cover?)

    ;)

  • RELEVANCY SCORE 3.10

    DB:3.10:Update All Tables In Db With A Particular Column 7m


    ANyone,

    How can I easily write a SQL Update statement to update all tables that have a column in it with a certain value?

    In SQL Server there is a system table that could be queried to get the table names and Dynamic Update SQL statement could be built to updte all those tables..

    Thanks in advance,
    Miller

  • RELEVANCY SCORE 3.10

    DB:3.10:Re: Dynamic Fetch For Dynamic Sql Query 19


    merz wrote:

    How do I declare the dynamic rec here?I would not know the columns in the query beforehand either.Not possible. What you are describing is called dynamic sql type four. For that type of synamic SQL you need to use DBMS_SQL package.

    SY.

    DB:3.10:Re: Dynamic Fetch For Dynamic Sql Query 19

    Thanks all for your help...I think my code is working now.
    Some more testing required to confirm...

  • RELEVANCY SCORE 3.10

    DB:3.10:Pl/Sql Pages Calling Discoverer 9a


    Hey,

    I am updating a system that was built using pl/sql pages. The application needs to link through to discoverer. Can this be done? If so, how would I go about doing this?

    Also, the current application contains reports that use dynamic sql. I need to replicate these reports using discoverer. Is it possible to use dynamic sql in discoverer?

    Any help will be greatly appreciated.

    DB:3.10:Pl/Sql Pages Calling Discoverer 9a

    Thanks for the advice guys. I used the pipe lined function as you mentioned. It worked very well. Thanks a lot

  • RELEVANCY SCORE 3.10

    DB:3.10:Dynamic Sql xp


    How to use dynamic sql

    DB:3.10:Dynamic Sql xp

    This is a very general question. You should refer to the PL/SQL documentation for help.

    In addition, Dynamic SQL is independent of Forms, so any questions on that topic should be directed to the appropriate forum.

    Regards,
    Robin Zimmermann
    Forms Product Management

  • RELEVANCY SCORE 3.10

    DB:3.10:Dynamic Sql aj


    SP의 Dynamic SQL을 지원하지 않을려면 어떻게 해야 할까요?

    DB:3.10:Dynamic Sql aj

    using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;
    namespace 그리드{ public partial class Form1 : Form {  DataTable tbl = new DataTable(stu);  public Form1()  {   InitializeComponent();      DataColumn column;   column = new DataColumn();   column.DataType = Type.GetType(System.String);   column.ColumnName = 이름;   column.AllowDBNull = false;   tbl.Columns.Add(column);
       column = new DataColumn();   column.DataType = Type.GetType(System.String);   column.ColumnName = 나이;   column.AllowDBNull = false;   tbl.Columns.Add(column);   column = new DataColumn();
       column.DataType = Type.GetType(System.String);   column.ColumnName = 출신도;   column.AllowDBNull = false;   tbl.Columns.Add(column);   column = new DataColumn();
       column.DataType = Type.GetType(System.String);   column.ColumnName = 출신고교;   column.AllowDBNull = false;   tbl.Columns.Add(column);
       dataGridView1.DataSource = tbl;
      }
      private void label2_Click(object sender, EventArgs e)  {
      }
      private void textBox3_TextChanged(object sender, EventArgs e)  {
      }
      private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)  {
      }
      private void button1_Click(object sender, EventArgs e)  {
                   DataRow row = tbl.NewRow();   row[이름] = textBox1.Text;   row[나이] = textBox2.Text;   row[출신도] = textBox3.Text;   row[출신고교] = textBox4.Text;   tbl.Rows.Add(row);
      }
      private void button2_Click(object sender, EventArgs e)  {   DataView view = new DataView(this.tbl);   view.RowFilter = textBox5.Text;   dataGridView2.DataSource = view;  } }}

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Dynamic Sql In Beginqry k8


    h1. Remove your username and password!

    DB:3.09:Re: Dynamic Sql In Beginqry k8

    Thanks for the direction. I found out why the query optimizer was not filtering over the partition key.

    The problem was the timestamp column used in the filter is in varchar format. The filter they used was
    "to_date(substr(timestamp,1,10), 'yyyy/mm/dd')= to_date(to_char(sysdate-1,'yyyy/mm/dd'),'yyyy/mm/dd')".

    I removed the functions from the left side of the condition and true enough, oracle filtered the partion via the partition key. :)

  • RELEVANCY SCORE 3.09

    DB:3.09:Re: Populate A Data Block Using A Dynamic Sql df


    may be you could set the DEFAULT_WHERE with the dynamic sql and
    call execute_query ?

    DB:3.09:Re: Populate A Data Block Using A Dynamic Sql df

    Hi Gurus,

    I have a control box which has 2 Text items. and there is a search button.

    Based on the user input, the need to prepare a Dynamic SQL and pass it as the limiting condition to another block which is based on a table x.

    What's the best approach?Any help on this is much appreciated

    Thanks

    Message was edited by:
    Forms

  • RELEVANCY SCORE 3.08

    DB:3.08:Record Groups, Execute Immediate, And Sql Injection 3k


    Hi,

    I'm not having much luck finding information on this: if I'm creating a record group from dynamic SQL using POPULATE_GROUP_WITH_QUERY, is it necessary to use EXECUTE IMMEDIATE or some other kind of DBMS_SQL or DBMS_Assert built-in beforehand to ensure that the query used will be injection proof?

    Edited by: 887630 on Oct 22, 2012 3:16 PM

    DB:3.08:Record Groups, Execute Immediate, And Sql Injection 3k

    Steve Cosner wrote:

    Seeing your other post about dynamic record groups and SHOW_LOV, I am wondering if you can even get anything to work successfully using an LOV. In fact, I did get that working. Hooray for me!

    BTW, thanks for the detailed response, Christian.

    Edited by: 887630 on Oct 23, 2012 2:26 PM

  • RELEVANCY SCORE 3.06

    DB:3.06:Re: Oracle Forms da


    The dynamic list item will be based on either a static query such as select table_name from user_tables or you will need to use populate_qroup_from_query built-in if the sql is truly dynamic.

    As for the detail block the columns will need to be the same format for all queries which can be entered in the query source for the block or table if it is always query the same table :

    ie use user_tab_columns where table_name = :contol_block.dynamic_table_item

    DB:3.06:Re: Oracle Forms da

    Hi All,

    I need help regarding oracle forms 6i. In a form, one list item having dynamic table names and when the user select the table name from the listitem it displays the details information of that table. There are horizontal and vertical scroll bars so that we can see the details of the table.

    How to change column names dynamically in database block ?

    Please help me how to create.

    Regards,
    Rajani

    Message was edited by:
    user485367

  • RELEVANCY SCORE 3.06

    DB:3.06:Dynamic Table In A Pl/Sql Script 77


    Hi everybody

    how can I design a sql query in a pl/sql script when the table I want to access is built dynamically ?

    for instance

    select to_char(sysdate,'MON')
    into v_month

    select * from table_'v_month' -- this is not working

    Thanks for your replies

    Alain

    DB:3.06:Dynamic Table In A Pl/Sql Script 77

    thank you for the answer, you're right , this dbms package for an old oracle version is not easy to set up.

  • RELEVANCY SCORE 3.05

    DB:3.05:Perform On The Fly Query m7


    I am playing with Application Wizard and built a simple SQL Report. In the SQL Report, Query section, we are asked to write a sql query that is to be run when the page is run. Is there any way to make this dynamic such that say based on some LOV that I can select from combo box, then this will be fed into the query as bind variable and run the query for example
    WHERE run_id = :run_id
    and I select any list of run_id from the dropdown box.

    Thanks,

    Mustafa

    DB:3.05:Perform On The Fly Query m7

    if your app had an item called RUN_ID you could reference it in exactly the way you've shown us above. you can see these types of references in action if you view our Demonstration Applications. take page 200 of the Sample Application, for example. if you install the Sample Application into your workspace per the directions in the doc (available online at http://htmldb.oracle.com/i/doc/mvl_samp.htm#sthref69 ), you're able to run the app and see how certain things were done. if you click the Orders tab in the app, you're taken to page 200. if you look at the query that makes up the Orders region, you'll see that it uses P200_SEARCH and P200_CUSTOMER_ID in its where clause.

    hope this helps,
    raj

  • RELEVANCY SCORE 3.05

    DB:3.05:Ref Cursor, Dynamic Sql And Jdbc m7


    Hi - can anyone help. We are building an application which uses Java on the client and performs all database functions in PL/SQL stored procedures and functions, accessed by JDBC. To return result sets I am using REF CURSORS (weakly typed) which return the results of a query built using Dynamic SQL. When called from Java the SP executes and appears to bring back a result set, however only the first field of each records is returning a value. When get is used on any other field index no result is returned. Is there an issue using Dynamic SQL and passing back ref cursors to JDBC. We have based the code on samples from OTN (refcursorsample), but it uses static SQL.
    Also - are ref cursors forward only scrolling when passed back to the client?
    Thanks

    DB:3.05:Ref Cursor, Dynamic Sql And Jdbc m7

    Hi - can anyone help. We are building an application which uses Java on the client and performs all database functions in PL/SQL stored procedures and functions, accessed by JDBC. To return result sets I am using REF CURSORS (weakly typed) which return the results of a query built using Dynamic SQL. When called from Java the SP executes and appears to bring back a result set, however only the first field of each records is returning a value. When get is used on any other field index no result is returned. Is there an issue using Dynamic SQL and passing back ref cursors to JDBC. We have based the code on samples from OTN (refcursorsample), but it uses static SQL.
    Also - are ref cursors forward only scrolling when passed back to the client?
    Thanks

  • RELEVANCY SCORE 3.03

    DB:3.03:Re: Ora-01008: Not All Variables Bound fk


    cmovva wrote:
    I need to use string of dynamic values with USING. Any alternatives?What's the business requirement that's driving the dynamic SQL decision? Dynamic SQL has certain use cases and they are few. Dynamic SQL creates code that's tough to maintain, and can be littered with SQL injection problems.

    DB:3.03:Re: Ora-01008: Not All Variables Bound fk

    create table CM_DEPT3( DEPTNO NUMBER(10),DNAME varchar2(20) );

    SQL DECLARE
    2 v_opr_sql VARCHAR2 (4000);
    3 v_table_opr VARCHAR2 (60) := 'CM_DEPT3';
    4 v_col_string VARCHAR2 (60) := 'DEPTNO,DNAME';
    5 v_values_string VARCHAR2 (60) := ':1';
    6 v_using_string VARCHAR2 (256) := '10,' || '''ACCOUNTING''';
    7 BEGIN
    9 EXECUTE IMMEDIATE 'INSERT INTO CM_DEPT3('
    10 || v_col_string
    11 || ') VALUES(REGEXP_SUBSTR('
    12 || v_values_string
    13 || ',''\d+''),REGEXP_SUBSTR('
    14 || v_values_string
    15 || ',''[[:alpha:]]+''))'
    16 USING v_using_string,v_using_string;
    17 COMMIT;
    18 19 END;
    20 /

    PL/SQL procedure successfully completed.

    SQL select *from CM_DEPT3;

    DEPTNO DNAME
    ---------- --------------------
    10 ACCOUNTING

    @adrian,
    you will get the solution for everything but you may have to do work around , just don't expect anything directly from someone.

  • RELEVANCY SCORE 3.03

    DB:3.03:Dynamic Sql ? ? ? sx


    what is dynamic sql ? why we use it ?
    and what is advantages of using dynamic sql

    post answers or link

    help me ? ? ?

  • RELEVANCY SCORE 3.03

    DB:3.03:Dynamic Sql pk


    what is the difference between DBMS_SQL and Native Dynamic Sql?

    Thanks
    Venkadesh Raja

    DB:3.03:Dynamic Sql pk

    user112246 wrote:
    what is the difference between DBMS_SQL and Native Dynamic Sql?

    Thanks
    Venkadesh RajaYou seem to be asking a lot of questions that are basic documentation questions. This is not appreciated on the forums.
    People here are volunteers giving up their own time to help others who have issues getting their code or whatever to work. They are not here to just teach people things that are easily found in the manuals or by googling.

    If you have a specific issue that you need help with, post the relevant details as per the forum FAQ: {message:id=9360002}

    Ensure you've checked for your answers in the manuals first:

    http://tahiti.oracle.com/

  • RELEVANCY SCORE 3.03

    DB:3.03:Creating Dynamic Pages With Static Urls 8f


    Hello

    I've built a simple CMS (in DW, using ASP and MS SQL) for a
    client which allows them to edit existing pages (all of which are
    called page.asp?p=X) or add news pages if they want.

    However they'd like to be able to name their new pages e.g.
    pagename.asp.

    Is there an easy way to do this?

    Many thanks in advance

    Square Eye

    DB:3.03:Creating Dynamic Pages With Static Urls 8f

    Hello

    I've built a simple CMS (in DW, using ASP and MS SQL) for a
    client which allows them to edit existing pages (all of which are
    called page.asp?p=X) or add news pages if they want.

    However they'd like to be able to name their new pages e.g.
    pagename.asp.

    Is there an easy way to do this?

    Many thanks in advance

    Square Eye

  • RELEVANCY SCORE 3.03

    DB:3.03:Dynamic Sql + Bind Variables p1


    Hi all,

    is it right or wrong to use Dynamic SQL + bind variables (and why) ?

    DB:3.03:Dynamic Sql + Bind Variables p1

    Marco,

    One of the reasons why Oracle invented stored procedures is to reduce parsing. Parsing is a serialization device, as during parse part of the shared pool is inaccessible, due to latches.
    So first of all: using Dynamic SQL defeats the purpose of stored procedures, as Dynamic SQL is parsed always.
    However, there is hard parsing and soft parsing.
    If you use bind variables, and you use the statement multiple times, whether it is in one session or between multiple sessions, you will at least get a soft parse : part of the parse will be skipped.
    So : you should avoid Dynamic SQL (also static SQL always uses bind variables), and if you use it, you should use bind variable. Please note table names and column names can not be bound.

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

  • RELEVANCY SCORE 3.01

    DB:3.01:Infoview Dynamic Prompt Error : Exception From Hresult: 0x8004100d d3



    Good evening,

    I am trying to post a report to our BusinessObjects Enterprise server. The report itself posts correctly and all applicable dynamic prompts have been built in the Business View Manager. The dynamic prompt comprises three levels and each level's values are derived from SQL Command objects.

    Ex:

    Provider (AD_Provider SQL Command)

    Date (AD_Date SQL Command)

    Patient (AD_Pat SQL Command)

    When the report is run from Crystal Reports XI R2 Designer, the dynamic prompts display correctly, I am able to select values for each level of the prompt, and the report pulls the correct data....however, when I post the report to InfoView, when I try to click on the dynamic prompt, I receive the following:

    Exception from HRESULT: 0x8004100D

    I have tried researching this error without success.

    Has anyone else ever encountered this error?

    Thank you

    DB:3.01:Infoview Dynamic Prompt Error : Exception From Hresult: 0x8004100d d3


    It could be related to access restrictions, the user needs the following rights:

    view objects

    schedule the document to run

    view document instances

    edit objects that the user owns

    Also, make sure the Page Server is running and verify username an password are entered correctly in the data connection used for the dynamic list of values in the BusinessView.

    Caroline

  • RELEVANCY SCORE 3.01

    DB:3.01:Ref Cursor In A Package xp


    I have a package that returns a ref cursor.

    Package definition is like this:
    TYPE PO_CURSOR IS REF CURSOR;
    PROCEDURE PROC_SELECT_POBOM(PONUMBER VARCHAR2,
    POLINENUMBER VARCHAR2,
    POBOMCURSOR OUT PO_CURSOR);

    In the package, I want to open ref curosr with a dynamic sql (select statement). I read dbms_sql built-in package can be used but not finding a good example on how to do this.

    Please advise. Thx!

    DB:3.01:Ref Cursor In A Package xp

    Thx. Your question made me to look into the alternate options to dynamic sql. dynamic sql is not really required in my package...

    First of all without using Dynamic SQL I could build a conditional SQL statement and opened a cursor.
    L_MAINSQL VARCHAR2(2000);
    ....
    OPEN POBOMCURSOR FOR L_MAINSQL;
    ....

    thanks again!

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Select Query Problem f8


    Means i can't use dynamic sql

    DB:3.01:Re: Select Query Problem f8

    1004937 wrote:
    I know this.

    But i can't use scott.emp
    I have to pass the table name at run time.Why do you? Don't you know the names of the tables on your database? That would seem like a poorly designed database or poorly designed application requirement, if you can't know at design time what the tables are called that you need to use. That results in having to use dynamically generated queries, which leads to more dyanmically generated code to process those queries, and then you'll probably be wanting a dynamically generated interface to adapt the output (and maybe input) for the user etc. and you end up with a whole load code that is unperformant, buggy, unscalable and liable to security issues.

    I am using this in function.
    But function don't use dynamic sql.Why do you think a function can't have dynamic sql...

    Dynamic sql is used in procedure but i can't use procedure.... but you think a procedure can. ?

    There's nothing to stop you creating dynamic sql in either a function or procedure. The only differences between a function and a procedure is that a function returns a value whereas a procedure doesn't, and functions can be used in SQL statements (subject to some restrictions).

  • RELEVANCY SCORE 3.01

    DB:3.01:How To Pass Dynamic Value For Dimension To Universe? 1c



    Hi Guys,

    I'm currently working on Crystal Reports on SAP BO 4.1 SP2. I built the reports from the universe. I have one dimension that required to be dynamically changing over time. Can I pass a dynamic value to one specific dimension in the universe? or send a custom SQL query to the universe upon request?

    Regards,

    Chai

    DB:3.01:How To Pass Dynamic Value For Dimension To Universe? 1c


    Thank you JRK. Could you please explain more what exactly is "a logical condition in the universe"? and where would I write it in the universe?

  • RELEVANCY SCORE 3.01

    DB:3.01:Nvarchar (Max) To Print Dynamic Sql Query To A Sql File 1k


    Hi
    I have a requirement where i need to write an SP which would construct a huge query using dynamic SQL and save the Dynamic query to a file.
    The Dynamic SQL Variable I am using as @NVARCHAR(MAX) but since the query being built is large (4000 characters), I am not able to select it into a table. PRINT @SQL prints the whole query but SELECT @SQL prints only upto 4000 characterrs.

    And I need to save this huge dynamix sql to a file using the SP.

    Any thoughts as to how i can achieve this?

    Rajiv

    DB:3.01:Nvarchar (Max) To Print Dynamic Sql Query To A Sql File 1k

    The easy way to do this is to store your data in a table variable. Store each segment on a new row
    Go down, not across :)

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Datapump Api Pl/Sql Question 7k


    hi,

    You can use DYNAMIC SQL and replace the schema name.

    sql := 'DBMS_DATAPUMP.METADATA_FILTER(h1, ' || .......;
    EXECUTE IMMEDIATE sql;

    Thanks

    DB:3.01:Re: Datapump Api Pl/Sql Question 7k

    try using SCHEMA_LIST instead:

    DBMS_DATAPUMP.METADATA_FILTER( h1,'SCHEMA_LIST', 'IN (''' || inSchema || ''')');

  • RELEVANCY SCORE 3.01

    DB:3.01:Jdbc, Using Dynamic Sql dp


    How to implement dynamic SQL (like dynamic SQL Method 4 in Pro*C), which
    lets program accept or build dynamic SQL statements that contain an unknown
    number of select-list items or placeholders?

  • RELEVANCY SCORE 3.01

    DB:3.01:How To Query Repository To Determine Dynamic Parameters Used By A Report d1



    Using BOXI 3.1 SP3

    Windows 2008

    .NET

    SQL 2008 Database

    I would like to query the repository to figure out which report is using which a dynamic parameter and then to figure out the name of that dynamic paremeter.

    The issue is the when I publish a report with Dynamic Paremeters from CR2008 to BOXI, BOXI will automatically create all the underlying connections and also rename the parameters if there is a duplicate. I have a situation where there were over 100 +reports published and thru repository explorer in CR2008, it's impossible to tell which parameter belongs to which report.

    I would like to find in the repository the name of the dynamic parameters being used by the report.

    If I query CI_INFOOBJECTS, it does have the prompts and the original name (SI_NAME). I don't know if it's possible thru Query Builder to query and find this result, or whether I need to write some .NET code. The only hint in CI_INFOOBJECTS that a report has dynamic parameter is thru SI_GROUP_ID = eor://HOECRS02.NA.XOM.COM:6400/AVDGc3Df_G5Ej9HoJqO3V2g

    All dynamic parameters have SI_GROUP_ID populated.....but not sure how to use this unique value AVDGc3Df_G5Ej9HoJqO3V2g which appears to be a SI_CUID but not sure what to query next to get more information.

    If anyone has a query built already or some code to point me in the right directiion, that would be greatly appreciated.

    DB:3.01:How To Query Repository To Determine Dynamic Parameters Used By A Report d1


    Using BOXI 3.1 SP3

    Windows 2008

    .NET

    SQL 2008 Database

    I would like to query the repository to figure out which report is using which a dynamic parameter and then to figure out the name of that dynamic paremeter.

    The issue is the when I publish a report with Dynamic Paremeters from CR2008 to BOXI, BOXI will automatically create all the underlying connections and also rename the parameters if there is a duplicate. I have a situation where there were over 100 +reports published and thru repository explorer in CR2008, it's impossible to tell which parameter belongs to which report.

    I would like to find in the repository the name of the dynamic parameters being used by the report.

    If I query CI_INFOOBJECTS, it does have the prompts and the original name (SI_NAME). I don't know if it's possible thru Query Builder to query and find this result, or whether I need to write some .NET code. The only hint in CI_INFOOBJECTS that a report has dynamic parameter is thru SI_GROUP_ID = eor://HOECRS02.NA.XOM.COM:6400/AVDGc3Df_G5Ej9HoJqO3V2g

    All dynamic parameters have SI_GROUP_ID populated.....but not sure how to use this unique value AVDGc3Df_G5Ej9HoJqO3V2g which appears to be a SI_CUID but not sure what to query next to get more information.

    If anyone has a query built already or some code to point me in the right directiion, that would be greatly appreciated.

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Dynamic Sql Problem da


    Yup. You can perform updates using Forms_ddl, if your DML statement is built dynamically.

    Thanks and Regards,

    DB:3.01:Re: Dynamic Sql Problem da

    so does do you know why the above is not working?

    Here's a clue: Your first Forms_DDL statement is a SQL Update, followed by a Commit.

    But you never check Form_Success. Instead, you barrel ahead and issue a second Forms_DDL('commit'); ...and THEN you check Form_Success. So it looks like your second one (with only the commit) was successful, but nobody knows what happened with the first, because you never checked the status.

    Whenever you issue a Forms_DDL, do this first: Put the entire SQL string into a varchar2 variable, named something like SQL_Text. Then, issue your Forms_DDL like this:

    Forms_DDL(SQL_Text);

    Then if it fails, you can display SQL_Text rather than repeat coding of the entire statement two more times like you did above.

  • RELEVANCY SCORE 3.01

    DB:3.01:Oracle a8


    Can you please advise me.
    Can we use dynamic sql heavily in our pl/sql code.
    What would be the disadvantage advantage.
    Does it would be the best approach to implement dynamic sql ( whole project ).

    DB:3.01:Oracle a8

    HuaMin Chen wrote:

    Pro*Ada programs that accept and process dynamically defined SQL statements are more versatile than those using static embedded SQL statements. Dynamic SQL statements can be built interactively with input from users having little or no knowledge of SQL.

    For example, your program might simply prompt users for a search condition to be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more complex program might allow users to choose from menus listing SQL operations, table and view names, column names, and so on. Thus, dynamic SQL lets you write highly flexible applications.Untrue and incorrect.

    Application flexibility has NOTHING to do with dynamic SQL!

    Dynamic SQL is needed when the system deals with "unknowns" at development time. Like a report rendering engine that generates a report from meta data and queries supplied at run-time.

    This is however an EXCEPTION. The vast majority of applications, HR, Sales, Marketing, Billing, Product Management, Trouble Tickets/Call Desk, SLA Management, and so on, deals with a KNOWN data model, where the entities and objects are known at development time.

    You are also totally incorrect with regards to the user interface. A user interface DOES NOT lists tables and columns in dropdowns and enable users to construct their own SQLs (and do cartesian joins that trash database performance).

    A user interface is an ABSTRACTION layer. It is there to ASSIST the user in business flow processing and the making of business decisions. Again, designing a user interface that exposes the data model (tables and columns) directly to the user is an EXCEPTION. And limited to functionality such as designing reports in a report writer system. Or designing an application entry form using Apex. Functions that are strictly limited to the developer-type and not end-users.

    Dynamic SQL is WRONG 99% of the time.

    If you want to argue that, then let's hear it. As I for one am sick and tired of reading about the greatness and flexibility of dynamic SQL approaches where there is not a single fact or shred of evidence provided on how this results in a sound, robust, secure, performant and scalable solution.

  • RELEVANCY SCORE 3.01

    DB:3.01:The Microsoft Ole Db Provider For Odbc (Msdasql) Does Not Support Sql Native Client df


    Summary=============================The Microsoft OLE DB Provider for ODBC (MSDASQL) does not support SQL Native Client.
    More Information=============================MSDASQL is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. The MSDASQL is recommended to be used for the scenario in which there is only ODBC driver available, while SQL Native Client (SQLNCLI) is a data access technology that (new to Microsoft SQL Server 2005) combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL). SQLNCLI is not desgined to support MSDASQL.
    References=============================http://msdnstage.dns.microsoft.com/en-us/library/ms810810.aspxhttp://msdn.microsoft.com/en-us/library/ms130892.aspxMicrosoft Online Community Support

    DB:3.01:The Microsoft Ole Db Provider For Odbc (Msdasql) Does Not Support Sql Native Client df

    Summary=============================The Microsoft OLE DB Provider for ODBC (MSDASQL) does not support SQL Native Client.
    More Information=============================MSDASQL is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. The MSDASQL is recommended to be used for the scenario in which there is only ODBC driver available, while SQL Native Client (SQLNCLI) is a data access technology that (new to Microsoft SQL Server 2005) combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL). SQLNCLI is not desgined to support MSDASQL.
    References=============================http://msdnstage.dns.microsoft.com/en-us/library/ms810810.aspxhttp://msdn.microsoft.com/en-us/library/ms130892.aspxMicrosoft Online Community Support

  • RELEVANCY SCORE 3.01

    DB:3.01:Permit Variables To Be Used As Object Names? zc


    Does the SQL Server team have any plans to permit variables to be used as object references in T-SQL statements? As explained inhttp://support.microsoft.com/kb/65466,
    and as every db developer knows, this has never been supported. In consequence, many kinds of abstraction force use of dynamic SQL, which makes refactoring and maintainability more difficult and, of course, means that in-built features such as referential
    integrity cannot be used.

    DB:3.01:Permit Variables To Be Used As Object Names? zc

    Not likely since ORACLE works just about the same way with regard to static dynamic SQL:
    When to Use Dynamic SQL
    In practice, static SQL will meet nearly all your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Its use is suggested when one of the following
    items is unknown at precompile time:

    Text of the SQL statement (commands, clauses, and so on)

    The number of host variables

    The datatypes of host variables

    References to database objects such as columns, indexes, sequences, tables, usernames, and views

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_13dyn.htm
    Dynamic SQL example for reindexing every table:
    http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

    Kalman Toth Database OLAP Architect
    sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration

  • RELEVANCY SCORE 3.01

    DB:3.01:Dynamic Page k1


    where can I find some docs for creating
    dynamic page in portal application?
    as well as the portal built-in PL/SQL such as
    htp.p(...) ?
    thanks

    DB:3.01:Dynamic Page k1

    PL/SQL doc : [URL=http://technet.oracle.com/doc/server73x/PLS23/toc.htm]http://technet.oracle.com/doc/server73x/PLS23/toc.htm
    null

  • RELEVANCY SCORE 3.01

    DB:3.01:Re: Fetch Table Procedure 7j


    well u can use Dynamic SQL in PL/SQL for this and REF Cursors refer Oracle documentation for REF CURSORS and DYNAMIC SQL

    plz mark it helpful if it is

    DB:3.01:Re: Fetch Table Procedure 7j

    Hi welcome,
    Please go through the forum link mentioned below before you try this out.
    Re: pctfree alterAs far as syntax is concerned

    -- Created on 11/30/2009 by USER
    declare
    -- Local variables here
    i INTEGER :=20; /* variable you will pass to the procedure */
    begin
    -- Test statements here
    FOR x IN (SELECT table_name FROM user_tables WHERE pct_free !=10 AND (table_name LIKE 'GENAGILE%' OR table_name LIKE 'GENMICCRO%')) LOOP

    dbms_output.put_line (x.table_name); /* for debugging */
    EXECUTE IMMEDIATE 'ALTER table '||x.table_name||' pctfree ' || i;
    END LOOP;
    end;Cheers!!!
    Bhushan

  • RELEVANCY SCORE 3.01

    DB:3.01:Linq To Sql j1


    I have an existing project built with Dynamic Data and need to migrate it to Sharepoint as webparts. Does anyone have any experience with this type of conversion?

    DB:3.01:Linq To Sql j1

    .NET 3.5 is built upon .NET 2.0, therefore, you can extend the current SharePoint by following the following 2 steps:
     
    1. You need to add all the assemblies entries required by dynamic data to the web.config in SharePoint web application.
    2. You might also need to register some of the controls as SafeControl in web.config
     
    Since I’m not expert on what’s the assemblies and/or controls required in dynamic data, you will need to turn to ASP.NET Dynamic Data for help on this matter.Keep It Simple and Stupid.

  • RELEVANCY SCORE 3.01

    DB:3.01:Using Multiple Sql Statements In Dynamic Sql 9z



    Hi,

    Is it possible to use multiple SQL statements in a dynamic SQL. For example:

    exec drop table A; drop table B;

    Thanks.

    D.

    DB:3.01:Using Multiple Sql Statements In Dynamic Sql 9z


    Hi,

    The recent discussion on this topic can be found here.

    Multiple statements in HANA SqlScript

    Regards,

    Ravi

  • RELEVANCY SCORE 3.01

    DB:3.01:Dynamic Sql Export To Excel mx


    Hi Sirs,
    How can Export Dynamic SQL DATA to MS EXCEL

    DB:3.01:Dynamic Sql Export To Excel mx

    Are you trying to export data through the database or through Oracle Forms? If from the database, then you need to post your question in the SQL and PL/SQL forum. If you are using Oracle Forms, then you can either use WebUtil or a 3rd Party Java Bean to export data to Excel. There are numerous examples in the forum on this topic so just search on "Webutil export to Excel" and you'll get several examples.

    Craig...

  • RELEVANCY SCORE 3.01

    DB:3.01:Can We Use Ref Cursor In Dynamic Sql? k8


    Hi Expert..

    Can we use ref cursor in Dynamic SQL

    Umesh Goel

    DB:3.01:Can We Use Ref Cursor In Dynamic Sql? k8

    Can we use ref cursor in Dynamic SQL

    This question does not make any sense.

    SQLs are turned into a cursor by the SQL engine. Does not matter who created the SQL (PL/SQL, Java, etc). Does not matter how that SQL was created (static, dynamic).

    The SQL (source code) is received by the SQL engine. It is parsed and a cursor (execution code) is created that contains the step-by-step binary instructions of how to get to the specified rows.

    To see how a cursor looks like, query V$SQL_AREA.

    So how can you use a cursor (a binary SQL "program") in a dynamic SQL (source code)?

    Yes, you can create multiple cursors in a SQL's projection. But that is something different and very seldom done.

    Exactly what prompted this question of yours?

  • RELEVANCY SCORE 3.01

    DB:3.01:What Exactly Does The Echo Command Do In A Dynamic Command String That Is Executed? s8


    Can someone tell me or provide a URL that explains what exactly an ECHO command does in a dynamic command string that is executed within a SQL Server Stored Procedure?
    I searched MSDN and can't seem to find anything that explains what it does.
    This is the command string being built that I'm looking at...
    SET @CommandString
    = 'ECHO ? dir \\FOLDER\Structure\Path'...
    INSERT INTO #TempTable EXEC xp_cmdshell @CommandString
    Thanks for your review and am hopeful for a reply.
    PSULionRP

    DB:3.01:What Exactly Does The Echo Command Do In A Dynamic Command String That Is Executed? s8

    Ahhhh...I think I found the answer...
    Basically, in the example, ECHO ? Simply Displays messages and in this case ?. It is simply acting as a verification that a connection has been made. The then executes the next, following command based on whether the previous command completed
    successfully. So rather than ?, it could have been anything...like ECHO Connect success

  • RELEVANCY SCORE 2.98

    DB:2.98:Under What Circumstances Are Right To Use Dynamic Sql? jp


    Hi,

    I'm wondering under what conditions are properly to use dynamic sql and what are the advantages of using dynamic sql?

    Amy

    DB:2.98:Under What Circumstances Are Right To Use Dynamic Sql? jp

    DDL would include, say creating a table. In a PL/SQL block, you cannot create a table with static SQL.

    begin
    create table dynSQLTbl( col1 varchar2(100) );
    end;
    [end]

    You'd have to do something like this

    [pre]
    declare
    sqlStmt varchar2(4000);
    begin
    sqlStmt := 'create table dynSQLTbl( col1 varchar2(100) );'
    execute immediate sqlStmt;
    end;If you wanted to move all your tables to a new tablespace, you could use dynamic SQL

    declare
    sqlStmt varchar2(4000);
    begin
    FOR x IN (SELECT table_name FROM user_tables)
    LOOP
    sqlStmt := 'ALTER TABLE ' || x.table_name ||
    ' MOVE TABLESPACE new tablespace';
    execute immediate sqlStmt;
    END LOOP;
    end;Justin
    Distributed Database Consulting, Inc.
    http://www.ddbcinc.com/askDDBC

  • RELEVANCY SCORE 2.96

    DB:2.96:How To Write Dynamic Sql For This Sql ? aj


    How do I write the Dynamic SQL for this select ?

    Select INITCAP(atv.mcd_name) ||' ( '||atv.mcd_type_code||' )' AS Municipality
    From Table1;

    I want the above sql statement in Dynamic SQL.
    Thanks.
    Srini

  • RELEVANCY SCORE 2.96

    DB:2.96:Problems Running Dynamic Sql 8k


    "WHAT COULD BE THE POSSIBLE REASONS FOR DYNAMIC SQL NOT RUNNING ??"

    Need in Urgent Reply

    Thanx

    Shailender Rawat

    DB:2.96:Problems Running Dynamic Sql 8k

    Who Left the last guy inside ... I think he is in teribble pain...

    Regards,
    Ganesh R

  • RELEVANCY SCORE 2.96

    DB:2.96:Ansi Dynamic Sql 3m


    Hi,

    have someone an example about ansi dynamic SQL (with lob)?
    on otn the example ansidyn2.pc doesn't work.

    thanks

    arno

    DB:2.96:Ansi Dynamic Sql 3m

    Hi,

    I have found the problem.
    type = 5; // smallint
    replace with:
    type = 12; // varchar

    ... and it works.

    but I didn't get the length

    arno

  • RELEVANCY SCORE 2.96

    DB:2.96:Dynamic Tree With Ie Style Problem j7


    Hi all,
    I developed a dynamic tree, built upon oracle hierarchical sql and tested with a 4000 records tree. It works great in Firefox, but it's a mess in IE: plus/minus icons and branch descriptions appear on different lines.

    The demo is on http://apex.oracle.com/pls/otn/f?p=38539:1

    Anybody can help ?

    Thanks
    Mauro

    DB:2.96:Dynamic Tree With Ie Style Problem j7

    Solved.
    For Internet Explorer the anchor style requires width: 100%.

  • RELEVANCY SCORE 2.96

    DB:2.96:Dynamic Sql j1


    i want to use one 'create statement' in pl/sql .is it possible using Dynamic SQL.how to use Dynamic SQL statement for create command.plz.. send full steps

    DB:2.96:Dynamic Sql j1

    You tried to kill him first with fries :P

    BTW we can arrange anything else, whatever you would like to have :)

    Sidhu

  • RELEVANCY SCORE 2.96

    DB:2.96:Dynamic Sql In Sql Field xc


    hello,

    I need execute an SQL dynamic as field's SQL:

    select field1, '(select field2 from table2)'
    from table1;

    And this field2 as output number. It's possible?

    DB:2.96:Dynamic Sql In Sql Field xc

    Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production

    Because if not use bind numeric values I get error.
    It's ocurred because my oracle client has brasilian portugues and server has american.

  • RELEVANCY SCORE 2.96

    DB:2.96:Re: Dynamic View aa


    Try dynamic SQL http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm ?

    DB:2.96:Re: Dynamic View aa

    You can try to adapt following example:

    SQL select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
    PL/SQL Release 10.2.0.1.0 - Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production

    SQL set serveroutput on
    SQL drop table t_us_v purge;

    Table dropped.

    SQL drop procedure cv;

    Procedure dropped.

    SQL drop view v1;

    View dropped.

    SQL --
    SQL create table t_us_v(x int);

    Table created.

    SQL insert into t_us_v values (99);

    1 row created.

    SQL commit;

    Commit complete.

    SQL --
    SQL create or replace procedure cv
    2 is
    3 v_lang varchar2(30);
    4 v_stmt varchar2(100);
    5 begin
    6 select sys_context('USERENV','LANG') into v_lang from dual;
    7 v_stmt := 'create view v1 as select * from t_' || v_lang || '_v';
    8 execute immediate v_stmt;
    9 end;
    10 /

    Procedure created.

    SQL show errors
    No errors.
    SQL --
    SQL select sys_context('USERENV','LANG') from dual;

    SYS_CONTEXT('USERENV','LANG')
    --------------------------------------------------------------------------------
    US

    SQL exec cv;

    PL/SQL procedure successfully completed.

    SQL --
    SQL desc v1;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    X NUMBER(38)

    SQL select * from v1;

    X
    ----------
    99Note that :
    - running a procedure containing DLL implies a commit of current transaction
    - it is questionable to create at run-time database objects.

    Edited by: P. Forstmann on 15 fvr. 2010 14:31

  • RELEVANCY SCORE 2.96

    DB:2.96:Dynamic Sql Queries kf


    Hi, Pl help me in understanding how SAP HANA treats dynamic SQL queries and what features/ capabilities are supported. Thanks.

    DB:2.96:Dynamic Sql Queries kf

    Hi Saurabh,Regarding Dynamic SQL features and support, check HANA SQLScript Reference Guidehttp://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdfRegards,Vivek

  • RELEVANCY SCORE 2.96

    DB:2.96:Stored Procedures Vs. Dynamic Sql m3


    why SP's are technically faster than dynamic SQL?

    DB:2.96:Stored Procedures Vs. Dynamic Sql m3

    Search stored procedure sql statement faster on http://www.msdn.com and the first result contains the answer to the question:They allow faster execution. If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times. Thanks,Hooray

  • RELEVANCY SCORE 2.95

    DB:2.95:10.1.2 Dynamic Page Pl/Sql Frameset, Iframe Issue xx


    DB:2.95:10.1.2 Dynamic Page Pl/Sql Frameset, Iframe Issue xx

    Hi,

    I have replaced all greater than and less than with [] in the below code as was not able to post.

    The question I have is arround frameset and iframe. I have 2 dynamic pages created under a DB Provider. Afte creating the dynamic pages when I click run as a portlet somehow it doesn't work. In the first dynamic page I have an iframe which points to the second dynamic page in which I have used frameset with 2 columns. When testing I don't see anything not even the frameset. Here is my first page with iframe:

    FIRST_IFRAME_TST:

    [ script language"JavaScript" ]
    [ ORACLE ]
    DECLARE
    srcUrl varchar2(200);
    begin
    htp.p('document.all.iParent.height=document.body.clientHeight-'||:frameHeight1||';');
    htp.p('}');
    htp.p('/script');
    htp.p('[iframe name="iParent" id="iParent" width="100%" height="500" src="'||'/pls/portal/TSTDBProvider.SECOND_CONTENT_FRAMES.show?p_arg_names=contentarea1p_arg_values=test'" onload="sizeIframe()"]'';');
    end;
    [ /ORACLE ]

    And here is the second page with frameset.
    SECOND_CONTENT_FRAMES

    [ frameset cols="200,*" ]
    [ ORACLE ]
    DECLARE
    srcUrl varchar2(200);
    begin
    htp.p('[ frame name="folder1" src="http://www.google.com"]');
    htp.p('[ frame name="content1" id="content1" src="http://www.yahoo.com"]');
    end;
    [ /ORACLE ]
    [ /frameset ]

    Don't know if it's the issue with Portal as the above are very startight forward test pages I created that doesn't work when I click FIRST_IFRAME_TST to run as a portlet. Any help is appreciated.

    Thanks

  • RELEVANCY SCORE 2.95

    DB:2.95:Dynamic Sql m1


    hii,

    can i used dynamic sql in oracle form
    and

    can i bypass oracle login screen in oracle form

    DB:2.95:Dynamic Sql m1

    In addition to Francois's answer

    In ON-LOGON trigger, pass the following credential.
    LOGON(ID,password@connect string);Example
    LOGON('scott','tiger'||'@'||'orcl.world');If you application deployed in Application server. You can make an entry in formsweb.cfg
    userid=scott/tiger@orcl.world

  • RELEVANCY SCORE 2.95

    DB:2.95:Openrowset Dynamic Sql If Exists f1


    Hi,

    DB:2.95:Openrowset Dynamic Sql If Exists f1

    How do i add the AS T1 to the print @sql?

  • RELEVANCY SCORE 2.95

    DB:2.95:Dynamic Pivot In T-Sql Or Somewhere Else? 3f


    In a
    recent thread on dynamic SQL, the issue of dynamic PIVOT came up.
    Dynamic PIVOT can be implemented in T-SQL with dynamic SQL:
    Pivots with Dynamic Columns in SQL Server 2005
    http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/
    http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query
    http://www.sqlusa.com/bestpractices2005/dynamicpivot/
    Alternatives:
    1. Enhancement by MS to the current static PIVOT which has limited use since it is not data-driven
    2. Use SSRS which has built-in dynamic columns
    3. Use client app
    What is your opinion? Is it OK to use dynamic SQL for dynamic PIVOT?

    Kalman Toth Database OLAP Architect
    SQL Server 2014 Design Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    DB:2.95:Dynamic Pivot In T-Sql Or Somewhere Else? 3f


    T-SQL dynamic SQL has a productivity edge over C#.

    That depends on what you want to do. And of course your current skills.
    If you want to implment, say, a five-table join in C# with on SQL, you need to write something like five nested loops. And you will have to settle on the query plan in advance. SQL wins without dispute. But for someone who is fluent in C#, writing this occasional
    five-table join is painful.
    On the other hand, if you do a lot of string manipulation and other operations where SQL is weak, it is a different matter.
    But, as I said, your skill set matter. I am certainly more productive in T-SQL for tasks where C# is the better choice, because the few times I work with C# I need to look up a lot of things in the manual. Its the same experience as the few time I write
    responses on these forum in Spanish (because the original question was in Spanish in Portuguese). To produce a 3-4 line response takes me about the same time as composing a full response like this one, if not longer.

    But this does not prove that C# or English is more productive in general than T-SQL or Spanish.
    I only browsed the link you posted, but I think one reason it seems complex is that he mix presentation and data manipulation. That is, rather than only pivoting the data, he also throws in the style-sheet stuff. Which you would never do in T-SQL.
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • RELEVANCY SCORE 2.95

    DB:2.95:Dynamic Sql Method 4; ac


    Does anyone know work around of Dynamic SQL Method 4 in PL/SQL
    thanks
    Pramod

    DB:2.95:Dynamic Sql Method 4; ac

    Does anyone know work around of Dynamic SQL Method 4 in PL/SQL
    thanks
    Pramod

  • RELEVANCY SCORE 2.95

    DB:2.95:Problem About Dynamic Sql p3


    Good Afternoon,

    sql query is faster or dynamic sql query is faster ?

    is there any diff between them ?
    which one is preffered ?

    thanks in advance
    Pankaj

    DB:2.95:Problem About Dynamic Sql p3

    A static SQL query will be hard-parsed and executed, then if executed a second
    time the existing execution plan will be used (soft-parsed) and the data, if
    cached, will be used causing it to execute more quickly.A static SQL and dynamic both query will be be hard-parsed and executed at first time, then if executed a second time the existing execution plan will be used
    (soft-parsed) unless you dont use bind variable or you dont issue the same sequel in respect to string ,if cached (when you use it within PL/SQL block) will be used causing it to execute more quickly.

    But with 10g there is a lil bit enhancment in internal algorithim which causes the dynamic sequel will also be cached in cursor global area if you use bind variable,which is reused rather to search parse tree and there execution plan and goes to execute which is softer soft parse, parse once and excute many.

    which one is preffered ?Preffered

    -When you use DDL inside PL/SQL.
    -When you use java,C but not with pre-compiler approach e.g PL/SQL

    Khurram

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Sql - Built m7


    i have a table with say - rule_component

    which has two columns

    1.db_table
    2.from_clause

    The two columns contains tables for example

    db_table
    ------------
    emp
    dept
    bonus
    emp
    def

    from_clause
    ----------------

    emp,dept,bonus,dept,emp,abc

    My requirement

    i want to find the duplicate tables in both the from_clause and db_table column
    and populate in my from clause which are unique to both the columns

    say example

    select ......

    from emp,dept,bonus,abc,def

    DB:2.94:Dynamic Sql - Built m7

    SQL descr sswms_rule_components;
    Name Null? Type
    ----------------------------------------- -------- ---------------------
    RULE_COMPONENT_ID NOT NULL NUMBER
    RULE_COMPONENT_CODE NOT NULL VARCHAR2(30)
    RULE_COMPONENT_NAME NOT NULL VARCHAR2(100)
    ENABLED_FLAG NOT NULL VARCHAR2(1)
    DB_TABLE VARCHAR2 (100)
    DB_COLUMN VARCHAR2 (100)
    DB_FUNCTION VARCHAR2(100)
    WHERE_CLAUSE VARCHAR2 (2000)
    FROM_CLAUSE VARCHAR2 (2000)

    The table contains the following data

    db_table
    --------------------------------
    sswms_shipment_lines
    wsh_carrier_ship_method --- the data inside the db_table column is a table

    db_column
    --------------------------------
    Ship to
    Carrier_id

    Where_clause
    -----------------------------
    Oe_order_headers_all.header_id = sswms_shipment_lines.order_header_id

    From_clause

    -------------------------

    Oe_order_headers_all, sswms_shipment_lines -- the data inside the From_clause is a table

    Now my requirement is to build a dynamic sql in forms 6i.when I click the build sql it should update the sql

    So --- I have to write a procedure --

    Select db_table || . || db_column || || group_key

    ---group_key is an alias

    From db_table, From_clause

    --- Here the logic should be

    1. I should remove the commas from the FROM_CLAUSE and check for duplicate values
    2. i should check for duplicate values for the DB_TABLE column
    3. I should compare both the FROM_CLAUSE and DB_TABLE for DUPLICATE VALUES
    4. After doing this I should add the result to the FROM in the select statement

    where where_clause

    hope this is clear.

    regards

    sudharshan

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Query - How To Access Properties Of The Report Region x9


    Hi all,

    I have dynamic query built by PL/SQL block. Number of columns is variable. Depending on the colum type I want to change column display properties. For example, I want to change alignment for text and numeric columns.

    Is there any way to access region properties during page rendering?

    TIA,
    Alex

    DB:2.94:Dynamic Query - How To Access Properties Of The Report Region x9

    Hi all,

    I have dynamic query built by PL/SQL block. Number of columns is variable. Depending on the colum type I want to change column display properties. For example, I want to change alignment for text and numeric columns.

    Is there any way to access region properties during page rendering?

    TIA,
    Alex

  • RELEVANCY SCORE 2.94

    DB:2.94:How: A Strongly-Typed Dynamic Cursor?? fx


    Heres what i want to do:

    - Define a RECORD type
    - Define a REF CURSOR based on this RECORD
    - Open the CURSOR with dynamic SQL in a storedProc
    - Return the CURSOR in an OUT proc param.

    I have several diff procs which all need to return the same record format, but based on different fields and with different criteria/params. I would like to be able to force them to return the same with strong typing off a RECORD. When i try this though, it tells me i can no longer open the cursor with dynamic sql.
    Is there anyway to do this? The query is built in the proc based on the IN params.

    Thanks in advance,
    - Aaron.

    DB:2.94:How: A Strongly-Typed Dynamic Cursor?? fx

    What version of Oracle are you on? Post what you tried.

    Padders

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Sql In Portlets dj


    Is this possible?

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Table Names Without Dynamic Sql? 3z


    I am trying to figure out a way to use table names as variables without using dynamic sql.

    So for instance, I have...

    execute immediate 'INSERT INTO '||vTABLE||' VALUES ('test')';
    and
    execute immediate 'INSERT INTO TABLE1 SELECT * FROM '||vTable;

    Is there any other way to do these without the dynamic sql in Oracle 10.2g?

    DB:2.94:Dynamic Table Names Without Dynamic Sql? 3z

    The ONLY way is with dynamic SQL.

    Your choices consist of using the DBMS_SQL package, NDS, or a weakly typed REF CURSOR.

    http://www.morganslibrary.org/reference/dbms_sql.html
    http://www.morganslibrary.org/reference/nds.html
    http://www.morganslibrary.org/reference/ref_cursors.html

  • RELEVANCY SCORE 2.94

    DB:2.94:Re: Dynamic Sql Is Not Working. Please Help. m8


    BTW, Why do you've dynamic SQLs here? Neither your table is dynamic nor your columns. You should be able to achieve it using static SQL statements.

    DB:2.94:Re: Dynamic Sql Is Not Working. Please Help. m8

    Thanks for the Tom link! I got what I wanted and learned something new and it is very helpful.

    ben

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Sql With Lowercase Table Names j7


    Greetings,

    DB:2.94:Dynamic Sql With Lowercase Table Names j7


    Thanks Lars...

    One additional thing we figured out was you can't select from a local table in the EXEC statement.

    Per the docs which I missed:

    One cannot use SQLScript variables in the SQL statement (but when constructing the SQL

    statement string).

    So we copied the SQLScript variable results to a HANA table then used the HANA table in the EXEC insert to write it to the ABAP table.

    One too many steps but it works and it's only being done once, not repeatedly.

    Thanks again.

  • RELEVANCY SCORE 2.94

    DB:2.94:Ssrs Query Designer Doesnt Support Dynamic Sql ? ak


    I got the error 'The Declare SQL construct or statement is not supported.' when I use dynamic sql,

    DB:2.94:Ssrs Query Designer Doesnt Support Dynamic Sql ? ak

    The tutorial shows how to use dynamic SQL in SSRS
    http://technet.microsoft.com/en-us/library/aa237477(v=sql.80).aspxval it: unit=()

  • RELEVANCY SCORE 2.94

    DB:2.94:How To Run Dynamic Sql From Forms. p9


    Hi,

    Could anybody tell me as to how to run dynamic SQl form forms 6i.

    Thanks

    DB:2.94:How To Run Dynamic Sql From Forms. p9

    But Mohammed:
    What I going to pass is the where statement for the updated proccess. Meaning that I will going to:
    1.- Create a Database Function or Procedure
    2.- Pass my where condition as you see in my previous e-mail.
    3.- Applied Dynamic SQL in PL/SQL. How this work?
    Thanks,

  • RELEVANCY SCORE 2.94

    DB:2.94:What Is Expected Error For Oracle Xa Application Built Without Ax_Reg ? pf


    Hi,
    I have an Oracle XA application running on a TPMonitor and it uses Oracle dynamic registration.
    I built the application without using TP-monitor libraries that define the symbols ax_reg and ax_unreg.
    While running that application on TPMonitor I got signal 11 in xaodynck function. If I build the application with TPMonitor library, it is working fine.

    I would like to know what should be the expected error if we build the application without libraries that define the symbols ax_reg and ax_unreg. Is it gives any SQL error for this case or the above error is expected ?

    DB:2.94:What Is Expected Error For Oracle Xa Application Built Without Ax_Reg ? pf

    Hi,
    I have an Oracle XA application running on a TPMonitor and it uses Oracle dynamic registration.
    I built the application without using TP-monitor libraries that define the symbols ax_reg and ax_unreg.
    While running that application on TPMonitor I got signal 11 in xaodynck function. If I build the application with TPMonitor library, it is working fine.

    I would like to know what should be the expected error if we build the application without libraries that define the symbols ax_reg and ax_unreg. Is it gives any SQL error for this case or the above error is expected ?

  • RELEVANCY SCORE 2.94

    DB:2.94:Difference Between Static Sql Query And Dynamic Sql Query. 8z


    Hi,

    Please explain the basic difference between static and dynamic sql queries. Please explain with example.

    DB:2.94:Difference Between Static Sql Query And Dynamic Sql Query. 8z

    Static: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/static.htm
    Dynamic: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/dynamic.htm

  • RELEVANCY SCORE 2.94

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


    Search for Dynamic SQL

    DB:2.94: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.94

    DB:2.94:Dynamic Sql In Report kf


    Hello
    Can i use dynamic sql in report builder , so as to allow user-defined reports .
    Thanx
    Shrikant

    DB:2.94:Dynamic Sql In Report kf

    read in the help system about lexical variables (these are variables with at the begining).
    So you can define a query as select P_1 where you get P_1 as a parameter.

  • RELEVANCY SCORE 2.94

    DB:2.94:Dynamic Sql Or Dbms_Sql 18


    hi all

    plse explain me what exactaly can use for perfomence dynamic sql or Dbms_Sql pkg

    Regards

    DB:2.94:Dynamic Sql Or Dbms_Sql 18

    Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime

    Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation

    For example, a reporting application in a data warehouse environment might not know the exact table name until runtime. These tables might be named according to the starting month and year of the quarter, for example INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.

    You might also want to run a complex query with a user-selectable sort order. Instead of coding the query twice, with different ORDER BY clauses, you can construct the query dynamically to include a specified ORDER BY clause.

    Dynamic SQL programs can handle changes in data definitions, without the need to recompile. This makes dynamic SQL much more flexible than static SQL. Dynamic SQL lets you write reusable code because the SQL can be easily adapted for different environments..

    Dynamic SQL also lets you execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.

  • RELEVANCY SCORE 2.92

    DB:2.92:Could We Use Dynamic Sql To Restric Row Numbers. pc



    Could we use Dynamic SQL to restric row numbers in Universe Designer?

    DB:2.92:Could We Use Dynamic Sql To Restric Row Numbers. pc


    I hope this helps as I'm not sure if it addresses your question exactly.

    You can restrict the number of rows if the database vendor provides SQL syntax for limiting rows to a specific count. In this case you could enter this SQL in a derived table. If you wanted to only restrict the row count on a certain set of tables you might consider restricting their total rowcount by enforcing a join to a derived table which applies the appropriate SQL syntax.

    Also, in the Universe Parameters dialog on the Controls tab (You can launch this dialog by clicking File | Parameters) you can set the row count by setting the "Limit size of result set to" option.

  • RELEVANCY SCORE 2.91

    DB:2.91:Dynamic Prompts In Crystal Reports 2008 mx



    I have a crystal report 2008 built out of stored procedure(Procedure is using SQL server 2008 database).

    there is a datetime parameter in stored procedure.when it is coming to crystal reports it is showing as datetime but when i change the parameter type to dynamic the paramter datetype is changing to string from datetime, i am not able to change that since it was disabled.

    I am changing that to dynamic because i would like to use LOV for that.

    Any suggestions on this?

    thanks!

    DB:2.91:Dynamic Prompts In Crystal Reports 2008 mx


    Call sales and get them to give you a link to the latest Service Pack - SP 5.

    Sales contact info:

    ph: 866-681-3435

    http://www.sap.com/contactsap/directory/index.epx

    - Ludek

    Senior Support Engineer AGS Product Support, Global Support Center Canada

    Follow us on Twitter

  • RELEVANCY SCORE 2.90

    DB:2.90:Processing Dynamic Query Returns a7


    I've used Scott Gu's Dynamic Linq library to process arbitrary queries built at runtime, and after a load of research, found the way to extract the actual data from the query (all examples just used it to generate a query which was bound to a Web Forms control,
    and there's no ToList() method available from his extension). The bit of code that yielded the data from the query was:

    // sql is a string that represents the query to run.
    // A generic list can be created with the usual ToList() method.
    IQueryables_Project projects =
    context.ExecuteQuerys_Project(sql)
    .AsQueryable();

    DB:2.90:Processing Dynamic Query Returns a7


    I cannot tack on ToList() to the end of this statement. Intellisense doesn't show it, and if I manually type it in, I get an error: 'System.Linq.IQueryable' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument
    of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?)

    The ToList extension method in my previous reply works fine on IQueryable here. Did you add dd it to a static class? If not, do that to make it available to your compiler.

    Cool tools for Linq-to-SQL and Entity Framework 4:
    huagati.com/dbmltools -
    Rule based class and property naming, Compare and Sync model = DB, Sync SSDL = CSDL (EF4)
    huagati.com/L2SProfiler -
    Query profiler for Linq-to-SQL and Entity Framework v4

  • RELEVANCY SCORE 2.90

    DB:2.90:Re: Dynamic Sql ck


    Maybe there's something wrong.

    DB:2.90:Re: Dynamic Sql ck

    Hi,I'm happy you solved your problem.If you column is a number I guess that the problem is the default decimal separator set to comma.You are concatenating a number with decimal to a string without formatting the output and your database is set to have decimal separator as comma.So when you implicit convert a number to a string will automatically gets comma as decimal separator.Your SQL statement does not recognize a comma as decimal separator so you need to convert it to a string using a comma separator.I think using bind variables as suggested by Stew is the best option.You can also reach your target by formatting the number before concatenating to a string:
    WHERE duration_modifiee = '||TO_CHAR(p_duration_modifiee, 'FM99999.9999');

    Just to give you an example after setting the session for having decimal separator as comma:
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";

    Session altered.

    SQL
    SQL select to_char(1.1,'FM999999.999999') format, to_char(1.1) no_format
    2 from dual;

    FORMAT NO_
    -------------- ---
    1.1 1,1

    Regards.Alberto

  • RELEVANCY SCORE 2.89

    DB:2.89:Database Driven Controls dk


    Hi All,
    We have a windows application built in VB.NET. I need to create controls on the fly meaning i have the name and type of control in a table in SQL Server. Based on that i need to create those type of controls. The reason is that the req changes often and
    hence we are looking to make it dynamic.
    So, when a user wants different controls based on a selection, we just put it in the table and no coding change needs to be done.
    I am not sure if this is feasible or not. I am actually a SQL DBA/Dev trying to do some coding and as we have dynamic sql, i was trying to see if this can be done in VB.NET
    Any help is much appreciated.
    Thanks,Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

    DB:2.89:Database Driven Controls dk


    Hi,
    That looks like exactly what i wanted. I am going to try implement this and see if this would work for me.
    I am also going to try saving complete code in the DB and use codeDomProvider to see if this can be accomplished.
    @Crazzypennine, Thanks for ur wonderful suggestion. If i have more question how do i contact you. I mean do you have ur own blog where i can post my question.
    Thanks again.

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.

    To reach me ... Just post on this thread, I check my threads several times a day ..

  • RELEVANCY SCORE 2.89

    DB:2.89:Native Dynamic Sql Within User Function 83


    I have a function that uses native dynamic sql doing inserts and updates. The table name is passed in as a parameter to the function and built into the sql statement using concatenation. The function compiles but fails at runtime with this error:

    ORA-14551 cannot perform a DML operation inside a query
    Cause: DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave.
    Action: Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

    However, when the exact same code is put into an anonymous PL/SQL block it runs perfectly.

    - Dana Reed

    DB:2.89:Native Dynamic Sql Within User Function 83

    I finally get it; thank you! The only remaining mystery is why this could be done with the old style dynamic sql using the dbms_sql package...

  • RELEVANCY SCORE 2.89

    DB:2.89:Can Create Table Statement Be Used In And Ssis Expresssion? fm


    I need to modify the preparation sql task built by the import wizard. I wanted to create an expression using the create table sql but insert the variable to append the date and another descriptor to make it dynamic. The source is Excel
    The expression would look something like this
    Create Table @user::descriptor @user::todaysdate ( name varchar(50) ...........
    SSIS cannot evaluate the sql although it runs fine in ssms
    The column mappings will always be the same but the table name needs to be dynamic

    DB:2.89:Can Create Table Statement Be Used In And Ssis Expresssion? fm

    Set up a variable using expression to have your query and then use this variable as the source in your execute SQL task or oledb destination.
    And you need to refer to the variables by using: @[User::todaysdate]

    Tweet me..

  • RELEVANCY SCORE 2.89

    DB:2.89:Native Dynamic Sql j7


    Hi all,
    What is meant by native dynamic SQL.???? How it is different from dbms_sql package???

  • RELEVANCY SCORE 2.89

    DB:2.89:Dynamic Sql From Table ff


    0

  • RELEVANCY SCORE 2.89

    DB:2.89:Re: Dynamic Sql 79


    Because the third param is an optional param hence the approach of using dynamic sql

    DB:2.89:Re: Dynamic Sql 79

    APC wrote:

    I have heard tell that in certain sectors of the galaxy the masters prefer to wield a leadpipe rather than a lightsabre....Yep.. in the tradition of the Great Miyamoto Musashi, who quickly fashioned wooden swords from oar to defeat his opponents wearing armour and using razor sharp swords, the "+enlightened+" Jedi does not need a lightsabre.. as piece of metal rod (made from a X-Wing stableliser strut) can do the job just as well.

    Or it could simple be that the sound of lead impacting flesh is more satisfying and there is no sickly odours of burned flesh afterwards?

    :D

  • RELEVANCY SCORE 2.89

    DB:2.89:Adf Faces: Default Value By Sql Statement For An Attribute md


    Hi,
    I'm working with JDeveloper 10.1.3.1.0.
    I have an updatable ViewObject and built an input form. For one attribute I want to have a default value. This value should be dynamic and generated by a SQL Statement.
    How can I do that? Thanks...

    DB:2.89:Adf Faces: Default Value By Sql Statement For An Attribute md

    Hi,
    unfortunately I have a new problem:
    I don't know, how I can apply the default value from the constructor of my managed bean to my attribute as the default value. I don't know, where and how I can bind it.
    Thank you once more...

  • RELEVANCY SCORE 2.89

    DB:2.89:Ddl Statment In Dynamic Sql 1p


    what privilege we need to give to the
    user for firing a DDL statment in dynamic sql

    DB:2.89:Ddl Statment In Dynamic Sql 1p

    Execute privilege on the procedure and Create table privelege should do good.

    I hope the user has privelges to connect to the database.

    Regards

    BLOCKQUOTEfont size="1" face="Verdana, Arial, Helvetica"quote:/fontHROriginally posted by shan:
    what privilege we need to give to the
    user for firing a DDL statment in dynamic sqlHR/BLOCKQUOTE

    null

  • RELEVANCY SCORE 2.89

    DB:2.89:Create Regions Dynamic (Via Sql)? sa


    hello is it possible to create regions dynamic via sql?

    DB:2.89:Create Regions Dynamic (Via Sql)? sa

    Not via SQL but via PL/SQL. See this example with an explanation:

    http://apex.oracle.com/pls/otn/f?p=31517:199

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.opal-consulting.de/training
    http://apex.oracle.com/pls/otn/f?p=31517:1
    -------------------------------------------------------------------

  • RELEVANCY SCORE 2.89

    DB:2.89:Address Book Dynamic Sql Link 8p



    Dose we need to have the Dynamic SQL Link / CRM Bridge license to create the Dynamic SQL Address Book Link

    DB:2.89:Address Book Dynamic Sql Link 8p


    Updated: You do need the Dynamic SQL lookup for a SQL address book.