`

Long to Varchar2 conversion

阅读更多

You Asked

Hi, 

Thanks for your earlier responses....

See, i have one more problem, like i want to retrive the first 4000 characters of the 
long datatype, with out using the pl sql code. i just wrote a function like

//

create or replace 
function getlong1( p_rowid in rowid)
return varchar2
  as
          l_data long;
  begin
         select remarks into l_data from cr_claimheader where rowid = p_rowid;
          return substr( l_data, 1, 4000 );
  end;

//

to get the first 4000 chars of remarks i am calling this function like 

//

select getlong1(rowid) from cr_claimheader where headersrno = 4005

//

Fine, this is working fine when i have the data with less than 4000 chars. in the 
particular remarks (long datatype) column.

suppose if the length of data increase means, it returns just null...

how can i solve this problem in a simple manner...

Its very urgent...

Thanks and bye

regards
Periasamy 

and we said...

In 8i and up, this will work:

ops$tkyte@ORA8I.WORLD> create or replace function  getlong( p_tname in varchar2,
  2                                       p_cname in varchar2,
  3                                       p_rowid in rowid ) return varchar2
  4  as
  5      l_cursor    integer default dbms_sql.open_cursor;
  6      l_n         number;
  7      l_long_val  varchar2(4000);
  8      l_long_len  number;
  9      l_buflen    number := 4000;
 10      l_curpos    number := 0;
 11  begin
 12      dbms_sql.parse( l_cursor,
 13                     'select ' || p_cname || ' from ' || p_tname ||
                                                          ' where rowid = :x',
 14                      dbms_sql.native );
 15      dbms_sql.bind_variable( l_cursor, ':x', p_rowid );
 16  
 17      dbms_sql.define_column_long(l_cursor, 1);
 18      l_n := dbms_sql.execute(l_cursor);
 19  
 20      if (dbms_sql.fetch_rows(l_cursor)>0)
 21      then
 22         dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
 23                                    l_long_val, l_long_len );
 24     end if;
 25     dbms_sql.close_cursor(l_cursor);
 26     return l_long_val;
 27  end getlong;
 28  /

Function created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> drop table t;

Table dropped.

ops$tkyte@ORA8I.WORLD> create table t ( x long );

Table created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> declare
  2    data long default rpad( '*', 32000, '*' );
  3  begin
  4    insert into t values ( data );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select getlong('T', 'X', rowid) from t;

GETLONG('T','X',ROWID)
------------------------------------------------------------------------------------------
-----------------------------------------
**************************************************.....


 
Reviews
5 starsThanks a lot June 21, 2001 - 3pm Central time zone
Reviewer:Periasamy Subramaniyanfrom Bulgaria
We planned to do some alteration... very good timely help...

Thanks a lot...

 


4 starsHow does this work... September 20, 2002 - 5am Central time zone
Reviewer:Subhrajyoti Paulfrom Mumbai, India
Tom,
 why does this happen...
pts@ptsmig.world> create table t1
  2  (x int, y long);

Table created.

pts@ptsmig.world> create table t2
  2  (x int, y varchar2(4000));

Table created.

pts@ptsmig.world> insert into t1 values(1,'abcde');

1 row created.

pts@ptsmig.world> commit;

Commit complete.

pts@ptsmig.world> insert into t2 select * from t1;
insert into t2 select * from t1
                      *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

pts@ptsmig.world> begin
  2  for a in (select * from t1) loop
  3     insert into t2 values(a.x, a.y);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

pts@ptsmig.world> select * from t2;

         X
----------
Y
--------------------------------------------------------------------------------
         1
abcde


When I am doing a Select-Insert, the datatype conversion gives an error but when i am using values 
clause, it works fine... Why does this happen...? 


Followup September 20, 2002 - 8am Central time zone:

because the documentation says it will?  LONGS cannot be used in an insert into.

Put more then 32k in the long and it'll fail miserably in plsql as well.

USE CLOBS, do not, repeat, do not, use a long.


 

4 starsBut Tom, what happens in this case, why PL/SQL succeeds in this case... September 20, 2002 - 8am Central time zone
Reviewer:Subhrajyoti Paul
Tom, 
    I have a table which was created with a long column and has around 1 M records in it... I want 
to modify the column type to long. In this process, I am saving the data into temp table with 
varchar2 column and modifying the main table. So, I have to do the conversion.

   I used the loop to do that.

   I just wanted to know why this failed in PURE SQL while it worked in PL/SQL loop....


Thanks

 


4 starsCorrection in the last review September 20, 2002 - 8am Central time zone
Reviewer:Subhrajyoti Paul
Sorry, 
  I want to modify the column type to VARCHAR2, not long as I wrongly wrote in the previous 
review... 


5 starsExcellent work February 5, 2003 - 7am Central time zone
Reviewer:Chandra S.Reddyfrom India
Great stuff from very great Tom. 


5 starsIs this againest to the coding Standards. February 11, 2003 - 5am Central time zone
Reviewer:Sindhufrom Alaska
Tom,
Thanks for the code you provided.
In one situation I have to send a value from LONG datatyped column to application through Stored 
Proc as a OUT parameter.

SQL> desc t_comment
 Name                                 Null?    Type
 ----------------------------------------------------- -----
T_NUMBER                             NOT NULL CHAR(13)
USER_COMMENT                                  LONG

In a stored Proc I implemented like.

procedure sp_xx(in_cTNo IN NUMBER, l_var OUT VARCHAR2) IS

select sf_pc_cmt_FNC(in_cTNo) into l_var from t_comment;

end;

the function "sf_pc_cmt_FNC" is exactly look like the code snippet you provided.
But when I implemented this snippent, My client refused to accept this.Below are the client's 
comments.
<<< his comments >>>
" I think that there is a misunderstand information for " item #10:Use Oracle Packages and 
functions for generalized common routines frequently executed"
in the Guideline for Oracle Database Application Development for PL/SQL Stored Procedures document. 
  The dbms_sql package can be directory access
by the store procedure if the code related to the reference tables and it is not heavy accessing by 
the applications.    
We have to discuss the correct the usage of the dbms_sql package. "
<<<<>>>>

The item#10 is below.
.     Use Oracle packages and functions for generalized common routines frequently executed
since code is efficient.  Also, use user-written packages and functions for application shared 
code. 
Before you write a user-written package or function, first verify it doesn’t already exist
with Oracle.   One good reason to do this is because when Oracle upgrades to a new release they 
upgrade their own packages and functions for you.   If you write them yourself, you may have more 
maintenance when converting to a new release.
< item #10 over...>

Is Oracle providing any package(DBMS_?) for this conversion.
Please let me know the way to resolve this and why client is refused to accept this SP.

Thanks in advance.
 


Followup February 11, 2003 - 8am Central time zone:

I don't understand this at all -- sorry?  

What exactly is their issue? 


My routine returns just the first 4000 characters of a long --regardless of how long the long is.

IF your longs are all 32k or less your code should be:

is
   l_tmp long;
begin
   select long_col into l_tmp from t_comment where ...;
   return substr( l_tmp, 1, 4000 );
end;

if they EXCEED 32k for just one row -- you have to use dbms_sql in plsql  

3 starsConverting Long to Varchar2 with minimal redo/undo. June 24, 2003 - 12am Central time zone
Reviewer:A readerfrom Japan
Hi Tom, What method would you suggest for converting a long column to varchar2. Our table has about 
20 million rows in it. We want to generate least number of archive logs for this. Sqlplus copy 
command and PL/SQL insert cannot be used as we cannot use APPEND hint with them(We tried creating a 
new table and then inserting into it,we had planed to rename the original table.We plan to create 
index's with nologging after the table is created.)

The orginal table and the new table are as given below.
CREATE TABLE AD_MESSAGES
 (MSG_NUM  NUMBER(5) DEFAULT 0 NOT NULL
 ,MSG_CONTENT  LONG NOT NULL
 ,MSG_TS  DATE NOT NULL
 ,AD_DOC_DOC_NUM  NUMBER(5) NOT NULL
 ,AD_DOC_ADC_CONTAINER_NUM  NUMBER(12) NOT NULL
 ,AML_MSG_LEVEL  VARCHAR2(10) NOT NULL
 )
 TABLESPACE AD_MESSAGES
/


CREATE TABLE TEMP_AD_MESSAGES
 (MSG_NUM  NUMBER(5) DEFAULT 0 NOT NULL
 ,MSG_CONTENT  VARCHAR2(4000) NOT NULL
 ,MSG_TS  DATE NOT NULL
 ,AD_DOC_DOC_NUM  NUMBER(5) NOT NULL
 ,AD_DOC_ADC_CONTAINER_NUM  NUMBER(12) NOT NULL
 ,AML_MSG_LEVEL  VARCHAR2(10) NOT NULL
 )
 TABLESPACE AD_MESSAGE
/

ALTER TABLE TEMP_AD_MESSAGES NOLOGGING
/

DECLARE
   CURSOR cur_msg IS
   SELECT msg_num
      ,msg_content
          ,msg_ts
          ,ad_doc_doc_num
          ,ad_doc_adc_container_num
          ,aml_msg_level
     FROM AD_MESSAGES;
   --
   ln_msg_num        TEMP_AD_MESSAGES.MSG_NUM%TYPE;
   lc_msg_content    TEMP_AD_MESSAGES.MSG_CONTENT%TYPE;
   lc_msg_ts         TEMP_AD_MESSAGES.MSG_TS%TYPE;
   ln_doc_num        TEMP_AD_MESSAGES.AD_DOC_DOC_NUM%TYPE;
   ln_container_num  TEMP_AD_MESSAGES.AD_DOC_ADC_CONTAINER_NUM%TYPE;
   lc_msg_lvl        TEMP_AD_MESSAGES.AML_MSG_LEVEL%TYPE;
   --
   ln_count          NUMBER(20) := 0;
   ln_step           NUMBER(10) := 0;
BEGIN
   ln_step := 1;
   OPEN cur_msg;
   LOOP
   ln_step := 2;
   FETCH cur_msg INTO ln_msg_num,lc_msg_content,lc_msg_ts,ln_doc_num,ln_container_num,lc_msg_lvl;
   EXIT WHEN cur_msg%NOTFOUND;
   ln_step := 3;
   INSERT /*+ APPEND */ INTO TEMP_AD_MESSAGES
                           (MSG_NUM
               ,MSG_CONTENT
               ,MSG_TS
               ,AD_DOC_DOC_NUM
               ,AD_DOC_ADC_CONTAINER_NUM
               ,AML_MSG_LEVEL)
                   VALUES
                           (ln_msg_num
                            ,lc_msg_content
                            ,lc_msg_ts
                            ,ln_doc_num
                            ,ln_container_num
                            ,lc_msg_lvl);

   ln_step := 4;
   IF ln_count > 100000 THEN
      COMMIT;
      ln_count := 0; 
   END IF; 
   ln_step := 5;

   ln_count := ln_count + 1;

   END LOOP;
   ln_step := 6;
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN 
      dbms_output.put_line('Error in script. '||SQLERRM ||'ln_step '||ln_step);
END;

This is generating heavy archive logs, as APPEND is ignored with 'values' clause of insert.

What would be your suggestion for this ?

Thanks, 


Followup June 24, 2003 - 7am Central time zone:

there will be no way to do this without unloading the data and then using sqlldr direct path to 
reload it or writing an OCI program in C using the direct path apis.

create table as select --> won't work, longs.
insert /*+ append */ as select --> won't work, longs.
copy -> won't work, conventional path.
code you write -> won't work, conventional path.

so, maybe you can unload to a flat file (see asktom.oracle.com/~tkyte for unloaders) and reload 
with sqlldr 

4 starsuse a function to return a varchar2 June 24, 2003 - 9am Central time zone
Reviewer:A readerfrom Japan
Hi Tom,

Do you see any problem, if we create a function which takes a rowid parameter and then selects the 
long column into a varchar2 variable from the rowid and returns this varchar2 variable. After this 
we just use this function in the insert statement.
For eg. insert /*+ APPEND */ into TEMP_AD_MESSAGES select MSG_NUM
,my_to_varchar_func(rowid),MSG_TS       ,AD_DOC_DOC_NUM,AD_DOC_ADC_CONTAINER_NUM,AML_MSG_LEVEL from 
ad_messages;

We know that our long field has less than 2000 bytes of length. In test case atleast its appearing 
to use direct mode insert.

Thanks,
 


Followup June 25, 2003 - 9am Central time zone:

It would seem to me that in order to use append, you must be worried about gobs of UNDO.

If you are worried about gobs of undo -- you must have TONS of data.

If you have tons of data, calling a function once per row is going to be slow, I'd rather deal with 
the UNDO. 

4 starsHow to deal with long for this query June 24, 2003 - 6pm Central time zone
Reviewer:Yongfrom CA, USA
Tom,

Search_condition is long type in Oracle data dictionary. How can I construct a query to get the 
following results without set a condition on a long type

SQL> select constraint_name from user_constraints
  2  where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
      *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

 


Followup June 25, 2003 - 11am Central time zone:

ops$tkyte@ORA920LAP> create or replace function get_search_condition( p_cons_name in varchar2 ) 
return varchar2
  2  authid current_user
  3  is
  4      l_search_condition user_constraints.search_condition%type;
  5  begin
  6      select search_condition into l_search_condition
  7        from user_constraints
  8       where constraint_name = p_cons_name;
  9
 10      return l_search_condition;
 11  end;
 12  /

Function created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select constraint_name
  2    from user_constraints
  3   where get_search_condition(constraint_name) like '%NOT NULL%';

CONSTRAINT_NAME
------------------------------
SYS_C004792
SYS_C004794
SYS_C004181
....

 

5 stars June 25, 2003 - 4pm Central time zone
Reviewer:A reader
Tom,

Great! thanks, 


3 starsI get error when I try to use your function January 15, 2004 - 3pm Central time zone
Reviewer:Sonali Kelkarfrom waltham, ma
When I do this I get error
create or replace function get_text( p_VIEW_NAME in varchar2 ) 
return varchar2 
    is
        l_text user_views.text%type;
    begin
        select text into l_text
          from user_views
         where view_name = p_VIEW_NAME;
  
       return l_text;
   end;
   /
show errors


select view_name
      from user_views
     where get_text(view_name) like '%Company_ID%';


ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "xyz.GET_TEXT", line 10


 


Followup January 16, 2004 - 12am Central time zone:

well -- a major issue with this is that varchar2 is always limited to 4,000 characters in SQL -- 
returning a view > 4,000 characters is going to blow up here.

You would have to:

where do_a_like_on_view_text( view_name, '%Company_ID%' ) > 0


and code more like this:
is
   return_val number := 0;
begin
   for x in ( select text ... )
   loop
      if ( x.text like p_inputs ) 
      then
          return_val :=1;
      end if;
   end loop;
   return return_val;
end; 

5 starshow to convert from long datatype to varchar April 21, 2004 - 4am Central time zone
Reviewer:diwakarfrom Malaysia
if i want to put like in long data type it gives error so 
how to do it...
thanks
 


Followup April 21, 2004 - 8pm Central time zone:

look up one review where I show you that you need to use a function. 

1 starsCONVERSION FROM LONG TO VARCHAR@ April 21, 2004 - 11pm Central time zone
Reviewer:diwakarfrom Malaysia
Hi,
  I have seen ur function but it also gives the following error:-


select distinct trigger_name from user_triggers
where FNC_getlong(trigger_name,'%BATCH%') > 0;
where FNC_getlong(trigger_name,'%BATCH%') > 0
      *
ERROR at line 2:
ORA-20001: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SEALINER_PH2B.FNC_GETLONG", line 20
ORA-06512: at line 1

As u can see i'm still getting error
and the function is
CREATE OR REPLACE FUNCTION Fnc_Getlong( p_tRG_name IN VARCHAR2,p_inputs VARCHAR2)
RETURN NUMBER
IS
   return_val NUMBER := 0;
BEGIN
   FOR x IN ( SELECT trigger_body FROM user_triggers WHERE trigger_name =p_tRG_name )
   LOOP

      IF ( x.trigger_body LIKE p_inputs )
      THEN
          return_val :=1;
      END IF;



   END LOOP;
   RETURN return_val;
EXCEPTION
  WHEN OTHERS THEN
       RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;
/
can u pls help me in this
Thanks in Advance
 


Followup April 22, 2004 - 7am Central time zone:

is your long >32k 

2 starsconversion from long to varchar2 April 22, 2004 - 10pm Central time zone
Reviewer:diwakar
Yes it is greater than 32K 


Followup April 23, 2004 - 11am Central time zone:

see above:

.....
IF your longs are all 32k or less your code should be:

is
   l_tmp long;
begin
   select long_col into l_tmp from t_comment where ...;
   return substr( l_tmp, 1, 4000 );
end;

if they EXCEED 32k for just one row -- you have to use dbms_sql in plsql 
................


however if you have need to search a long, you really ought be looking at a text index:

create index t_idx on t(long_column) indextype ctxsys.context;

and then

select * from t where contains( long_column, 'foobar' ) > 0;


 

5 starsAwesome April 15, 2005 - 8pm Central time zone
Reviewer:Paul Ramosfrom Santa Barbara, CA USA
Your team rules!!! This site is the cure for the common headache. 


4 starsconvert LONG to Varchar2 - THANKS! May 11, 2005 - 1am Central time zone
Reviewer:A.Bozrikovfrom Lemesos, Cyprus
I had to convert a dozen of tables having LONG columns (for no reason, as all strings in LONG 
columns were shorter than 2k!) to VARCHAR2 columns. Tom Kyte's suggestions to others were very 
helphul, thanks! I did not even have to ask anything. 


4 starssearch long July 15, 2005 - 5am Central time zone
Reviewer:A reader
Hi

The procedure you used to find search_condition in user_constrains works fine. 
Is there a way to build a generic procedure/function to search long columns?

Such as

select *
from user_views
where search_long(text) like '%XXX%'


select *
from user_constraints
where search_long(search_condition) like '%XXX%'

THANKS 


Followup July 15, 2005 - 8am Central time zone:

Here is an excerpt from the book I'm working on now that deals with this question:

<quote>

A question that arises frequently however is - what about the data dictionary in Oracle?  It is 
littered with LONG columns and this makes using them (the dictionary columns) problematic.  For 
example, to find all VIEWS that contain the text 'HELLO' is not possible:

ops$tkyte@ORA10G> select *
  2  from all_views
  3  where text like '%HELLO%';
where text like '%HELLO%'
      *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

This is not limited to just the ALL_VIEWS view, there are many views:

ops$tkyte@ORA10G> select table_name, column_name
  2  from dba_tab_columns
  3  where data_type in ( 'LONG', 'LONG RAW' )
  4  and owner = 'SYS'
  5  and table_name like 'DBA%';
 
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DBA_VIEWS                      TEXT
DBA_TRIGGERS                   TRIGGER_BODY
DBA_TAB_SUBPARTITIONS          HIGH_VALUE
DBA_TAB_PARTITIONS             HIGH_VALUE
DBA_TAB_COLUMNS                DATA_DEFAULT
DBA_TAB_COLS                   DATA_DEFAULT
DBA_SUMMARY_AGGREGATES         MEASURE
DBA_SUMMARIES                  QUERY
DBA_SUBPARTITION_TEMPLATES     HIGH_BOUND
DBA_SQLTUNE_PLANS              OTHER
DBA_SNAPSHOTS                  QUERY
DBA_REGISTERED_SNAPSHOTS       QUERY_TXT
DBA_REGISTERED_MVIEWS          QUERY_TXT
DBA_OUTLINES                   SQL_TEXT
DBA_NESTED_TABLE_COLS          DATA_DEFAULT
DBA_MVIEW_ANALYSIS             QUERY
DBA_MVIEW_AGGREGATES           MEASURE
DBA_MVIEWS                     QUERY
DBA_IND_SUBPARTITIONS          HIGH_VALUE
DBA_IND_PARTITIONS             HIGH_VALUE
DBA_IND_EXPRESSIONS            COLUMN_EXPRESSION
DBA_CONSTRAINTS                SEARCH_CONDITION
DBA_CLUSTER_HASH_EXPRESSIONS   HASH_EXPRESSION
 
23 rows selected.

that are affected by this.  So, what is the solution?  If you want to make use of these columns in 
SQL - we'll need to convert them to a SQL friendly type.  We can use a user defined function for 
doing so.  This demonstrates how to accomplish a "long substr" function that will allow you to 
effectively convert any 4000 bytes of a LONG type into a varchar2, for use with SQL.  When we are 
done, we'll be able to query:

ops$tkyte@ORA10G> select *
  2    from (
  3  select owner, view_name,
  4         long_help.substr_of( 'select text
  5                                 from dba_views
  6                                where owner = :owner
  7                                  and view_name = :view_name',
  8                               1, 4000,
  9                               'owner', owner,
 10                               'view_name', view_name ) substr_of_view_text
 11    from dba_views
 12   where owner = user
 13         )
 14   where upper(substr_of_view_text) like '%INNER%'
 15  /

Meaning, we converted the first 4000 bytes of the VIEW_TEXT column from LONG to a VARCHAR2 and can 
now use a predicate on it.  Using the same technique, you would be able to implement your own 
instr, like and such for LONG types as well.  In this book, I'll only be demonstrating how to get 
the substring of a LONG type.

The package we will implement has the following specification:

ops$tkyte@ORA10G> create or replace package long_help
  2  authid current_user
  3  as
  4      function substr_of
  5      ( p_query in varchar2,
  6        p_from  in number,
  7        p_for   in number,
  8        p_name1 in varchar2 default NULL,
  9        p_bind1 in varchar2 default NULL,
 10        p_name2 in varchar2 default NULL,
 11        p_bind2 in varchar2 default NULL,
 12        p_name3 in varchar2 default NULL,
 13        p_bind3 in varchar2 default NULL,
 14        p_name4 in varchar2 default NULL,
 15        p_bind4 in varchar2 default NULL )
 16      return varchar2;
 17  end;
 18  /
Package created.

Note that one line 2, we've specified AUTHID CURRENT_USER.  This makes the package run as the 
invoker, with all roles and grants in place.  This is important for two reasons.  Firstly, we'd 
like the database security to no be subverted - this package will only return substrings of columns 
you (the invoker) is allowed to see.  Secondly, we'd like to install this package once in the 
database and have its functionality available for all to use - using invokers rights allows us to 
do that.  If we used the default security model of PLSQL, definer rights, the package would run 
with the privileges of the owner of the package.  Meaning it would only be able to see data the 
owner of the package could see - which does not include the set of data the invoker is allowed to 
see.

The concept behind the function SUBSTR_OF is to take a query that selects at most one row and one 
column - the LONG value we are interested in.  SUBSTR_OF will parse that query if needed, bind any 
inputs to it and fetch the results programmatically, returning the necessary piece of the LONG 
value.
The package body, the implementation, begins with two global variables.  The G_CURSOR variable 
holds a persistent cursor open for the duration of our session.  This is to avoid having to 
repeatedly open and close the cursor and to avoid parsing SQL more than we need to.  The second 
global variable, G_QUERY, is used to remember the text of the last SQL query we've parsed in this 
package.  As long as the query remains constant, we'll just parse it once.  So, even if we query 
5,000 rows in a query - as long as the SQL query we pass to this function doesn't change, we'll 
only have one parse call:

ops$tkyte@ORA10G> create or replace package body long_help
  2  as
  3
  4      g_cursor number := dbms_sql.open_cursor;
  5      g_query  varchar2(32765);
  6

Next in this package is a private  function, BIND_VARIABLE, that we'll use to bind inputs passed to 
us by the caller.  We implemented this as a separate private procedure only to make life easier - 
we want to bind only when the input name is NOT NULL.  Rather than perform that check 4 times in 
the code for each input parameter - we do it once in this procedure:

  7  procedure bind_variable( p_name in varchar2, p_value in varchar2 )
  8  is
  9  begin
 10      if ( p_name is not null )
 11      then
 12          dbms_sql.bind_variable( g_cursor, p_name, p_value );
 13      end if;
 14  end;
 15

Next is the actual implementation of SUBSTR_OF in the package body, it begins with function 
declaration from the package specification and the declaration for some local variables.  L_BUFFER 
will be used to return the value and L_BUFFER_LEN will be used to hold the length returned by an 
Oracle supplied function:

 16
 17  function substr_of
 18  ( p_query in varchar2,
 19    p_from  in number,
 20    p_for   in number,
 21    p_name1 in varchar2 default NULL,
 22    p_bind1 in varchar2 default NULL,
 23    p_name2 in varchar2 default NULL,
 24    p_bind2 in varchar2 default NULL,
 25    p_name3 in varchar2 default NULL,
 26    p_bind3 in varchar2 default NULL,
 27    p_name4 in varchar2 default NULL,
 28    p_bind4 in varchar2 default NULL )
 29  return varchar2
 30  as
 31      l_buffer       varchar2(4000);
 32      l_buffer_len   number;
 33  begin

Now, the first thing our code does is a sanity check on the P_FROM and P_FOR inputs.  P_FROM must 
be a number greater than or equal to 1 and P_FOR must be between 1 and 4000 - just like the 
built-in function SUBSTR:

 34      if ( nvl(p_from,0) <= 0 )
 35      then
 36          raise_application_error
 37          (-20002, 'From must be >= 1 (positive numbers)' );
 38      end if;
 39      if ( nvl(p_for,0) not between 1 and 4000 )
 40      then
 41          raise_application_error
 42          (-20003, 'For must be between 1 and 4000' );
 43      end if;
 44

Next, we'll check to see if we are getting a new query that needs to be parsed.  If the last query 
we parsed is the same as the current query - we can skip this step.  It is very important to note 
that on line 47 we are verifying that the P_QUERY passed to us is just a SELECT - we will use this 
package only to execute SQL SELECT statements, this check validates that for us:

 45      if ( p_query <> g_query or g_query is NULL )
 46      then
 47          if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')
 48          then
 49              raise_application_error
 50              (-20001, 'This must be a select only' );
 51          end if;
 52          dbms_sql.parse( g_cursor, p_query, dbms_sql.native );
 53          g_query := p_query;
 54      end if;

Now we are ready to bind the inputs to this query.  Any non-NULL names that were passed to us will 
be "bound" to the query so when we execute it, it finds the right row:

 55      bind_variable( p_name1, p_bind1 );
 56      bind_variable( p_name2, p_bind2 );
 57      bind_variable( p_name3, p_bind3 );
 58      bind_variable( p_name4, p_bind4 );
 59

And now we are ready to execute the query and fetch the row.  Then using 
DBMS_SQL.COLUMN_VALUE_LONG, we extract the necessary substring of the long and return it:

 60      dbms_sql.define_column_long(g_cursor, 1);
 61      if (dbms_sql.execute_and_fetch(g_cursor)>0)
 62      then
 63          dbms_sql.column_value_long
 64          (g_cursor, 1, p_for, p_from-1,
 65           l_buffer, l_buffer_len );
 66      end if;
 67      return l_buffer;
 68  end substr_of;
 69
 70  end;
 71  / 
Package body created.

And that is it, you should be able to use that package against any legacy LONG column in your 
database, allowing you to perform many "where clause" operations that were not possible before, for 
example, to find all partitions in your schema such that the HIGH_VALUE has the year 2003 in it:

ops$tkyte@ORA10G> select *
  2    from (
  3  select table_owner, table_name, partition_name,
  4         long_help.substr_of
  5         ( 'select high_value
  6              from all_tab_partitions
  7             where table_owner = :o
  8               and table_name = :n
  9               and partition_name = :p',
 10            1, 4000,
 11            'o', table_owner,
 12            'n', table_name,
 13            'p', partition_name ) high_value
 14    from all_tab_partitions
 15   where table_name = 'T'
 16     and table_owner = user
 17         )
 18   where high_value like '%2003%'
 19  /
 
TABLE_OWN TABLE PARTIT HIGH_VALUE
--------- ----- ------ ------------------------------
OPS$TKYTE T     PART1  TO_DATE(' 2003-03-13 00:00:00'
                       , 'SYYYY-MM-DD HH24:MI:SS', 'N
                       LS_CALENDAR=GREGORIAN')
 
OPS$TKYTE T     PART2  TO_DATE(' 2003-03-14 00:00:00'
                       , 'SYYYY-MM-DD HH24:MI:SS', 'N
                       LS_CALENDAR=GREGORIAN')

Using this same technique - that of processing the result of a query that returns a single row with 
a single LONG column in a function - you can implement your own INSTR, LIKE and so on as needed.  

This implementation works well on the LONG type but will not work on LONG RAW types.  LONG RAWs are 
not piecewise accessible (there is no COLUMN_VALUE_LONG_RAW function in DBMS_SQL).  Fortunately, 
this is not too serious of a restriction since LONG RAWs are not used in the dictionary and the 
need to "substring" so you can search on it is rare.  If you do have a need to do so however, you 
will not be using PLSQL unless the LONG RAW is 32k or less, there is simply no method for dealing 
with LONG RAWS over 32k in PLSQL itself.  Java, C, C++, Visual Basic or some other language would 
have to be used.
Another approach would be to temporarily convert the LONG or LONG RAW into a CLOB or BLOB using the 
TO_LOB built in function and a global temporary table.  Your PLSQL procedure could:

Insert into global_temp_table ( blob_column ) 
select to_lob(long_raw_column) from t where

This would work well in an application that occasionally needed to work with a single LONG RAW 
value - you would not want to be continuously doing that however due to the amount of work 
involved.  If you find yourself needing to resort to this technique frequently, you would 
definitely convert the LONG RAW to a BLOB once and be done with it.
</quote> 

5 starsVery Useful August 29, 2005 - 10pm Central time zone
Reviewer:A reader


3 starsUpdating a value in a long column November 18, 2005 - 1pm Central time zone
Reviewer:Ajums T Tfrom Mumbai, India
Hi Tom,
We have a table with a long column datatype. In this long column datatype there are several 
instances of the word "Bombay". 
We have to replace all instances of "Bombay" with "Mumbai".

There are over 15000 such records and its painful to do this one at a time through the front-end. 
Can we write a PL/SQL block to do this at one go? 


Followup November 18, 2005 - 3pm Central time zone:

how long are the longs? 

3 stars November 18, 2005 - 1pm Central time zone
Reviewer:Ajums TT
We are using Oracle 8.1.7. I forgot to mention that earlier.

Regards,
Ajums TT 


3 stars November 19, 2005 - 12pm Central time zone
Reviewer:Ajums TTfrom mumbai, india
The longs are about 5 pages of text in some of the rows. In others where its less than 4000 bytes, 
I wrote a piece of code in VB and that worked for them. For this 5 pages of text, there are 3 rows 
that still need to be updated. 


Followup November 19, 2005 - 1pm Central time zone:

If "5 pages" is less then 32k - you can use plsql.


begin
   for x in ( select * from t ) 
   loop
       x.long_col := replace( x.long_col, .... )
       update t set long_col = x.long_col where ....
   end loop;


I've no idea what "5 pages" is though. 

3 starsWhat is 5 pages. November 19, 2005 - 1pm Central time zone
Reviewer:Ajums TTfrom Mumbai, India
5 pages is a printout on A4 size paper.
I am certain that one of the rows has crossed 32 K in size.

 


Followup November 19, 2005 - 2pm Central time zone:

that still doesn't tell me how big 5 pages is :)

but if they are over 32k in size, you need something beyond SQL and PLSQL to deal with them.   

4 starsworked for 4 rows November 19, 2005 - 4pm Central time zone
Reviewer:Ajums TTfrom Mumbai, India
Hi Tom,
Your technique has updated all but 1 row in my table. I am certain its more than 32 K of data in 
this particular row.

If I need something beyond Pl /SQL or SQL to fix this, where should I be looking at?

I have been having a lot of sleepless nights for some time now.

Regards,
Ajums TT 


Followup November 19, 2005 - 8pm Central time zone:

look to the application that puts it into the database in the first place???

what is it written in. 

4 starsExplicitly checking for SELECT instead of pragma restrict_references(WNDS' November 20, 2005 - 4am Central time zone
Reviewer:Jagjeet Singhfrom Delhi, India
Hi,

in Long_help package you are checking explicitly checking whether this text is "SELECT" or not. 

Any specific reason for not using  

Pragma restrict_reference(....,WNDS);


Js 


Followup November 20, 2005 - 8am Central time zone:

because you haven't needed to use restrict references for many releases, so I don't bother. 

5 starsVarchar to Nvarachar migration November 22, 2005 - 12am Central time zone
Reviewer:thirumaranfrom INDIA
Hi Tom, 

Is it possible to move data from a varchar2 datatype in Oracle 9i solaris Server to an NVARACHAR2 
oracle 10g R2 Windows 2003 server datatype.

Oracle 9i is using the default oracle character set whereas the proposed Oracle 10g R2 Database 
character set will be AL32UTF8 sets and the National character set will be UTF – 16 while creating 
the database.


Thanks in adv
Thirumaran



Thanks in adv
Thirumaran. 


Followup November 22, 2005 - 8am Central time zone:

yes it is. 

5 starsChar to Nvarchar conversion November 22, 2005 - 2am Central time zone
Reviewer:Banufrom INDIA
Dear Tom,

1) Can i move my Table columns data from Char to NVarchar datatype. Examples are available only 
form CHAR to NCHAR .

2) What is the difference between unicode units VS Bytes.
i am not clear with the text from this site:
http://www.cs.umb.edu/cs634/ora9idocs/server.920/a96529/ch2.htm#104327
Oracle9i Database Globalization Support Guide
Release 2 (9.2)
Part Number A96529-01

"When the NCHAR character set is AL16UTF16, ename can hold up to 10 Unicode code units. When the 
NCHAR character set is AL16UTF16, ename can hold up to 20 bytes."

please illustrate with examples.

3)i have a DB with western alphabets(9i) when i move data to a DB which has different Database 
character set(AL32UTF8) & National character set(UTF-16) what are the impacts ? please guide me on 
this.

regards
Banu

 


Followup November 22, 2005 - 8am Central time zone:

1) yes.

2) basically it is saying that unicode takes one OR MORE bytes to store a single character, it is a 
multi-byte encoding scheme.  

when you say "varchar2(20)", you get 20 bytes - it might be able to hold 20 characters, then again 
- maybe not.


3) you'll be going from single byte per character to multi-byte.  You might find that 40 characters 
no longer fits in the varchar2(40) which is 40 bytes as 40 characters might need 80, 120, 160 or 
even more bytes.

 

3 starsconverting LONG to NUMBER datatype November 25, 2005 - 6am Central time zone
Reviewer:Suvendufrom Bangalore, INDIA
Hi Tom,
Here my problem with high_value column in user_tab_partitions not with the query. If my query is 
written wrong way, could you please correct me here.

The scenarion is like:
When I'm getting low range value partition to an existing partition table, I wants find out where 
this new partition going to fit in between existing two partitions and after getting it, I need to 
:
Split the highest bound partition with new partition key value.

To find out same here is my query, but BETWEEN ..AND .. clause going fail on LONG column 
high_value, I tried with utl_raw package relating this thread along with CAST( high_value AS 
NUMBER) option too, but not getting solved the problem.
 
Could you, please provide any good option to do same, if any and to solve the problem here too?

SQL> select lower.partition_name,
       lower.high_value, 
       higher.partition_name,
       higher.high_value  
from (select partition_name, high_value from user_tab_partitions 
      where table_name='FACT_TAB') lower,
     (select partition_name, high_value from user_tab_partitions 
      where table_name='FACT_TAB') higher
where lower.partition_name=higher.partition_name
and 1117324802 between lower.high_value and higher.high_value;

ORA-00997: illegal use of LONG datatype


It's in Oracle 9.2 on HP Unix.

Thanking you a lot for your kind consideartion to my question.

Regards,
Suvendu


 


4 stars10g DATE datatypes vs. 8i DATE datatypes March 15, 2006 - 6pm Central time zone
Reviewer:Sinan Topuzfrom NYC USA
Tom,

The following package was working well on 8i. I migrated it to 10g and I got an error message 
"ORA-00932:inconsistent datatypes: expected - got -".

Could you explain me what this means please?

Thanks in advance.
Sinan

VIEW
----
Name                  Type          
--------------------- ------------- 
SH_HEADER_ID          VARCHAR2(30)                    
SH_DATE               DATE                            
SH_SERIAL             VARCHAR2(2)                     
SH_NUMBER             VARCHAR2(30)                    
SH_STATUS             VARCHAR2(2)                     
SH_TYPE               VARCHAR2(3)                     
SH_CUST_ID            VARCHAR2(30)                    
SH_CUST_PHONE         VARCHAR2(30)                    
SH_PO_NUMBER          VARCHAR2(30)                    
SH_DEPT_CODE          VARCHAR2(10)                    
SH_STORE_CODE         VARCHAR2(10)                    
SH_TERMS_CODE         VARCHAR2(5)                     
SH_SHIPMENT_TYPE      VARCHAR2(30)                    
SH_SHIPTO_ATTN        VARCHAR2(100)                   
SH_SHIPTO             VARCHAR2(100)                   
SH_SHIPTO_ADDRESS1    VARCHAR2(100)                   
SH_SHIPTO_ADDRESS2    VARCHAR2(100)                   
SH_SHIPTO_CITY        VARCHAR2(100)                   
SH_SHIPTO_STATE       VARCHAR2(5)                     
SH_SHIPTO_ZIP         VARCHAR2(15)                    
SH_SHIPTO_COUNTRY     VARCHAR2(30)                    
SH_SHIPTO_PHONE       VARCHAR2(30)                    
SH_SHIPTO_FAX         VARCHAR2(30)                    
SH_BILLTO             VARCHAR2(100)                   
SH_BILLTO_ADDRESS1    VARCHAR2(100)                   
SH_BILLTO_ADDRESS2    VARCHAR2(100)                   
SH_BILLTO_CITY        VARCHAR2(100)                   
SH_BILLTO_STATE       VARCHAR2(5)                     
SH_BILLTO_ZIP         VARCHAR2(15)                    
SH_BILLTO_COUNTRY     VARCHAR2(30)                    
SH_BILLTO_PHONE       VARCHAR2(30)                    
SH_BILLTO_FAX         VARCHAR2(30)                    
SH_SALESPERSON        VARCHAR2(30)                    
SH_STARTDATE          DATE                            
SH_CANCELDATE         DATE                            
SH_SPECIALINSTRUCTION VARCHAR2(254)                   
SH_ENTEREDBY          VARCHAR2(30)                    
SH_ENTERED_DATE       DATE                            
SH_UPDATEDBY          VARCHAR2(30)                    
SH_UPDATED_DATE       DATE                            
SH_BFVAT_AMOUNT       NUMBER                          
SH_VAT_AMOUNT         NUMBER                          
SH_NET_AMOUNT         NUMBER                          
SH_CCARDNO            VARCHAR2(30)                    
SH_CCARDEXP           DATE                            
SH_CCARDNAME          VARCHAR2(60)                    
CR_CUST_CODE          VARCHAR2(30)                    
CR_CUST_NAME          VARCHAR2(100)                   
CR_EMAIL              VARCHAR2(254)                   
CR_PHONE              VARCHAR2(30)                    
CR_FAX                VARCHAR2(30)                    
SP_NAME               VARCHAR2(100)                   
SP_ADDRESS1           VARCHAR2(100)                   
SP_ADDRESS2           VARCHAR2(100)                   
SP_CITY               VARCHAR2(100)                   
SP_ZIP                VARCHAR2(15)                    
SP_COUNTRY            VARCHAR2(30)                    
SP_PHONE1             VARCHAR2(30)                    
SP_PHONE2             VARCHAR2(30)                    
SP_PAGER              VARCHAR2(30)                    
SP_FAX                VARCHAR2(30)                    
SP_EMAIL              VARCHAR2(254) 

The error occurs when the cursor is opened because if I comment the cursor loop, I don't get the 
error message.

Could this be something related to DATE formats???

CREATE OR REPLACE PACKAGE sl_view
AS
  PROCEDURE orderlist(
   wsid       IN VARCHAR2
  ,pfind      IN VARCHAR2 DEFAULT ''
  ,pbeg_date  IN VARCHAR2 DEFAULT to_char(sysdate-15, 'MM/DD/YYYY')
  ,pend_date  IN VARCHAR2 DEFAULT to_char(sysdate, 'MM/DD/YYYY')
  ,pslsperson IN VARCHAR2 DEFAULT ''
  ,pstatus    IN VARCHAR2 DEFAULT ''
  ,pfilter    IN VARCHAR2 DEFAULT ''
  ,pgn_beg    IN NUMBER DEFAULT 1
  );

END sl_view;
/

CREATE OR REPLACE PACKAGE BODY sl_view
AS

/*
  LIST ORDERS
*/
 PROCEDURE orderlist(
   wsid       IN VARCHAR2
  ,pfind      IN VARCHAR2 DEFAULT ''
  ,pbeg_date  IN VARCHAR2 DEFAULT to_char(sysdate-15, 'MM/DD/YYYY')
  ,pend_date  IN VARCHAR2 DEFAULT to_char(sysdate, 'MM/DD/YYYY')
  ,pslsperson IN VARCHAR2 DEFAULT ''
  ,pstatus    IN VARCHAR2 DEFAULT ''
  ,pfilter    IN VARCHAR2 DEFAULT ''
  ,pgn_beg    IN NUMBER DEFAULT 1
  ) IS

 CURSOR csid IS
 SELECT SD_SID, SD_USER_CODE, SD_SERVER, SD_LOGIN_DATE, SD_EXP_DATE, SD_IP
   FROM SL_SESSION
  WHERE SD_SID = wsid ;

 RSID CSID%ROWTYPE;

 max_line_page   integer;
 SQ              varchar2(5000);
 SQW             varchar2(5000);
 max_page_num    integer;
 P_FIRST         number;
 P_LAST          number;
 F_PAGE          integer;
 Z_MAXNUM        VARCHAR2(25);
 max_var           number;
 page_write_count  integer;

 TYPE typ1 IS REF CURSOR;
 ref_cr typ1;

 v_SH_HEADER_ID        SL_SLSORDERS.SH_HEADER_ID     %TYPE;
 v_SH_DATE             SL_SLSORDERS.SH_DATE          %TYPE;
 v_SH_SERIAL           SL_SLSORDERS.SH_SERIAL        %TYPE;
 v_SH_NUMBER           SL_SLSORDERS.SH_NUMBER        %TYPE;
 v_SH_STATUS           SL_SLSORDERS.SH_STATUS        %TYPE;
 v_SH_TYPE             SL_SLSORDERS.SH_TYPE          %TYPE;
 v_SH_CUST_ID          SL_SLSORDERS.SH_CUST_ID       %TYPE;
 v_SH_PO_NUMBER        SL_SLSORDERS.SH_PO_NUMBER     %TYPE;
 v_SH_DEPT_CODE        SL_SLSORDERS.SH_DEPT_CODE     %TYPE;
 v_SH_STORE_CODE       SL_SLSORDERS.SH_STORE_CODE    %TYPE;
 v_SH_TERMS_CODE       SL_SLSORDERS.SH_TERMS_CODE    %TYPE;
 v_SH_SHIPMENT_TYPE    SL_SLSORDERS.SH_SHIPMENT_TYPE %TYPE;
 v_SH_SALESPERSON      SL_SLSORDERS.SH_SALESPERSON   %TYPE;
 v_SH_STARTDATE        SL_SLSORDERS.SH_STARTDATE     %TYPE;
 v_SH_CANCELDATE       SL_SLSORDERS.SH_CANCELDATE    %TYPE;
 v_SH_ENTEREDBY        SL_SLSORDERS.SH_ENTEREDBY     %TYPE;
 v_SH_ENTERED_DATE     SL_SLSORDERS.SH_ENTERED_DATE  %TYPE;
 v_SH_UPDATEDBY        SL_SLSORDERS.SH_UPDATEDBY     %TYPE;
 v_SH_UPDATED_DATE     SL_SLSORDERS.SH_UPDATED_DATE  %TYPE;
 v_SH_BFVAT_AMOUNT     SL_SLSORDERS.SH_BFVAT_AMOUNT  %TYPE;
 v_SH_VAT_AMOUNT       SL_SLSORDERS.SH_VAT_AMOUNT    %TYPE;
 v_SH_NET_AMOUNT       SL_SLSORDERS.SH_NET_AMOUNT    %TYPE;
 v_CR_CUST_CODE        SL_SLSORDERS.CR_CUST_CODE     %TYPE;
 v_CR_CUST_NAME        SL_SLSORDERS.CR_CUST_NAME     %TYPE;
 v_SP_NAME             SL_SLSORDERS.SP_NAME          %TYPE;

 beg_date    DATE;
 end_date    DATE;

 ERR1 EXCEPTION;
 ERR_MSG VARCHAR2(250);

 pSID       SL_SESSION.SD_SID%TYPE;

 BEGIN

   pSID := wsid;

   sl_security_check.checkit(pSID);

   max_line_page := TO_NUMBER(get_lookupvalue('SYSTEM', 'MAX_PAGE_LINE'));

   OPEN CSID;
   FETCH CSID INTO RS

  


  
分享到:
评论

相关推荐

    bESQL的数据类型

    4. **Julian Date Conversion**(`rmdyjul(short mdy[3],long*jdate)`):将月、日、年转换为儒略日期。 5. **Date Formatting**(`rfmtdate(ling jdate,char*fmt,char*str)`):将日期值转换为指定格式的字符串。...

    DbfDotNet_version_1.0_Source

    The answer is a bit long but simple. As I said earlier DbfDotNet is designed to be as fast as possible. In order to get the database started and get some interest I need two things: A good product ...

    bcp常见问题集合

    ##### 问题 2:`The conversion/operation was stopped due to a syntax error in the source field.` 该错误提示表示在源字段中出现了语法错误导致转换/操作停止。 **解决方案**: 1. **检查源文件中的数据格式*...

    【KUKA 机器人资料】:激光跟踪焊接机器人系统技术方案.pdf

    KUKA机器人相关资料

    基于Matlab的模拟退火算法在旅行商问题(TSP)优化中的应用及其实现

    内容概要:本文详细介绍了利用Matlab实现模拟退火算法来优化旅行商问题(TSP)。首先阐述了TSP的基本概念及其在路径规划、物流配送等领域的重要性和挑战。接着深入讲解了模拟退火算法的工作原理,包括高温状态下随机探索、逐步降温过程中选择较优解或以一定概率接受较差解的过程。随后展示了具体的Matlab代码实现步骤,涵盖城市坐标的定义、路径长度的计算方法、模拟退火主循环的设计等方面。并通过多个实例演示了不同参数配置下的优化效果,强调了参数调优的重要性。最后讨论了该算法的实际应用场景,如物流配送路线优化,并提供了实用技巧和注意事项。 适合人群:对路径规划、物流配送优化感兴趣的科研人员、工程师及高校学生。 使用场景及目标:适用于需要解决复杂路径规划问题的场合,特别是涉及多个节点间最优路径选择的情况。通过本算法可以有效地减少路径长度,提高配送效率,降低成本。 其他说明:文中不仅给出了完整的Matlab代码,还包括了一些优化建议和技术细节,帮助读者更好地理解和应用这一算法。此外,还提到了一些常见的陷阱和解决方案,有助于初学者避开常见错误。

    基于STM32的永磁同步电机Simulink代码生成与57次谐波抑制的霍尔FOC控制

    内容概要:本文详细介绍了如何利用Simulink进行自动代码生成,在STM32平台上实现带57次谐波抑制功能的霍尔场定向控制(FOC)。首先,文章讲解了所需的软件环境准备,包括MATLAB/Simulink及其硬件支持包的安装。接着,阐述了构建永磁同步电机(PMSM)霍尔FOC控制模型的具体步骤,涵盖电机模型、坐标变换模块(如Clark和Park变换)、PI调节器、SVPWM模块以及用于抑制特定谐波的陷波器的设计。随后,描述了硬件目标配置、代码生成过程中的注意事项,以及生成后的C代码结构。此外,还讨论了霍尔传感器的位置估算、谐波补偿器的实现细节、ADC配置技巧、PWM死区时间和换相逻辑的优化。最后,分享了一些实用的工程集成经验,并推荐了几篇有助于深入了解相关技术和优化控制效果的研究论文。 适合人群:从事电机控制系统开发的技术人员,尤其是那些希望掌握基于Simulink的自动代码生成技术,以提高开发效率和控制精度的专业人士。 使用场景及目标:适用于需要精确控制永磁同步电机的应用场合,特别是在面对高次谐波干扰导致的电流波形失真问题时。通过采用文中提供的解决方案,可以显著改善系统的稳定性和性能,降低噪声水平,提升用户体验。 其他说明:文中不仅提供了详细的理论解释和技术指导,还包括了许多实践经验教训,如霍尔传感器处理、谐波抑制策略的选择、代码生成配置等方面的实际案例。这对于初学者来说是非常宝贵的参考资料。

    基于S7-200 PLC和组态王的机械手搬运控制系统设计与调试

    内容概要:本文详细介绍了基于西门子S7-200 PLC和组态王的机械手搬运控制系统的实现方案。首先,文章展示了梯形图程序的关键逻辑,如急停连锁保护、水平移动互锁以及定时器的应用。接着,详细解释了IO分配的具体配置,包括数字输入、数字输出和模拟量接口的功能划分。此外,还讨论了接线图的设计注意事项,强调了电磁阀供电和继电器隔离的重要性。组态王的画面设计部分涵盖了三层画面结构(总览页、参数页、调试页)及其动画脚本的编写。最后,分享了调试过程中遇到的问题及解决方案,如传感器抖动、输出互锁设计等。 适合人群:从事自动化控制领域的工程师和技术人员,尤其是对PLC编程和组态软件有一定基础的读者。 使用场景及目标:适用于自动化生产线中机械手搬运控制系统的开发与调试。目标是帮助读者掌握从硬件接线到软件逻辑的完整实现过程,提高系统的稳定性和可靠性。 其他说明:文中提供了大量实践经验,包括常见的错误和解决方案,有助于读者在实际工作中少走弯路。

    西门子1200PLC污水处理项目:PLC程序、通讯配置与HMI设计详解

    内容概要:本文详细介绍了基于西门子1200PLC的污水处理项目,涵盖了PLC程序结构、通信配置、HMI设计以及CAD原理图等多个方面。PLC程序采用梯形图和SCL语言相结合的方式,实现了复杂的控制逻辑,如水位控制、曝气量模糊控制等。通讯配置采用了Modbus TCP和Profinet双协议,确保了设备间高效稳定的通信。HMI设计则注重用户体验,提供了详细的报警记录和趋势图展示。此外,CAD图纸详尽标注了设备位号,便于后期维护。操作说明书中包含了应急操作流程和定期维护建议,确保系统的长期稳定运行。 适合人群:从事工业自动化领域的工程师和技术人员,尤其是对PLC编程、HMI设计和通信配置感兴趣的从业者。 使用场景及目标:适用于污水处理厂及其他类似工业控制系统的设计、实施和维护。目标是帮助工程师掌握完整的项目开发流程,提高系统的可靠性和效率。 其他说明:文中提供的具体代码片段和设计思路对于理解和解决实际问题非常有价值,建议读者结合实际项目进行深入学习和实践。

    5电平三相MMC的VSG控制与MATLAB-Simulink仿真:调频调压效果验证

    内容概要:本文详细介绍了基于5电平三相模块化多电平变流器(MMC)的虚拟同步发电机(VSG)控制系统的构建与仿真。首先,文章描述了MMC的基本结构和参数设置,包括子模块电容电压均衡策略和载波移相策略。接着,深入探讨了VSG控制算法的设计,特别是有功-频率和无功-电压下垂控制的具体实现方法。文中还展示了通过MATLAB-Simulink进行仿真的具体步骤,包括设置理想的直流电源和可编程三相源来模拟电网扰动。仿真结果显示,VSG控制系统能够在面对频率和电压扰动时迅速恢复稳定,表现出良好的调频调压性能。 适合人群:从事电力电子、电力系统自动化及相关领域的研究人员和技术人员。 使用场景及目标:适用于研究和开发新型电力电子设备,特别是在新能源接入电网时提高系统的稳定性。目标是通过仿真验证VSG控制的有效性,为实际应用提供理论支持和技术指导。 其他说明:文章提供了详细的代码片段和仿真配置,帮助读者更好地理解和重现实验结果。此外,还提到了一些常见的调试技巧和注意事项,如选择合适的仿真步长和参数配对调整。

    工业自动化中基于PLC1200的SCL与梯形图混编立体库及码垛系统的通信与控制

    内容概要:本文详细介绍了在一个复杂的工业自动化项目中,如何利用西门子S7-1200 PLC为核心,结合基恩士视觉相机、ABB机器人以及G120变频器等多种设备,构建了一个高效的立体库码垛系统。文中不仅探讨了不同设备之间的通信协议(如Modbus TCP和Profinet),还展示了SCL和梯形图混合编程的具体应用场景和技术细节。例如,通过SCL进行视觉坐标解析、机器人通信心跳维护等功能的实现,而梯形图则用于处理简单的状态切换和安全回路。此外,作者分享了许多实际调试过程中遇到的问题及其解决方案,强调了良好的注释习惯对于提高代码可维护性的关键作用。 适用人群:从事工业自动化领域的工程师和技术人员,尤其是对PLC编程、机器人控制及多种通信协议感兴趣的从业者。 使用场景及目标:适用于需要整合多种工业设备并确保它们能够稳定协作的工作环境。主要目标是在保证系统高精度的同时降低故障率,从而提升生产效率。 其他说明:文中提到的一些具体技术和方法可以作为类似项目的参考指南,帮助开发者更好地理解和应对复杂的工业控制系统挑战。

    【KUKA 机器人资料】:KUKA机器人_Interbus_输入输出端口配置说明书.pdf

    KUKA机器人相关资料

    java脱敏工具类,敏感数据脱敏

    java脱敏工具类

    基于自抗扰控制的表贴式永磁同步电机双环控制系统设计与实现

    内容概要:本文详细介绍了基于自抗扰控制(ADRC)的表贴式永磁同步电机(SPMSM)双环控制系统的建模与实现方法。该系统采用速度环一阶ADRC控制和电流环PI控制相结合的方式,旨在提高电机在复杂工况下的稳定性和响应速度。文章首先解释了选择ADRC的原因及其优势,接着展示了ADRC和PI控制器的具体实现代码,并讨论了在Matlab/Simulink环境中搭建模型的方法和注意事项。通过对不同工况下的仿真测试,验证了该控制策略的有效性,特别是在负载突变情况下的优越表现。 适合人群:从事电机控制、自动化控制及相关领域的研究人员和技术人员,尤其是对自抗扰控制感兴趣的工程师。 使用场景及目标:适用于需要高精度、高响应速度的工业伺服系统和其他高性能电机应用场景。目标是提升电机在复杂环境下的稳定性和抗扰能力,减少转速波动和恢复时间。 其他说明:文中提供了详细的代码示例和调试技巧,帮助读者更好地理解和实施该控制策略。同时,强调了在实际应用中需要注意的问题,如参数调整、输出限幅等。

    java设计模式之责任链的demo

    java设计模式之责任链的使用demo

    电力电子领域中两相交错并联Buck/Boost变换器的三种控制方式及其仿真分析

    内容概要:本文详细介绍了两相交错并联Buck/Boost变换器的硬件结构和三种控制方式(开环、电压单环、双环)的实现方法及仿真结果。文中首先描述了该变换器的硬件结构特点,即四个MOS管组成的H桥结构,两相电感交错180度工作,从而有效减少电流纹波。接着,针对每种控制方式,具体讲解了其配置步骤、关键参数设置以及仿真过程中需要注意的问题。例如,在开环模式下,通过固定PWM占空比来观察原始波形;电压单环则引入PI控制器进行电压反馈调节;双环控制进一步增加了电流内环,实现了更为精确的电流控制。此外,文章还探讨了单向结构的特点,并提供了仿真技巧和避坑指南。 适合人群:从事电力电子研究的技术人员、高校相关专业师生。 使用场景及目标:适用于希望深入了解两相交错并联Buck/Boost变换器的工作原理和技术细节的研究者,旨在帮助他们掌握不同控制方式的设计思路和仿真方法。 其他说明:文中不仅提供了详细的理论解释,还有丰富的实例代码片段,便于读者理解和实践。同时,作者分享了许多宝贵的实践经验,有助于避免常见的仿真错误。

    第十六届蓝桥杯大赛软件赛省赛第二场 C/C++ 大学 A 组

    第二场c++A组

    数控磨床编程.ppt

    数控磨床编程.ppt

    COMSOL数值模拟:N2和CO2混合气体在THM热流固三场耦合下增强瓦斯抽采的技术研究与应用

    内容概要:本文详细介绍了利用COMSOL软件进行N2和CO2混合气体在热-流-固三场耦合作用下增强煤层气抽采的数值模拟。首先,通过设定煤岩材料参数,如热导率、杨氏模量等,构建了煤岩物理模型。接着,引入达西定律和Maxwell-Stefan扩散方程,建立了混合气体运移方程,考虑了气体膨胀系数和吸附特性。在应力场求解方面,采用自适应步长和阻尼系数调整,确保模型稳定。同时,探讨了温度场与气体运移的耦合机制,特别是在低温条件下CO2注入对煤体裂隙扩展的影响。最后,通过粒子追踪和流线图展示了气体运移路径和抽采效率的变化。 适合人群:从事煤层气开采、数值模拟以及相关领域的科研人员和技术工程师。 使用场景及目标:适用于需要优化煤层气抽采工艺的研究机构和企业,旨在通过数值模拟提高抽采效率并减少环境影响。 其他说明:文中提供了详细的MATLAB和COMSOL代码片段,帮助读者理解和复现模型。此外,强调了模型参数选择和求解器配置的重要性,分享了作者的实际经验和常见问题解决方法。

    计算给定G、相位裕度、交叉频率和安全裕度要求的引线补偿器

    基于Bode的引线补偿器设计 计算给定G、相位裕度、交叉频率和安全裕度要求的引线补偿器。 计算给定电厂G、PM和Wc要求的铅补偿器,并运行ControlSystemDesigner进行验证。

    【KUKA 机器人TCP测量】:mp2_tool_fixed_en.ppt

    KUKA机器人相关文档

Global site tag (gtag.js) - Google Analytics